PARTITION BY란?
PARTITION BY는 지정한 열의 값별로 계산 처리를 하는 기능입니다.
'부서별 건수를 매기고 싶다', '상품별 순위를 매기고 싶다' 등의 처리를 하고 싶을 때 PARTITION BY가 사용됩니다.
분석 함수나 분석 함수와 함께 사용되는 OVER 구문과 함께 PARTITION BY도 함께 사용하는 경우가 많습니다.
분석 함수란?
"애초에 분석 함수가 뭐야?" 라고 생각하는 사람도 있을 것입니다.
분석 함수는 그룹 내에서 지정된 범위를 집계하고, 집계 결과를 각 행으로 되돌려주는 함수를 말합니다.
SQL 중에서도 분석 함수는 비교적 최근에 추가된 기능이며, 윈도우(Window) 함수라고도 합니다.
분석 함수에서는 여러 개의 서브쿼리를 조합해야 했던 것을 보다 간단한 SQL 문장으로 구현할 수 있게 되었습니다.
분석 함수 중에서 PARTITION BY는 열을 지정하기 위해 사용합니다.
분석 함수에서 PARTITION BY 사용 예시
다음은 분석 함수에서 PARTITION BY를 사용하는 예를 소개합니다.
다음 쿼리에서는 PARTITION BY와 ORDER BY를 사용하여 NAME별로 PRICE가 큰 순서대로 순서를 지정합다.
SELECT id, name, price,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY price DESC)
FROM my_table;
ID NAME PRICE ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY PRICE DESC)
---------- ---------- ---------- -----------------------------------------------
1 홍길동 570 1
3 홍길동 440 2
2 홍길동 200 3
5 홍길동 80 4
6 영수 350 1
4 영수 130 2
위와 같이 NAME별로 PRICE가 큰 순서대로 표시할 수 있습니다.
지정 방법을 조금 변형하여 각 NAME별로 가장 큰 PRICE를 가진 행을 가져오는 쿼리도 만들 수 있습니다.
OVER구문의 기본 사용법
앞의 분석 함수 예제에서 'OVER 구문'을 사용했습니다.
여기서는 OVER 구문에 대해 설명합니다.
OVER 구문에서는 다음과 같은 방법을 사용하여 집계 대상 범위를 지정할 수 있습니다.
- PARTITION BY
- ORDER BY
- WINDOW(Frame)
분석 함수에서 OVER 구문을 사용할 때는 다음과 같이 지정합니다.
OVER (PARTITION BY 집계단위 ORDER BY 표시순서)
PARTITION BY가 집계하고자 하는 열을 지정하는 반면, ORDER BY는 표시 순서를 지정합니다.
앞의 예에서도 사용한 'ROW_NUMBER(행 번호)'와 같이 집계 결과를 정렬하고 싶을 때 많이 사용합니다.
WINDOW는 ORDER BY의 옵션처럼 취급됩니다.
행을 정렬할 때, 각 행에서 어디서부터 어디까지를 집계할 것인지 지정할 수 있습니다. 따라서 WINDOW를 지정할 때는 반드시 ORDER BY가 필요합니다.
ORDER BY에서는 선두 행부터 현재 행까지가 대상 범위가 되는데, 이는 WINDOW를 생략한 경우의 기본 동작입니다.
이를 WINDOW를 사용하면 대상 범위를 자유롭게 설정할 수 있습니다.
단, 분석 함수에 따라 WINDOW를 사용할 수 없는 함수도 있으므로 수동으로 확인이 필요합니다.
PARTITION BY와 SUM을 사용하여 누적값 구하기
여기서는 PARTITION BY와 SUM을 사용하여 NAME별로 PRICE의 합계를 구해봅시다.
다음과 같이 쿼리를 작성합니다.
SELECT id, name, price,
SUM(price) OVER (PARTITION BY name ORDER BY price DESC)
FROM my_table;
ID NAME PRICE SUM(PRICE)OVER(PARTITION BY NAME ORDER BY PRICE DESC)
---------- ---------- ---------- -----------------------------------------------
1 홍길동 570 570
3 홍길동 440 1010
2 홍길동 200 1210
5 홍길동 80 1290
6 영수 350 350
4 영수 130 480
PARTITION BY에 의해 NAME으로 그룹화되어 있고, ORDER BY에 의해 순서가 정해져 누적되어 있습니다.
PARTITION BY와 WHERE를 사용하여 최대값을 가진 행을 추출합니다.
' 분석 함수에서 PARTITION BY를 사용하는 예 '에서 조금 설명했듯이, 분석 함수를 사용하면 그룹 내 최대값, 최소값 등 특정 행을 추출할 수 있습니다.
여기서는 PARTITION BY와 WHERE를 사용하여 최대값을 가진 행을 추출해 봅시다. 최대값을 가진 행을 추출하려면 다음과 같이 쿼리를 작성합니다.
SELECT * FROM (
SELECT id, name, price,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY price DESC) rn
FROM my_table)
WHERE rn = 1;
ID NAME PRICE RN
---------- ---------- ---------- ----------
1 홍길동 570 1
6 영수 350 1
NAME마다 RN=1(최대값)의 행을 추출할 수 있었습니다.
PARTITION BY와 RANK를 사용하여 순위 매기기
여기서는 PARTITION BY와 RANK를 사용하여 순위를 매기는 쿼리를 살펴봅시다.
RANK는 결과 집합의 순위를 매기는 함수입니다.
구문은 ROW_NUMBER와 동일합니다.
ROW_NUMBER는 각 행에 다른 번호를 부여하는 반면, RANK는 열의 값이 같을 경우 같은 번호가 부여됩니다.
실제로 쿼리를 작성해 봅시다.
SELECT name, age, gender
ROW_NUMBER() OVER (PARTITION BY gender ORDER BY age DESC) AS RowNo,
RANK() OVER (PARTITION BY gender ORDER BY age DESC) AS RankNo,
FROM my_table;
NAME AGE GENDER ROWNO RANKNO
---------- ---------- ---------- ---------- ----------
철수 45 M 1 1
영수 32 M 2 2
대희 32 M 3 2
대수 31 M 4 4
영희 29 F 1 1
미현 24 F 2 2
GENDER별로 구분하여 연령이 높은 순으로 순위를 매겼습니다.
또한 RANKNO는 같은 나이(32)인 경우 모두 같은 번호가 부여되며, 3은 건너뛰고 있습니다.
PARTITION BY와 GROUP BY의 차이점
SQL에서 '그룹화 기능'을 가지고 있는 구문이 'GROUP BY'와 'PARTITION BY' 입니다.
둘 다 테이블을 지정된 키로 분할하는 역할을 합니다.
이 두 가지가 다른 점은 GROUP BY의 경우 '분할 후 집계하여 한 행으로 묶는' 작업이 들어간다는 점입니다.
그 외의 점은 동일하므로 기억해둡시다.
요약
SQL의 PARTITION BY에 대해 알아보았습니다.
PARTITION BY는 지정한 열의 값별로 계산 처리를 할 수 있으며, 분석 함수와 함께 사용하면 효율적으로 데이터를 가져올 수 있습니다.
이 글을 참고하여 PARTITION BY의 사용법을 익혀보시기 바랍니다.