MS SQL 서버 2005에서는 순위 관련한 다양한 함수를 제공하고 있습니다. 이번 강좌에서는 이 함수듫을 살펴보고자 하며 그 함수들은 다음과 같습니다.
o RANK
o DENS_RANK
o ROW_NUMBER
o NTILE
위 함수들을 이용하면 기존 2000 버전에서 복잡하게 구현해야 되는 부분을 단순한 로직으로 구현 할 수 있습니다. 우선 각 함수의 기능을 살펴보기 위해 사용될 간단한 테이블 하나를 Tempdb에 만들도록 하겠습니다.
[소스1]
USE Tempdb IF OBJECT_ID('Customer', 'U') IS NOT NULL CREATE TABLE Customer ( INSERT INTO Customer VALUES(1, 'Gilyong', 'M', 70) SELECT Name, Gender, Score |
위에서 만든 Customer 테이블에는 10명의 고객정보가 성별(Gender), 점수(Score)와 함께 기록되어 있습니다. 마지막의 SELECT는 다음과 같은 결과를 보여줍니다.
[결과1]
Name Gender Score---------- ------ -----------Hyori F 90MiJa F 90Deoken M 80Kobong M 80ChoiGuk M 75Hani F 70Gilyong M 70Hodong M 70Juyeon F 60Minsoo M 50(10개 행 적용됨) |
1. RANK
RANK 함수는 각 행의 순위를 구할 때 사용하는 함수입니다. 예를 들어 석차를 구한다거나 고객의 포인트를 기준으로 순위를 산출하는 경우 사용 될 수 있습니다. 일반적으로 우리가 알고 있는 순위를 생각하시면 됩니다. RANK 함수를 포함해서 앞으로 다루어지게 될 함수들은 OVER절과 같이 사용되어 집니다. 순위를 위해 그 기준을 설정하는 부분이 OVER 절입니다. 기준이 없다면 순위는 아무 의미가 없을 것입니다. 다음은 RANK 함수를 이용해 위의 결과에서 Score 를 기준으로 순위를 같이 표시하고자 한다면 다음과 같이 하면 됩니다.
[소스2]
SELECT Name, Gender, Score, |
위 구문에서 RANK() OVER(ORDER BY Score DESC) AS ScoreRank 부분을 잘 보시면 됩니다. RANK 함수를 이용하고 있으며 OVER 절에 ORDER BY Score DESC 처럼 순위의 기준을 지정해 주고 있습니다. AS ScoreRank 부분은 순위 값을 ScoreRank 로 하겠다는 별칭을 지정하는 일반적인 부분입니다. 결과는 다음과 같습니다.
[결과2]
Name Gender Score ScoreRank---------- ------ ----------- --------------------Hyori F 90 1MiJa F 90 1Deoken M 80 3Kobong M 80 3ChoiGuk M 75 5Hani F 70 6Gilyong M 70 6Hodong M 70 6Juyeon F 60 9Minsoo M 50 10(10개 행 적용됨) |
만일 위 결과에서 남자와 여자를 구분해 그 안에서의 순위를 구하고자 하는 경우가 있을 것입니다. 이런 경우에 OVER 절 안에 PARTITION 구문을 포함해 주면 됩니다. 어떻게 영역을 구분하여 순위를 결정할지를 정하는 것입니다. PARTITION 부분이 없으며 전체 결과를 대상으로 순위를 정하게 되는 것입니다. PARTITION을 사용한 구문은 다음과 같습니다.
[소스3]
SELECT Name, Gender, Score, |
OVER 절 안에 PARTITION BY Gender 같이 하여 성별(Gender)로 영역을 구분해 그 안에서 순위를 정하겠다고 지정한 것입니다. 수행 결과는 다음과 같습니다.
[결과3]
Name Gender Score ScoreRank---------- ------ ----------- --------------------Hyori F 90 1MiJa F 90 1Hani F 70 3Juyeon F 60 4Deoken M 80 1Kobong M 80 1ChoiGuk M 75 3Gilyong M 70 4Hodong M 70 4Minsoo M 50 6(10개 행 적용됨) |
결과에서 Gender와 ScoreRank 부분을 보시면 PARTITION의 의미를 쉽게 알 수 있을 것입니다.
자, 위 결과들에서 보면 순위 1이 두명인 경우 세 번째는 순위가 3이 됩니다. 이것이 일반적인 순위 이기는 하지만 세 번째가 3이 아닌 2가 되게 할 수 있습니다. 바로 DENS_RANK 함수가 이때 사용되는 것입니다.
2. DENS_RANK
다음은 DENS_RANK 함수를 이용해 다시 산출해 본 것입니다.
[소스4]
SELECT Name, Gender, Score, |
결과는 다음과 같습니다.
[결과4]
Name Gender Score ScoreRank---------- ------ ----------- --------------------Hyori F 90 1MiJa F 90 1Deoken M 80 2Kobong M 80 2ChoiGuk M 75 3Hani F 70 4Gilyong M 70 4Hodong M 70 4Juyeon F 60 5Minsoo M 50 6(10개 행 적용됨) |
ScoreRank 부분을 보시면 1, 1, 2, 2, 3... 처럼 순위가 연속적인 숫자로 표시됨을 알 수 있습니다. DENS_RANK 함수에서도 RANK 함수처럼 PARTITION을 사용할 수 있습니다. 다음은 PARTITION을 사용한 구문과 그 결과 입니다.
[소스5]
SELECT Name, Gender, Score, |
[결과5]
Name Gender Score ScoreRank---------- ------ ----------- --------------------Hyori F 90 1MiJa F 90 1Hani F 70 2Juyeon F 60 3Deoken M 80 1Kobong M 80 1ChoiGuk M 75 2Gilyong M 70 3Hodong M 70 3Minsoo M 50 4(10개 행 적용됨) |
3. ROW_NUMBER
ROW_NUMBER 함수는 사용 방법은 위 두 함수와 같습니다. 단, 순위가 아니다 순서를 표시해 주는 함수입니다. 특정 컬럼을 기준으로 1부터 시작해서 1씩 증가되는 숫자를 표시해 주는 함수 입니다. 다음의 예를 보시면 이해가 될 것입니다.
[소스6]
SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS RowNum, |
앞에서 본 두 함수와 사용방법이 동일하며 함수 이름만 바뀐것을 볼 수 있습니다. (물론 의미 있는 결과를 보기 위해 함수를 다른 컬럼보다 앞으로 위치 시킨 부분도 달라졌다고 할 수 있겠네요^^) 결과는 다음과 같습니다.
[결과6]
RowNum Name Gender Score-------------------- ---------- ------ -----------1 Hyori F 902 MiJa F 903 Deoken M 804 Kobong M 805 ChoiGuk M 756 Hani F 707 Gilyong M 708 Hodong M 709 Juyeon F 6010 Minsoo M 50(10개 행 적용됨) |
RowNum에 표시된 1부터 10이 ROW_NUMBER 함수가 준 결과 입니다. ROW_NUMBER 역시 PARTITION 부분을 포함 할 수 있습니다.
[소스7]
SELECT ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY Score DESC) AS RowNum, |
결과는 다음과 같습니다.
[결과7]
RowNum Name Gender Score-------------------- ---------- ------ -----------1 Hyori F 902 MiJa F 903 Hani F 704 Juyeon F 601 Deoken M 802 Kobong M 803 ChoiGuk M 754 Gilyong M 705 Hodong M 706 Minsoo M 50(10개 행 적용됨) |
Gender 컬럼을 기준으로 RowNum이 다시 시작됨을 볼 수 있습니다.
4. NTILE
마지막으로 NTILE 함수를 살펴보도록 하겠습니다. 10명의 고객을 Score를 기준으로 하여 세 개의 그룹으로 분류 할 경우 Hodong은 과연 어느 그룹에 포함될지 알고 싶을 수 있습니다. 이때 사용되는 것이 NTILE 함수 입니다. NTILE(3) 처럼 NTILE 함수에 분류할 그룹의 수를 지정해 주어야 합니다. 이 부분이 위에서 살펴본 다름 함수들과 다릅니다. 그럼 NTILE 함수를 이용해 보도록 하겠습니다.
[소스8]
SELECT NTILE(3) OVER(ORDER BY Score DESC) AS ScoreBand, |
결과는 다음과 같습니다.
[결과8]
ScoreBand Name Gender Score-------------------- ---------- ------ -----------1 Hyori F 901 MiJa F 901 Deoken M 801 Kobong M 802 ChoiGuk M 752 Hani F 702 Gilyong M 703 Hodong M 703 Juyeon F 603 Minsoo M 50(10개 행 적용됨) |
ScoreBand 부분을 보시면 1, 2, 3의 숫자가 그룹을 구분해 주고 있습니다. 결국 Hodong은 Score를 기준으로 했을 경우 세 번째 그룹에 포함됨을 알 수 있습니다. 행의 숫자가 NTILE 함수에 전달된 값으로 정확히 나누어 떨어지지 않으면 각 그룹에 포함된 행의 수가 다를 수 있습니다. 위 예에서는 1이 네개, 2와 3은 세개임을 알 수 있습니다.
NTILE 함수도 당연히 PARTITION 부분을 포함 할 수 있습니다.
[소스9]
SELECT NTILE(3) OVER(PARTITION BY Gender ORDER BY Score DESC) AS ScoreBand, |
결과는 다음과 같습니다.
[결과9]
ScoreBand Name Gender Score-------------------- ---------- ------ -----------1 Hyori F 901 MiJa F 902 Hani F 703 Juyeon F 601 Deoken M 801 Kobong M 802 ChoiGuk M 752 Gilyong M 703 Hodong M 703 Minsoo M 50(10개 행 적용됨) |
5. 정리
위에서 살펴본 함수들이 기존 2000 버전에 없어 개발자 분들이 많은 불편을 겪었습니다. 그래서 나름대로 순위를 구하는 방법을 사용하고 있었습니다. 이런 점에서 위 함수들의 등장은 반가울 따름입니다. 분명 코드의 복잡성을 줄여주어 유지 관리 및 소스 파악에 많은 이점을 줄 것입니다. MS SQL 서버 2005를 사용하고 계시다면 위 함수들을 이용해 보시기 바랍니다.