[Oracle] Partition by로 그룹별 순서기반으로 집계하기

아래와 같이 sales 로그 테이블이 있다. 

PROD_ID	CUST_ID	TIME_ID	CHANNEL_ID	PROMO_ID	QUANTITY_SOLD	AMOUNT_SOLD
13	987	1-10-1998	3	999	1	1232.16
13	1660	1-10-1998	3	999	1	1232.16
13	1762	1-10-1998	3	999	1	1232.16
13	1843	1-10-1998	3	999	1	1232.16
13	1948	1-10-1998	3	999	1	1232.16
13	2273	1-10-1998	3	999	1	1232.16
13	2380	1-10-1998	3	999	1	1232.16
13	2683	1-10-1998	3	999	1	1232.16
13	2865	1-10-1998	3	999	1	1232.16
13	4663	1-10-1998	3	999	1	1232.16
13	5203	1-10-1998	3	999	1	1232.16
13	5321	1-10-1998	3	999	1	1232.16
13	5590	1-10-1998	3	999	1	1232.16
13	6277	1-10-1998	3	999	1	1232.16
13	6859	1-10-1998	3	999	1	1232.16
13	8540	1-10-1998	3	999	1	1232.16
13	9076	1-10-1998	3	999	1	1232.16
13	12099	1-10-1998	3	999	1	1232.16

 

Cust_id, Promo_id 그룹 별 Amount_sold 금액이 높은 순으로 3위까지의 합을 구하는 쿼리를 구해보자

SELECT
    CUST_ID,
    PROMO_ID, 
    SUM(AMOUNT_SOLD) AS TOP3_SUM
FROM (
    SELECT 
        CUST_ID,
        PROMO_ID,
        AMOUNT_SOLD,
        DENSE_RANK() OVER(PARTITION BY CUST_ID, PROMO_ID ORDER BY AMOUNT_SOLD) AS RN
    FROM SALES
)
WHERE RN <= 3
GROUP BY CUST_ID, PROMO_ID
;

 

결과는 다음과 같다.

CUST_ID	PROMO_ID	TOP3_SUM
2	350	55.58
2	999	112.71
3	999	69.77
4	999	29.54
6	350	252.48
6	999	23.37
7	999	22.06
8	999	55.16
9	351	49.19
9	999	36.91
10	999	128.91
11	350	27.84
11	999	34.03
12	999	54.18
13	350	260.05

 

위에서 순위를 정할때 DENSE_RANK 함수를 사용했는데 순위 함수는 아래와 같이 여러개가 있다.

 

Rank() - 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환한다. (예) 1, 2, 3, 4, 5)

Dense_Rank() - 동일한 값이면 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 순차적으로 반환한다.

Row_number() - 중복 관계없이 순차적으로 순위를 반환한다

 

-- The End --