SELECT Transact-SQL 문에서 동적으로 행 번호를 매기는 방법

SQL Server 2007. 3. 16. 01:41

출처 : http://support.microsoft.com/kb/186133/ko


SELECT Transact-SQL 문에서 동적으로 행 번호를 매기는 방법

이 문서가 적용되는 제품 보기.
기술 자료 ID : 186133
마지막 검토 : 2006년 5월 22일 월요일
수정 : 5.1
이 문서는 이전에 다음 ID로 출판되었음: KR186133

요약

이 문서에서는 유연성 있는 방법을 사용하여 SELECT 문을 수행할 때 동적으로 행의 순위를 매기는 방법을 설명합니다. 이 방법은 유일한 해결책일 수 있으며 프로시저 해결책보다 빠릅니다. 행 번호 매기기나 순위 매기기는 전형적인 프로시저 문제입니다. 이 해결책은 대개 루프와 임시 테이블을 기반으로 하므로 SQL Server 루프와 커서를 사용합니다. 이 기술은 자동 조인에 기반을 두고 있습니다. 선택되는 관계는 대개 "보다 큼"입니다. 특정 데이터 집합을 자신과 비교할 때 해당 집합의 각 요소가 "보다 큼" 관계를 충족하는 횟수를 계산합니다.

참고 다음 예제는 pubs 데이터베이스를 기반으로 합니다. 기본적으로 Northwind 예제 데이터베이스와 pubs 예제 데이터베이스는 SQL Server 2005에 설치되지 않습니다. 이러한 데이터베이스는 Microsoft 다운로드 센터에서 다운로드할 수 있습니다. 자세한 내용을 보려면 다음 Microsoft 웹 사이트를 방문하십시오.
http://go.microsoft.com/fwlink/?linkid=30196 (http://go.microsoft.com/fwlink/?linkid=30196)(영문)
SQL2000SampleDb.msi를 다운로드한 후 SQL2000SampleDb.msi를 두 번 눌러 예제 데이터베이스 스크립트를 추출합니다. 기본적으로 SQL2000SampleDb.msi는 데이터베이스 스크립트와 추가 정보 파일을 다음 폴더에 추출합니다.
C:\SQL Server 2000 Sample Databases
추가 정보 파일의 지침에 따라 설치 스크립트를 실행합니다.

위로 가기

SQL Server 2005를 사용하는 경우

SQL Server 2005에서 새 기능으로 제공되는 순위 함수를 사용하는 것이 좋습니다. 순위 함수에 대한 자세한 내용은 다음 MSDN(Microsoft Developer Network) 웹 사이트를 참조하십시오.
http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx (http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx)

위로 가기

예제 1

이 예제의 경우
• 집합 1은 저자입니다.
• 집합 2는 저자입니다.
• 관계는 "성과 이름이 보다 큼(last and first names are greater than)"입니다.
• 이름 + 성을 다른 이름 + 성과 비교하여 중복 문제를 방지할 수 있습니다.
• count(*)를 통해 해당 관계가 충족된 횟수를 계산합니다.
쿼리: 
   select rank=count(*), a1.au_lname, a1.au_fname
   from authors a1, authors a2
   where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
   group by a1.au_lname, a1.au_fname
   order by rank
SQL Server 2005에서는 다음 코드를 사용합니다.
   select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname
   from authors a
   order by rank
결과: 
   Rank        Au_Lname              Au_Fname
   ----        --------------        -----------
     1         Bennet                Abraham
     2         Blotchet-Halls        Reginald
     3         Carson                Cheryl
     4         DeFrance              Michel
     5         del Castillo          Innes
     6         Dull                  Ann
     7         Greene                Morningstar
     8         Green                 Marjorie
     9         Gringlesby            Burt
    10         Hunter                Sheryl
    11         Karsen                Livia
    12         Locksley              Charlene
    13         MacFeather            Stearns
    14         McBadden              Heather
    15         O'Leary               Michael
    16         Panteley              Sylvia
    17         Ringer                Albert
    18         Ringer                Anne
    19         Smith                 Meander
    20         Straight              Dean
    21         Stringer              Dirk
    22         White                 Johnson
    23         Yokomoto              Akiko

   (23 row(s) affected)

위로 가기

예제 2

이 예제의 경우
• 판매 부수를 기준으로 서점의 순위를 매깁니다.
• 집합 1은 서점에서 판매한 책 부수입니다(select stor_id, qty=sum(qty) from sales group by stor_id).
• 집합 2는 서점에서 판매한 책 부수입니다(select stor_id, qty=sum(qty) from sales group by stor_id).
• 관계는 "책 부수가 보다 큼(the number of books is greater than)"입니다.
• 중복을 피하기 위해 qty 대신 price*qty를 비교할 수도 있습니다.
쿼리: 
   select rank=count(*), s1.stor_id, qty=sum(s1.qty)
   from (select stor_id, qty=sum(qty) from sales group by stor_id) s1,
        (select stor_id, qty=sum(qty) from sales group by stor_id) s2
   where s1.qty >= s2.qty
   group by s1.stor_id
   order by rank
결과: 
   Rank     Stor_Id    Qty
   ----     -------    ---
   1         6380        8
   2         7896      120
   3         8042      240
   4         7067      360
   5         7066      625
   6         7131      780

   (6 row(s) affected)
참고 Qty 열의 값이 올바르지 않지만 판매 부수를 기준으로 하는 서점 순위는 올바릅니다. 이것이 이 방법의 결함입니다. 결과에 부수가 잘못 나타나는 것에 신경쓰지 않는다면 이 방법을 사용하여 서점의 순위를 반환할 수 있습니다.

SQL Server 2005에서는 다음 코드를 사용합니다.
select row_number() over (order by qty desc) as rank,s1.stor_id,s1.qty
from (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
결과: 
rank     stor_id  qty
-------  -------  ------
1        7131     130
2        7066     125
3        7067     90
4        8042     80
5        7896     60
6        6380     8

(6 row(s) affected)
참고 SQL Server 2005에서 순위 함수를 사용하면 순위와 부수에 대한 올바른 결과를 얻을 수 있습니다.

위로 가기

예제 3

이 예제의 경우
• 수익을 기준으로 출판사의 순위를 매깁니다.
• 집합 1은 출판사별 총 판매액입니다.
        select t.pub_id, sales=sum(s.qty*t.price)
        from sales s, titles t
        where s.title_id=t.title_id
          and t.price is not null
        group by t.pub_id
• 집합 2는 출판사별 총 판매액입니다.
        select t.pub_id, sales=sum(s.qty*t.price)
        from sales s, titles t
        where s.title_id=t.title_id
          and t.price is not null
        group by t.pub_id
• 관계는 "보다 많은 돈을 벌었음(earns more money than)"입니다.
쿼리: 
   select rank=count(*), s1.pub_id, sales=sum(s1.sales)
   from    (select t.pub_id, sales=sum(s.qty*t.price)
           from sales s, titles t
           where s.title_id=t.title_id
             and t.price is not null
           group by t.pub_id) s1,
           (select t.pub_id, sales=sum(s.qty*t.price)
           from sales s, titles t
           where s.title_id=t.title_id
             and t.price is not null
           group by t.pub_id) s2
   where s1.sales>= s2.sales
   group by s1.pub_id
   order by rank
결과: 
   Rank     Pub_Id   Sales
   ----     ------   --------
   1         0736    1,961.85
   2         0877    4,256.20
   3         1389    7,760.85

   (3 row(s) affected)
참고 Sales 열의 값이 올바르지 않지만 수익을 기준으로 하는 출판사의 순위는 올바릅니다.

SQL Server 2005에서는 다음 코드를 사용합니다. 
select rank() over (order by sales desc) as rank,s1.pub_id,s1.sales 
from (select t.pub_id, sales=sum(s.qty*t.price)
     from sales s inner join titles t
     on s.title_id=t.title_id
     where  t.price is not null
     group by t.pub_id) as s1
결과: 
rank     pub_id  sales
-------  ------  ---------
1        1389    2586.95
2        0877    2128.10
3        0736    1961.85

(3 row(s) affected)
참고 SQL Server 2005에서 순위 함수를 사용하면 순위와 수익에 대한 올바른 결과를 얻을 수 있습니다.

위로 가기

단점

• Cross Join 때문에 많은 수의 행에는 이 방법을 사용할 수 없습니다. 이 방법은 행이 수십 또는 수백 개인 경우에 적합합니다. 대형 테이블에서는 대규모 검색을 피하기 위해 인덱스를 사용해야 합니다.
• 이 방법은 중복 값을 제대로 처리하지 못합니다. 중복 값을 비교하면 행 번호가 불연속적으로 매겨집니다. 행 번호가 불연속적으로 매겨지는 것을 원하지 않으면 스프레드시트에 결과를 삽입할 때 순위 열을 숨기고 대신 스프레드시트 번호 매기기를 사용하면 됩니다.

참고 SQL Server 2005를 사용하는 경우 row_number() 함수를 사용하여 중복 행에 관계없이 순차적인 행의 번호를 반환할 수 있습니다.
예제: 
   select rank=count(*), s1.title_id, qty=sum(s1.qty)
   from (select title_id, qty=sum(qty) from sales group by title_id) s1,
        (select title_id, qty=sum(qty) from sales group by title_id) s2
   where s1.qty >= s2.qty
   group by s1.title_id
   order by rank
결과: 
   Rank    Title_Id    Qty
   ----    --------    ----
   1       MC2222        10
   4       BU1032        60
   4       BU7832        60
   4       PS3333        60
   7       PS1372       140
   7       TC4203       140
   7       TC7777       140
   10      BU1111       250
   10      PS2106       250
   10      PS7777       250
   11      PC1035       330
   12      BU2075       420
   14      MC3021       560
   14      TC3218       560
   15      PC8888       750
   16      PS2091      1728

   (16 row(s) affected)

위로 가기

장점

• 뷰와 결과 서식에도 이러한 쿼리를 사용할 수 있습니다.
• 순위가 낮은 데이터일수록 더 오른쪽으로 들여 쓸 수 있습니다.
예제 1: 
   CREATE VIEW v_pub_rank
   AS
   select rank=count(*), s1.title_id, qty=sum(s1.qty)
   from (select title_id, qty=sum(qty) from sales group by title_id) s1,
        (select title_id, qty=sum(qty) from sales group by title_id) s2
   where s1.qty >= s2.qty
   group by s1.title_id
쿼리: 
   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
           pub_id +
           replicate(' ', 15-power(2,rank))+': '),
           earnings=qty
   from v_pub_rank
결과: 
   Publisher       Earnings
   -------------   --------
     0736          : 1,961.85
       0877        : 4,256.20
           1389    : 7,760.85
SQL Server 2005에서는 다음 코드를 사용합니다.
CREATE VIEW v_pub_rank
AS
select rank() over (order by sales) as rank,s1.pub_id,s1.sales 
	from (select t.pub_id, sales=sum(s.qty*t.price)
	from sales s, titles t
	where s.title_id=t.title_id
	and t.price is not null
	group by t.pub_id) as s1
GO

select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
	pub_id + replicate(' ', 15-power(2,rank))+': '),
	earnings=sales
from v_pub_rank order by rank
GO
결과: 
publisher            earnings
-------------------- ---------------------
  0736             : 1961.85
    0877           : 2128.10
        1389       : 2586.95

(3 row(s) affected)

예제 2: 
   CREATE VIEW v_title_rank
   AS
   select rank=count(*), s1.title_id, qty=sum(s1.qty)
   from (select title_id, qty=sum(qty) from sales group by title_id) s1,
        (select title_id, qty=sum(qty) from sales group by title_id) s2
   where s1.qty >= s2.qty
   group by s1.title_id
쿼리: 
   select  Book=convert(varchar(45),replicate (' ', 2*rank) +
           title_id +
           replicate(' ', 35-2*rank)+': '),
           qty
   from v_title_rank
   order by rank
결과: 
   Book                                          Qty
   -------------------------------------------   ----
     MC2222                                 :      10
           BU1032                           :      60
           BU7832                           :      60
           PS3333                           :      60
                 PS1372                     :     140
                 TC4203                     :     140
                 TC7777                     :     140
                       BU1111               :     250
                       PS2106               :     250
                       PS7777               :     250
                         PC1035             :     330
                           BU2075           :     420
                               MC3021       :     560
                               TC3218       :     560
                                 PC8888     :     750
                                   PS2091   :    1728

   (16 row(s) affected)
SQL Server 2005에서는 다음 코드를 사용합니다.
CREATE VIEW v_title_rank
AS
select rank() over (order by qty) as rank, s1.title_id,s1.qty
from (select title_id, qty=sum(qty) from sales group by title_id) as s1
GO

select Book=convert(varchar(45),replicate (' ', 2*rank) +
title_id + replicate(' ', 35-2*rank)+': '), qty
from v_title_rank
order by rank
GO
결과: 
Book                                          qty
--------------------------------------------- -----------
  MC2222                                 :    10
    BU1032                               :    15
    BU7832                               :    15
    PS3333                               :    15
          TC4203                         :    20
          TC7777                         :    20
          PS1372                         :    20
                BU1111                   :    25
                PS7777                   :    25
                PS2106                   :    25
                      PC1035             :    30
                        BU2075           :    35
                          MC3021         :    40
                          TC3218         :    40
                              PC8888     :    50
                                PS2091   :    108

(16 row(s) affected)




Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹 (http://support.microsoft.com/newsgroups/default.aspx)에 참여하시기 바랍니다.

위로 가기

본 문서의 정보는 다음의 제품에 적용됩니다.
• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 4.21a Standard Edition
• Microsoft SQL Server 6.0 Standard Edition
• Microsoft SQL Server 6.5 Standard Edition
• Microsoft SQL Server 7.0 Standard Edition
• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL 2005 Server Enterprise
• Microsoft SQL Server 2005 Express Edition
• Microsoft SQL 2005 Server Workgroup

위로 가기

키워드:
kbhowtomaster KB186133

위로 가기

AND

티스토리툴바