[SQL] 랭크함수와 PARTITION BY를 이용하여 1등만 뽑아보자.

 

SQL에서 랭크 함수는 많이 사용된다.

ROW_NUMBER, RANK, DENSE_RANK가 대표적인 랭크 함수인데,

차이점은 아래 글을 참고하자.

 

2019/12/04 - [개발/MSSQL] - [SQL] ROW_NUMBER, RANK, DENSE_RANK 차이점

[SQL] ROW_NUMBER, RANK, DENSE_RANK 차이점

랭크 함수를 이용해서 순위를 구할 수 있다.

그런데, 해당 순위의 1등 혹은 N등만 구하고 싶을 때가 있다.

그럴 때 PARTITION BY를 이용하면 너무너무 쉽게 구할 수 있다.

 

CREATE TABLE #MEMBER(
  num          INT IDENTITY PRIMARY KEY,
  name         NVARCHAR(20),
   [subject]    NVARCHAR(10),
   score        INT
)
 
INSERT INTO #MEMBER (name, [subject], score) VALUES('홍길동', '국어', 20);
INSERT INTO #MEMBER (name, [subject], score) VALUES('홍길동', '수학', 85);
INSERT INTO #MEMBER (name, [subject], score) VALUES('홍길동', '영어', 70);
INSERT INTO #MEMBER (name, [subject], score) VALUES('홍길동', '과학', 100);
INSERT INTO #MEMBER (name, [subject], score) VALUES('홍길동', '국사', 50);
 
INSERT INTO #MEMBER (name, [subject], score) VALUES('고길동', '국어', 100);
INSERT INTO #MEMBER (name, [subject], score) VALUES('고길동', '수학', 20);
INSERT INTO #MEMBER (name, [subject], score) VALUES('고길동', '영어', 40);
INSERT INTO #MEMBER (name, [subject], score) VALUES('고길동', '과학', 95);
INSERT INTO #MEMBER (name, [subject], score) VALUES('고길동', '국사', 70);
 
INSERT INTO #MEMBER (name, [subject], score) VALUES('피카츄', '국어', 80);
INSERT INTO #MEMBER (name, [subject], score) VALUES('피카츄', '수학', 55);
INSERT INTO #MEMBER (name, [subject], score) VALUES('피카츄', '영어', 70);
INSERT INTO #MEMBER (name, [subject], score) VALUES('피카츄', '과학', 65);
INSERT INTO #MEMBER (name, [subject], score) VALUES('피카츄', '국사', 40);
 
INSERT INTO #MEMBER (name, [subject], score) VALUES('파이리', '국어', 77);
INSERT INTO #MEMBER (name, [subject], score) VALUES('파이리', '수학', 85);
INSERT INTO #MEMBER (name, [subject], score) VALUES('파이리', '영어', 92);
INSERT INTO #MEMBER (name, [subject], score) VALUES('파이리', '과학', 48);
INSERT INTO #MEMBER (name, [subject], score) VALUES('파이리', '국사', 65);
 
INSERT INTO #MEMBER (name, [subject], score) VALUES('꼬북이', '국어', 72);
INSERT INTO #MEMBER (name, [subject], score) VALUES('꼬북이', '수학', 89);
INSERT INTO #MEMBER (name, [subject], score) VALUES('꼬북이', '영어', 92);
INSERT INTO #MEMBER (name, [subject], score) VALUES('꼬북이', '과학', 70);
INSERT INTO #MEMBER (name, [subject], score) VALUES('꼬북이', '국사', 50);

 

 

#MEMBER 임시 테이블을 생성하였고

이름 / 과목 / 점수의 컬럼을 가지고 있다.

 

내가 추출하고 싶은 데이터는

과목별 1등이 누구인지?

사람별 점수가 가장 높은 과목이 무엇인지? 두 가지 데이터를 뽑고 싶다.

 

ROW_NUMBER, RANK, DENSE_RANK가 랭크 함수 중에서,

ROW_NUMBER를 사용하는 것이 가장 좋다.

 

그 이유는 ROW_NUMBER만 순위의 중복이 허용되지 않기 때문이다.

그래서 PARTITION BY를 사용할 때에는 랭크 함수 중에서 ROW_NUMBER를 사용하는 것이 가장 좋다.

 

-- 과목별 1등
SELECT num, name, [subject], score
    FROM (
        SELECT 
            ROW_NUMBER() OVER (PARTITION BY [subject] ORDER BY score DESC) AS [rank],
            num, name, [subject], score
        FROM #MEMBER ) AS M
    WHERE M.[rank] = 1
    ORDER BY [subject];
 
-- 사람별 점수가 가장 높은 과목
SELECT num, name, [subject], score
    FROM (
        SELECT 
            ROW_NUMBER() OVER (PARTITION BY name ORDER BY score DESC) AS [rank],
            num, name, [subject], score
        FROM #MEMBER ) AS M
    WHERE M.[rank] = 1
    ORDER BY name;

 

 

문법은 간단하다. 랭크 함수에서 ORDER BY 하기 전에 PARTITION BY [컬럼]만 추가해주면 된다.

PARTITION BY [컬럼]에서 [컬럼]은 그룹화할 컬럼을 넣어주면 된다.

 

첫 번째에서는 과목(subject)을 두 번째에서는 이름(name)을 그룹화하였다.

WHERE 조건에서 rank = 1 을 넣어주어, 순위가 1위인 데이터만 조회하게끔 해주었다. 2위를 얻기 위해서는 2를, 3위를 얻기 위해서는 3을 조건절에 넣어주면 된다.(당연한 소리...)

 

 

 

위에 결과처럼, 과목별 순위와 사람별 가장 높은 점수의 과목을 얻을 수 있다.

랭크 함수와 PARTITION BY는 데이터 집계할 때 많이 사용되니, 필히 숙지하자.