순위에 대해 알아보자 (feat. OVER, RANK, ROW_NUMBER)

안녕하세요, 끙정입니다.

 

오늘은 순위에 대해서 알아보겠습니다.

 

1. OVER

SUM, AVG, MIN, MAX 집계 함수는 주로 GROUP BY와 함께 사용하며,

은근히 제약이 많아 복잡한 집계를 내는데 한계가 있습니다.

OVER는 집계 함수를 보조하여 섬세한 그룹핑을 지원하면서도 구문은 더 간단한 축약형입니다.

 

집계함수() OVER(PARTITION BY 그룹핑 기준 ORDER BY 정렬기준 ROWS | RANGE)

 

OVER 안에 상세 옵션이 들어가며 필요 없으면 생략 가능합니다.

PARTITION BY 절은 GROUP BY와 유사한 그룹핑을 수행하며

ORDER BY 절은 통계를 낼 정렬 순서를 지정합니다.

 

다소 난해해 보일 수 있지만, 기존 집계 구문의 한계를 살펴보며 OVER를 이해해 보겠습니다.

 

tStaff 테이블에서 이름, 부서, 월급과 함께 월급 총합을 레코드별로 출력하고 싶습니다.

의미가 없어 보이는 쿼리지만 활용도가 있습니다.

 

목표는 이 모양이다-!

 

/* 공존할 수 없는 필드와 집계함수로 에러가 뜨는 쿼리다. */
SELECT name, depart, salary, SUM(salary) FROM tStaff;

/* 에러가 뜨지 않지만 원하는 결과는 뽑을 수 없다. */
SELECT depart, SUM(salary) FROM tStaff GROUP BY depart;

/* 서브쿼리를 활용하면 원하는 결과를 볼 수 있다. */
SELECT name, depart, salary, (SELECT SUM(salary) FROM tStaff) AS 월급총합 FROM tStaff;

/* OVER()를 사용하면 복잡한 서브쿼리 없이도 동일한 결과를 출력할 수 있다. */
SELECT name, depart, salary, SUM(salary) OVER() AS 월급총합 FROM tStaff;

 

첫번째 쿼리는 에러다. 사실 말이 안되는 쿼리다.

GROUP BY를 쓰기는 하는데 필드 기준이라 원하는 결과는 아니다.

서브쿼리를 통해 출력에 성공했다-! 근데 조금 복잡하다.

OVER() 를 쓰면 이렇게 쉽습니다.

 

순서대로 보았듯이 집계 함수와 GROUP BY를 사용하는 규칙은 매우 까탈스럽습니다.

또한 서브 쿼리는 복잡해서 쿼리가 길어집니다.

그러나 OVER 절은 이러한 복잡한 쿼리 없이 간단하게 원하는 결과를 뽑을 수 있습니다.

 

OVER절은 아무 옵션 없이 빈 괄호만 적으면 전체행을 대상으로 집계하라는 뜻이며 전체 월급 총합을 구합니다.

이 쿼리를 활용하는 방법은 아래와 같이 월급의 비율을 계산하는 것이 있습니다.

아래와 같이 말이죠-!

 

SELECT name, depart, salary, ROUND(salary * 100.0 / SUM(salary) OVER(), 2) AS 월급비율
FROM tStaff;

 

장보고가 6.33으로 가장 높은 월급 비중을 가지고 있음을 알 수 있다.

 

전체 월급 총합이 아닌 소속 부서에 대한 월급 총합을 구해보겠습니다.

 

SELECT name, depart, salary,
(SELECT SUM(salary) FROM tStaff WHERE depart = A.depart) AS 부서월급총합
FROM tStaff A ORDER BY depart;

 

 

OVER 절로 아주 쉽게 똑같은 결과를 출력할 수 있습니다.

 

SELECT name, depart, salary, SUM(salary) OVER(PARTITION BY depart)
AS 부서월급총합 FROM tStaff;

 

서브쿼리보다 훨씬 쉽다...서브쿼리 너무 어렵다...

 

그룹핑 기준으로 자동 정렬하므로 뒤의 ORDER BY는 빼도 상관없습니다.

OVER 안에 ORDERY BY 절을 사용하면 정렬을 수행하면서 집계를 계산합니다.

다음 쿼리는 직원 목록을 출력하면서 앞쪽 직원의 월급을 다 더한 누적 월급을 계산합니다.

 

SELECT name, depart, salary, SUM(salary) OVER(ORDER BY name)
AS 누적월급 FROM tStaff;

 

와...꿀이다.

 

이 통계를 내려면 누가 더 앞인지 순서를 결정해야 하니 OVER 안에 ORDER BY가 필요합니다.

OVER구문 스스로 정렬을 수행하기 때문에 전체 쿼리의 ORDER BY는 생략해도 상관이 없습니다.

이제 부서별 월급 누적 합계를 구해보겠습니다.

 

SELECT name, depart, salary, SUM(salary) OVER(PARTITION BY depart ORDER BY name) AS 누적월급
FROM tStaff;

 

 

SUM함수 말고도 AVG나 MIN, MAX 등의 집계 함수도 물론 사용할 수 있습니다.

 

2. 윈도우절

OVER 괄호 안에 PARTITION BY 절은 그룹핑을, ORDER BY 절은 정렬을 지정하여 집계를 낼 범위와 순서를 통제합니다.

더 정밀한 범위를 지정할 때 윈도우절 WINDOWING CLAUSE를 사용합니다.

 

ROWS BETWEEN start AND end
RANGE BETWEEN start AND end

 

start와 end는 행 범위를 지정하여 집계를 뽑을 정확한 범위를 기술합니다.

전체 결과셋의 일부만 들여다본다고 해서 윈도우절입니다.

각각 다음 세 가지 종류를 지정할 수 있습니다.

 

start	설명	end	설명
UNBOUNDED PRECEDING	처음부터	UNBOUNDED FOLLOWING	마지막까지
CURRENT ROW	현재행부터	CURRENT ROW	현재행까지
n PRECEDING	n 행 앞부터	n FOLLOWING	n 행 뒤까지

 

처음부터 끝까지, 앞쪽 2행에서 현재행까지 등으로 범위를 지정합니다.

다음 쿼리는 나보다 앞 뒤로 각 1명씩까지의 월급 합계를 계산합니다.

앞뒤 순서를 결정하려면 정렬부터 해야 하므로 ORDER BY 절이 있어야 합니다.

 

SELECT name, depart, salary,
SUM(salary) OVER(ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS 누적월급 FROM tStaff;

 

 

김유신은 앞의 강감찬, 뒤의 논개의 월급을 더하고, 논개는 앞의 김유신, 뒤의 대조영을 더해서 출력합니다.

 

다음 쿼리는 나부터 마지막 직원까지의 월급 총합을 구합니다.

 

SELECT name, depart, salary,
SUM(salary) OVER(ORDER BY name ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
AS 누적월급 FROM tStaff ORDER BY name;

 

 

아래쪽으로 내려올수록 뒤쪽의 직원 수가 줄어들기 때문에 월급 총합이 점점 감소합니다.

PARTITION BY로 그룹핑하면 그룹 내에서의 범위로 제한합니다.

 

다음 쿼리는 같은 부서 내에서 입사일 기준으로 나부터 2명 이후까지 만의 합계를 구합니다.

 

SELECT name, depart, joindate, salary,
SUM(salary) OVER(PARTITION BY depart ORDER BY joindate
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS 누적월급 FROM tStaff;

 

 

PARTITION BY depart를 통해 부서별로 구분하고,

ORDER BY joindate로 정렬합니다.

CURRENT ROW로 현재행부터 2 FOLLOWING으로 다음 2명까지 누적시킵니다.

 

ROWS 옵션은 지정한 물리적인 행순서로 범위를 취하는데 비해

RANGE 옵션은 논리적인 값으로 범위를 취합니다.

보통의 경우는 별 차이가 없지만 동순위가 있을 때 포함 여부가 다릅니다.

 

/* ROWS 문 */
SELECT name, depart, salary, SUM(salary) OVER(ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적월급 FROM tStaff;

/* RANGE 문 */
SELECT name, depart, salary, SUM(salary) OVER(ORDER BY salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적월급 FROM tStaff;

 

자세히 보면 값이 미세하게 다르다-!

 

UNBOUNDED PRECEDING을 통해 처음부터

CURRENT ROW를 통해 현재행까지 누적을 시킵니다.

동일한 월급이 없는 윤봉길은 두 결과가 똑같이 3046이지만,

동일한 월급이 존재하는 정몽주와 안창호는 3416, 3786 / 3786, 3786으로 다릅니다.

RANGE는 행 번호가 아닌 값으로 범위를 정하기 때문에 동일한 값이 존재할 경우,

위와 같이 동일한 값의 행을 같은 범위로 산정하여 동시에 더하게 됩니다.

이와 더불어 RANGE는 특정 행 번호를 지정하는 n PRECEDING과 n FOLLOWING은 쓸 수 없습니다.

처음부터, 현재행, 끝까지 세 가지 범위 지정만 가능합니다.

 

3. RANK

RANK 함수는 지정한 기준으로 정렬한 순위를 구합니다.

 

RANK() OVER([PARTITION BY 구분기준] ORDER BY 정렬기준)

 

이 함수의 리턴 값을 필드 목록에 적으면 석차까지 포함한 완전한 성적표를 얻을 수 있습니다.

OVER절과 함께 사용하며 순위를 구하려면 정렬 순서가 있어야 하므로 ORDER BY는 필수적입니다.

 

SELECT RANK() OVER(ORDER BY salary DESC) AS 순위, tStaff.* FROM tStaff;

 

순위가 나타났다-!

 

RANK 함수로 순위를 구해 첫 필드에 출력하고 tStaff의 나머지 필드를 모두 출력합니다.

SQL Server, MariaDB는 그냥 * 로 출력해도 무방하지만 호환성을 위해 테이블명.*으로 출력하는 것이 좋습니다.

월급이 같은 사람은 공동 순위로 매깁니다.

6, 7 등이 없으니 다음 순위인 이사부는 바로 8등입니다.

 

RANK 구분에 ORDER BY절이 있으니 자동으로 정렬이 되지만,

다른 기준으로 정렬을 하고자 한다면 전체 쿼리에 ORDER BY를 따로 붙이면 됩니다.

고과가 높은 순서대로 월급 순위를 출력하겠습니다.

 

SELECT RANK() OVER(ORDER BY salary DESC) AS 순위, tStaff.* FROM tStaff ORDER BY score DESC;

 

신사임당, 김유신은 월급에 걸맞는 퍼포먼스를 냈다-!

 

WHERE 절을 통해 특정 조건을 기준으로 순위를 매길 수도 있습니다.

 

/* 영업부만 순위를 구해서 출력해봅니다. */
SELECT RANK() OVER(ORDER BY salary DESC) AS 순위, tStaff.* FROM tStaff WHERE depart = '영업부';

 

 

그룹별로 순위를 매기고자 할 때는 OVER에 PARTITION BY 절을 추가하면 됩니다.

 

SELECT RANK() OVER(PARTITION BY depart ORDER BY salary DESC) AS 순위, tStaff.* FROM tStaff;

 

부서별로 순위가 새로 매겨진다-!

 

RANK 함수는 공동 순위가 있으면 이후 공동 순위만큼 건너뛰어 순위가 불연속적입니다.

이에 비해 DENSE_RANK함수는 공동 순위는 계산하지만 그다음 순위를 이어서 붙입니다.

SELECT DENSE_RANK() OVER(ORDER BY salary DESC) AS 순위, tStaff.* FROM tStaff;

 

5, 5, 5 등 뒤에 6등이 온다-!

4. RWO_NUMBER

ROW_NUMBER 함수는 1, 2, 3, 4 식으로 일련번호를 붙입니다.

공동 순위를 인정하지 않으며 따라서 중간에 순위가 비는 경우도 없습니다.

마찬가지로 번호를 붙일 순서가 필요하므로 ORDER BY가 필요합니다.

특별한 순서를 지정하고 싶지 않다면 PK순으로 정렬합니다.

 

SELECT ROW_NUMBER() OVER(ORDER BY name) AS 순서, tStaff.* FROM tStaff;

 

이름 순서대로 번호를 매긴다-!

 

ROW_NUMBER를 활용하는 대표적인 예는 레코드를 나누어 출력하는 것입니다.

직원 테이블을 일정한 페이지 크기로 나눠 출력해보겠습니다.

 

SELECT ROW_NUMBER() OVER(ORDER BY name) AS 순서, tStaff.* FROM tStaff
ORDER BY 순서 OFFSET 2 * 5 ROWS FETCH NEXT 5 ROWS ONLY;

 

5개씩 출력하여 3번째 페이지를 출력

 

OFFSET FETCH 구문이 ORDER BY의 옵션 절이어서 ORDER BY가 두 번 중복되는 건 어쩔 수 없습니다.

 

 

그만 알아보겠습니다.

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24