순위 결정 함수

SQL Server 2005는 ROW_NUMBER, RANK, DENSE_RANK, NTILE의 새로운 순위 결정 함수 4가지를 도입하였습니다. 이 새로운 함수들을 이용하면 데이터를 효율적으로 분석하고 쿼리의 결과 행에 순위 결정 값을 제공할 수 있습니다. 이 새로운 함수가 도움이 될 수 있는 전형적인 시나리오로서 프레젠테이션 목적, 페이징, 평가 및 히스토그램에 대한 결과 행에 순차 정수를 지정하는 경우를 들 수 있습니다.

 

화자 통계 시나리오

다음의 화자 통계 시나리오는 서로 다른 함수와 각 함수의 절에 대해 설명하고 예시할 때 사용될 것입니다. 대규모 컴퓨팅 회의에는 데이터베이스, 개발 및 시스템 관리의 세 가지 트랙이 포함되었습니다. 이 회의에는 11명의 화자가 참여하였고 각자 주어진 세션에 대해 1-9점 사이의 점수를 받았습니다. 그 결과는 다음 SpeakerStats 테이블에 요약, 저장되어 있습니다.

 

USE tempdb -- or your own test database

CREATE TABLE SpeakerStats

(

 speaker VARCHAR(10) NOT NULL PRIMARY KEY,

 track VARCHAR(10) NOT NULL,

 score INT NOT NULL,

 pctfilledevals INT NOT NULL,

 numsessions INT NOT NULL

)

 

SET NOCOUNT ON

INSERT INTO SpeakerStats VALUES('Dan', 'Sys', 3, 22, 4)

INSERT INTO SpeakerStats VALUES('Ron', 'Dev', 9, 30, 3)

INSERT INTO SpeakerStats VALUES('Kathy', 'Sys', 8, 27, 2)

INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)

INSERT INTO SpeakerStats VALUES('Joe', 'Dev', 6, 20, 2)

INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)

INSERT INTO SpeakerStats VALUES('Mike', 'DB', 8, 20, 3)

INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)

INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)

INSERT INTO SpeakerStats VALUES('Brian', 'Sys', 7, 22, 3)

INSERT INTO SpeakerStats VALUES('Kevin', 'DB', 7, 25, 4)

 

각각의 화자에게 테이블의 한 행이 할당되며 여기에 화자의 이름과 트랙, 평균 점수, 해당 세션에 출석한 출석자의 수에 대해 평가 점수를 기록한 출석자의 백분율, 해당 화자가 수행한 세션의 횟수가 기록됩니다. 이 섹션에서는 새로운 순위 결정 함수를 사용하여 화자 통계 자료를 분석함으로써 유용한 정보를 산출하는 방법을 보여줍니다.

 

의미론

4가지 순위 결정 함수 모두 다음과 같은 유사한 구문 패턴을 따릅니다.

 

순위 결정 함수

 

<function_name>() OVER(

   [PARTITION BY <partition_by_list>]

   ORDER BY <order_by_list>)

이 함수는 SELECT 절이나 ORDER BY 절로 표현되는 쿼리의 두 가지 절로만 지정할 수 있습니다. 다음 섹션에서는 이 서로 다른 함수들에 대해 자세히 알아보기로 합니다.

ROW_NUMBER

이 ROW_NUMBER 함수를 이용하면 쿼리의 결과 행에 순차 정수 값을 제공할 수 있습니다. 예를 들어, 내림차순의 점수 순서에 따라 결과 행에 1부터 시작하여 순차적 값을 지정함으로써 모든 화자의 speaker, track 및 score를 반환하고자 한다고 가정합니다. 다음 쿼리는 ROW_NUMBER 함수를 사용하여 OVER(ORDER BY score DESC)를 지정함으로써 원하는 결과를 생성합니다.

 

SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,

      speaker, track, score

FROM SpeakerStats

ORDER BY score DESC

 

결과 집합은 다음과 같습니다.

 

rownum   speaker     track       score

------ ---------- ---------- -----------

1            Jessica      Dev          9

2            Ron           Dev           9

3            Suzanne    DB            9

4            Kathy         Sys           8

5            Michele     Sys           8

6            Mike         DB             8

7            Kevin         DB            7

8            Brian        Sys            7

9            Joe          Dev            6

10          Robert       Dev           6

11          Dan          Sys            3

 

가장 높은 점수를 받은 화자가 행 번호 1을 가지고 가장 낮은 점수를 받은 화자가 행 번호 11을 가졌습니다. ROW_NUMBER는 항상 요청된 정렬 방식에 따라 각 행마다 다른 행 번호를 생성합니다. OVER() 옵션 안에서 지정된 ORDER BY 목록이 고유하지 않을 경우 그 결과는 비결정적이 됩니다. 이는 곧 쿼리에 대한 올바른 결과가 여러 개 나오게 되고 같은 쿼리를 달리 호출할 경우 결과도 달라질 수 있다는 것을 의미합니다. 예를 들어, 이 사례에서는 세 명의 화자 Jessica, Ron, Suzanne이 모두 똑같은 최고점인 9점을 받았습니다. SQL Server는 각 화자마다 다른 행 번호를 할당해야 하기 때문에 Jessica, Ron, Suzanne에게 각각 할당된 1, 2, 3이라는 값은 이들 화자 사이에 임의의 순서로 할당되었다고 가정할 수 있습니다. 그 결과는 Jessica, Ron, Suzanne에게 각각 1, 2, 3의 값이 할당되었다 해도 똑같이 올바른 결과가 되었을 것입니다.

고유한 ORDER BY 목록을 지정할 경우 그 결과는 항상 결정적이 됩니다. 예를 들어, 점수를 기준으로 할 때 두 화자 간에 동점이 나오는 경우 가장 높은 pctfilledevals 값을 승자 결정 요소로 사용하려고 한다고 가정합시다. 그래도 여전히 동점이 나올 경우 가장 높은 numsessions 값을 승자 결정 요소로 사용하십시오. 마지막으로, 여전히 동점이 나올 경우 사전 순서로 가장 늦은 speaker 이름을 승자 결정 요소로 사용합니다. ORDER BY 목록의 score, pctfilledevals, numsessions, speaker가 고유하기 때문에 결과는 다음과 같이 결정적이 됩니다.

 

SELECT  ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,

                                                               numsessions DESC, speaker) AS rownum,

   speaker, track, score, pctfilledevals, numsessions

FROM SpeakerStats

ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

 

결과 집합은 다음과 같습니다.

rownum speaker track score pctfilledevals numsessions

------ ---------- ---------- ----------- -------------- -----------

1 Ron Dev 9 30 3

2 Suzanne DB 9 30 3

3 Jessica Dev 9 19 1

4 Michele Sys 8 31 4

5 Kathy Sys 8 27 2

6 Mike DB 8 20 3

7 Kevin DB 7 25 4

8 Brian Sys 7 22 3

9 Robert Dev 6 28 2

10 Joe Dev 6 20 2

11 Dan Sys 3 22 4

 

새로운 순위 결정 함수의 중요한 장점 중 하나는 그 효율성에 있습니다. SQL Server의 최적화 프로그램은 그 값을 계산하는데 데이터를 단 한 번만 스캔하면 됩니다. 이 프로그램은 정렬 열 상에 배치된 인덱스의 정렬된 스캔을 이용하거나 해당 인덱스가 생성되지 않는 경우 데이터를 한 번 스캔하고 정렬하여 이 작업을 수행합니다.

또 다른 장점은 구문의 간결성입니다. 이전의 SQL Server 릴리스에서 사용된 집합 기반 접근 방식을 이용하여 순위 결정 값을 계산하는 과정이 얼마나 어렵고 비효율적인지 알아보기 위해 앞의 쿼리와 같은 결과를 반환하는 다음 SQL Server 2000 쿼리를 생각해 보기로 합시다.

 

SELECT

   (SELECT COUNT(*)

   FROM SpeakerStats AS S2

   WHERE S2.score > S1.score

          OR (S2.score = S1.score

                 AND S2.pctfilledevals > S1.pctfilledevals)

         OR (S2.score = S1.score AND S2.pctfilledevals = S1.pctfilledevals 

                AND S2.numsessions > S1.numsessions)

         OR (S2.score = S1.score 

                AND S2.pctfilledevals = S1.pctfilledevals

                AND S2.numsessions = S1.numsessions

                AND S2.speaker < S1.speaker)) + 1 AS rownum,

   speaker, track, score, pctfilledevals, numsessions

FROM SpeakerStats AS S1

ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

 

이 쿼리는 분명히 SQL Server 2005 쿼리에 비해 훨씬 더 복잡합니다. 더구나 SpeakerStats 테이블의 각 기본 행에 대해 SQL Server는 테이블의 다른 인스턴스에서 일치하는 모든 행을 스캔해야 합니다. 평균적으로 기본 테이블의 각 행별로 테이블의 거의 반에 해당하는 행을 스캔해야 합니다. SQL Server 2005 쿼리의 성능 저하는 선형으로 발생하나 SQL Server 2000 쿼리의 성능 저하는 기하급수적으로 발생합니다. 매우 작은 테이블의 경우에도 성능의 차이는 상당합니다. 예를 들어, SalesOrderID 순서에 따라 판매 주문에 대한 행 번호를 계산하기 위해 AdventureWorks 데이터베이스의 SalesOrderHeader 테이블을 쿼리하는 다음 쿼리의 성능을 테스트해봅시다. SalesOrderHeader 테이블에는 31,465개의 행이 있습니다. 첫 번째 쿼리는 SQL Server 2005 ROW_NUMBER 함수를 사용하고 두 번째 쿼리는 SQL Server 2000 하위 쿼리 기법을 사용하고 있습니다.

 

-- SQL Server 2005 query

SELECT SalesOrderID, ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownum

FROM Sales.SalesOrderHeader

 

-- SQL Server 2000 query

SELECT SalesOrderID,

            (SELECT COUNT(*)

             FROM Sales.SalesOrderHeader AS S2

             WHERE S2.SalesOrderID <= S1.SalesOrderID) AS rownum 

FROM Sales.SalesOrderHeader AS S1

 

이 테스트를 제 랩톱(Compaq Presario X1020U, CPU: Centrino 1.4GH, RAM: 1GB, 로컬 HD)에서 실행해 보았습니다. SQL Server 2005 쿼리는 단 1초 만에 끝났지만 SQL Server 2000 쿼리는 약 12분이 걸렸습니다.

행 번호를 적용하는 전형적인 경우는 쿼리의 결과를 페이징하는 경우입니다. 행의 수를 기준으로 한 페이지 크기와 페이지 번호가 주어지면 주어진 페이지에 속하는 행을 반환해야 합니다. 예를 들어, 점수 DESC, 화자 순서에 따라 한 페이지 크기를 3행으로 가정하여 SpeakerStats 테이블로부터 두 번째 페이지의 행을 반환하고자 한다고 합시다. 다음 쿼리는 파생 테이블 D의 특정 정렬에 따라 행 번호를 계산한 후 두 번째 페이지에 속한 행 번호 4-6인 행만을 필터링합니다.

 

SELECT *

FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,

                   speaker, track, score

               FROM SpeakerStats) AS D 

WHERE rownum BETWEEN 4 AND 6

ORDER BY score DESC, speaker

결과 집합은 다음과 같습니다.

 

rownum speaker track score

------ ---------- ---------- -----------

4 Kathy Sys 8

5 Michele Sys 8

6 Mike DB 8

보다 일반적인 용어로서 @pagenum 변수에는 페이지 번호를, @pagesize 변수에는 페이지 크기를 주면 다음 쿼리는 원하는 페이지에 속한 행을 반환합니다.

 

DECLARE @pagenum AS INT, @pagesize AS INT

SET @pagenum = 2

SET @pagesize = 3

 

SELECT *

FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,

                        speaker, track, score

              FROM SpeakerStats) AS D

WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

ORDER BY score DESC, speaker

위의 접근 방식은 특정 페이지의 행에만 관심이 있는 ad hoc 요청 시에 적합합니다. 그러나 이 접근 방식은 사용자가 복수의 요청을 실행하는 경우에는 적합하지 않으며 그 이유는 쿼리를 호출할 때마다 행 번호를 계산하기 위해 테이블 전체를 스캔해야 하기 때문입니다. 여러 페이지를 반복적으로 요청할 경우 보다 효율적인 페이징을 위해서는 먼저 다음과 같이 계산된 행 번호를 포함한 모든 기본 테이블 행을 임시 테이블에 기록하고 행 번호가 포함된 열을 인덱싱합니다.

 

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *

INTO #SpeakerStatsRN

FROM SpeakerStats

CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)

그런 다음 요청된 각 페이지 별로 다음과 같은 쿼리를 실행합니다.

 

SELECT rownum, speaker, track, score

FROM #SpeakerStatsRN

WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

ORDER BY score DESC, speaker

 

원하는 페이지에 속한 행만이 스캔됩니다.

 

분할

순위 결정 값은 테이블의 모든 행을 한 그룹으로 간주하여 계산되는 경우와는 반대로 행을 그룹으로 나눠 별도로 계산할 수 있습니다. 이렇게 하려면 PARTITION BY 절을 사용하여 순위 결정 값을 별도로 계산해야 하는 행 그룹을 식별하는 식의 목록을 지정해야 합니다. 예를 들어, 다음 쿼리는 점수 DESC, 화자 순서에 따라 각 트랙 안에 별도로 행 번호를 할당합니다.

SELECT track, ROW_NUMBER() OVER( PARTITION BY track ORDER BY score DESC, speaker) AS pos, speaker, score FROM SpeakerStats ORDER BY track, score DESC, speaker

결과 집합은 다음과 같습니다.

track pos speaker score ---------- --- ---------- ----------- DB 1 Suzanne 9 DB 2 Mike 8 DB 3 Kevin 7 Dev 1 Jessica 9 Dev 2 Ron 9 Dev 3 Joe 6 Dev 4 Robert 6 Sys 1 Kathy 8 Sys 2 Michele 8 Sys 3 Brian 7 Sys 4 Dan 3

PARTITION BY 절에 track 열을 지정하면 동일 트랙을 가진 각 그룹의 행에 대해 개별적으로 행 번호가 계산됩니다.

RANK, DENSE_RANK

RANK 및 DENSE_RANK 함수는 역시 행 그룹(분할) 내에서 선택적으로, 지정된 정렬에 따라 순위 결정 값을 제공한다는 점에서 ROW_NUMBER 함수와 매우 유사합니다. 그러나 ROW_NUMBER와는 달리 RANK와 DENSE_RANK는 ORDER BY 목록이 고유하지 않을 때 ORDER BY 목록에 동일 값을 가진 열에 대해 다른 순위가 할당되지 않도록 할 때 유용합니다. RANK와 DENSE_RANK의 목적과 이들 둘 사이의 차이는 실례를 통해 가장 잘 설명됩니다. 다음 쿼리는 점수 DESC 순서에 따라 각각의 화자에 대해 행 번호, 순위 및 조밀한 순위 값을 계산합니다.

SELECT speaker, track, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, RANK() OVER(ORDER BY score DESC) AS rnk, DENSE_RANK() OVER(ORDER BY score DESC) AS drnk FROM SpeakerStats ORDER BY score DESC

결과 집합은 다음과 같습니다.

speaker track score rownum rnk drnk ---------- ---------- ----------- ------ --- ---- Jessica Dev 9 1 1 1 Ron Dev 9 2 1 1 Suzanne DB 9 3 1 1 Kathy Sys 8 4 4 2 Michele Sys 8 5 4 2 Mike DB 8 6 4 2 Kevin DB 7 7 7 3 Brian Sys 7 8 7 3 Joe Dev 6 9 9 4 Robert Dev 6 10 9 4 Dan Sys 3 11 11 5

앞에서 설명한 바와 같이 score 열이 고유하지 않기 때문에 서로 다른 화자들이 같은 점수를 받게 될 수 있습니다. 행 번호는 내림차순 점수 순서를 나타내지만, 화자들의 점수는 같은데도 다른 행 번호를 받게 됩니다. 그러나 결과적으로는 같은 점수를 받은 모든 화자가 같은 순위와 조밀한 순위 값을 받는다는 점을 생각해야 합니다. 다시 말해 ORDER BY 목록이 고유하지 않으면 ROW_NUMBER는 결정적이 되지 않지만 RANK와 DENSE_RANK는 항상 결정적이 됩니다. 순위와 조밀한 순위 값의 차이는 순위는 보다 높은 점수를 받은 행의 수에 1을 더한 수를 나타내지만 조밀한 순위 값은 분명히 더 높은 점수의 개수에 1을 더한 수를 나타냅니다. 지금까지 배운 내용을 통해, ORDER BY 목록이 고유하다면 ROW_NUMBER, RANK, DENSE_RANK가 정확히 동일한 값을 생성한다는 사실을 추론할 수 있습니다.

NTILE

NTILE를 이용하면 쿼리의 결과 행을 지정된 순서에 따라 지정된 수의 그룹(타일)으로 분리할 수 있습니다. 각 그룹의 행들은 첫 번째 그룹에 1, 두 번째에 2, 등으로 시작해서 각각 다른 번호를 갖게 됩니다. 함수 이름 뒤의 괄호 안에는 요청할 그룹의 번호를, OVER 옵션의 ORDER BY 절에는 요청할 정렬을 지정합니다. 한 그룹에 속한 행의 개수는 total_num_rows / num_groups로 계산됩니다. 나머지 n이 남는 경우 처음의 n 그룹들이 추가 행을 갖게 됩니다. 따라서 모든 그룹이 같은 개수의 행을 갖지 않을 수도 있으나 그룹의 크기가 달라도 기껏해야 한 행 차이가 나는데 불과합니다. 예를 들어, 다음 쿼리는 내림차순 점수 순서에 따라 서로 다른 화자 행들에 3개의 그룹 번호를 할당합니다.

SELECT speaker, track, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, NTILE(3) OVER(ORDER BY score DESC) AS tile FROM SpeakerStats ORDER BY score DESC

결과 집합은 다음과 같습니다.

speaker track score rownum tile ---------- ---------- ----------- ------ ---- Jessica Dev 9 1 1 Ron Dev 9 2 1 Suzanne DB 9 3 1 Kathy Sys 8 4 1 Michele Sys 8 5 2 Mike DB 8 6 2 Kevin DB 7 7 2 Brian Sys 7 8 2 Joe Dev 6 9 3 Robert Dev 6 10 3 Dan Sys 3 11 3

SpeakerStats 테이블에는 11명의 화자가 있습니다. 11을 3으로 나누면 그룹 크기는 3이 되고 나머지 2가 남으므로 처음 2개 그룹은 추가 행을 갖게 되지만(그룹별 4행) 세 번째 그룹은 그렇지 않다(3행 그대로)는 것을 의미합니다. 행 1에서 4까지 그룹 번호(타일 번호) 1이 할당되고, 행 5에서 8까지는 그룹 번호 2, 행 9에서 11까지는 그룹 번호 3이 할당됩니다. 이 정보를 이용하여 각 단계별로 항목이 고루 분포된 히스토그램을 생성할 수 있습니다. 이 사례의 경우, 첫 단계는 가장 높은 점수를 받은 화자들을 대표하고 두 번째는 중간 점수를 받은 화자들을 대표하며 세 번째는 가장 낮은 점수를 받은 화자들을 대표합니다. CASE 식을 사용하여 그룹 번호에 설명형의 의미 있는 대체 표현을 제공할 수 있습니다.

SELECT speaker, track, score, CASE NTILE(3) OVER(ORDER BY score DESC) WHEN 1 THEN 'High' WHEN 2 THEN 'Medium' WHEN 3 THEN 'Low' END AS scorecategory FROM SpeakerStats ORDER BY track, speaker

결과 집합은 다음과 같습니다.

speaker track score scorecategory ---------- ---------- ----------- ------------- Kevin DB 7 Medium Mike DB 8 Medium Suzanne DB 9 High Jessica Dev 9 High Joe Dev 6 Low Robert Dev 6 Low Ron Dev 9 High Brian Sys 7 Medium Dan Sys 3 Low Kathy Sys 8 High Michele Sys 8 Medium