SQL 윈도우 함수 - PARTITION BY와 ORDER BY의 역할

PARTITION BY와 ORDER BY는 SQL의 윈도우 함수의 작동 방식을 정의하는 데 사용된다.

구체적으로 RANK 함수에서 이 두 절의 차이점과 역할을 살펴보자.

 

PARTITION BY

PARTITION BY 절은 데이터 세트를 하나 이상의 컬럼에 따라 분할하는 역할을 한다.

PARTITION BY를 적용하면, 각 파티션 내에서 독립적으로 윈도우 함수가 적용된다.

즉, 데이터 세트를 작은 그룹으로 나누고 각 그룹 내에서 RANK가 계산된다.

 

ORDER BY

ORDER BY 절은 각 파티션 내에서 행의 순서를 정의한다. 순서는 RANK를 계산할 때의 기준이 된다.

RANK는 데이터 세트의 특정 값이 파티션 내에서 차지하는 순위를 결정하기 때문에, 데이터가 어떤 순서로 정렬되어야 할지에 따라 ORDER BY 뒤에 기준이 되는 컬럼(쿼리)을 작성해야 한다.

 

 

예제 쿼리 분석

SELECT 
    column1,
    column2,
    RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank
FROM 
    your_table;

 

위 쿼리에서 PARTITION BY와 ORDER BY의 역할을 자세히 설명하면 다음과 같다:

1. PARTITION BY column1:
   • column1의 값에 따라 데이터 세트를 여러 파티션으로 나눈다. 예를 들어, column1이 'A'와 'B'라는 두 개의 서로 다른 값을 가질 경우, 데이터 세트는 'A' 그룹과 'B' 그룹으로 나뉜다.
   • 각 파티션 내에서 RANK가 독립적으로 계산된다.
2. ORDER BY column2:
   • 각 파티션 내에서 column2의 값을 기준으로 행을 정렬한다.
   • RANK는 이 정렬된 순서를 기반으로 계산된다. 순서가 중요하기 때문에, column2 값이 작은 것부터 큰 것 순으로 정렬된 후 백분위 순위가 계산된다.
   • 만약 column2의 값이 큰 순서대로 순위 부여가 필요하다면, RANK() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS rank로 쓸 수 있다
     

 

 

예제 데이터와 결과

아래와 같은 테이블이 있다고 가정하자:

	column1	column2
	A	10
	A	20
	A	20
	A	30
	B	15
	B	25
	B	25
	B	35

 

위 테이블에서 column1으로 파티션을 나누고, column2로 정렬하여 RANK()를 계산하면 다음과 같은 결과가 출력된다.

SELECT 
    column1,
    column2,
    RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank
FROM 
    your_table;

 

결과:

	column1	column2	rank
	A	10	1
	A	20	2
	A	20	2
	A	30	4
	B	15	1
	B	25	2
	B	25	2
	B	35	4

• PARTITION BY column1: column1의 값에 따라 데이터가 파티션으로 나뉜다. column1이 'A'인 행들과 'B'인 행들은 각각의 파티션을 형성한다.
• ORDER BY column2: 각 파티션 내에서 column2의 값에 따라 행이 정렬된다.
• RANK(): 정렬된 순서대로 순위가 부여된다.

PARTITION A:

• column2 값이 10인 행의 순위는 1
• column2 값이 20인 행들은 동일한 값이므로 동일한 순위 2가 부여됨
• 중복된 값을 건너뛰고, 다음 순위는 4가 됨

PARTITION B:

• column2 값이 15인 행의 순위는 1
• column2 값이 25인 행들은 동일한 값이므로 동일한 순위 2가 부여됨
• 중복된 값을 건너뛰고, 다음 순위는 4가 됨

이와 같이 PARTITION BY는 데이터 세트를 나누고, ORDER BY는 각 파티션 내에서 행을 정렬하여 RANK()가 각 행의 순위를 계산한다. 중복된 값이 있는 경우 동일한 순위가 부여되며, 그 다음 순위는 건너뛰게 된다.