[PostgreSQL] CUBE문을 사용해 사용자 액션 집계하기

먼저 사용자들의 액션 플래그를 집계하는 쿼리를 다음과 같다고 가정합니다.

WITH
action_flag AS (
    SELECT 
           user_id,
           SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase,
           SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END)) AS has_review,
           SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
    FROM action_log
    GROUP BY user_id
)

SELECT *
from action_flag

이러한 결과는 다음과 같습니다.

user_id	has_purchase	has_review	has_favorite
1	1	1	1
2	1	0	1
3	1	0	0
4	0	0	1

 

위와 같은 결과를 가지고 만들 수 있는 액션 플래그에 속한 사용자의 수를 구하려 합니다. 즉 구하고자 하는 결과값은 아래와 같습니다.

has_purchase	has_review	has_favorite	user_cnt
0	0	0	~~~
0	0	1	~~~
0	1	0	~~~
1	0	0	~~~
0	1	1	~~~
등등등

 

위와 같은 결과를 한 테이블로 추출하기 위해선 아래와 같은 SQL이 나옵니다.

WITH
action_flag AS (
    SELECT
           user_id,
           SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase,
           SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END)) AS has_review,
           SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
    FROM action_log
    GROUP BY user_id
)
, action_venn_diagram AS (
    -- 3개를 모두한 경우
    SELECT has_purchase, has_review, has_favorite, COUNT(1) AS user_cnt
    FROM action_flag
    GROUP BY has_purchase, has_review, has_favorite

    UNION ALL

    --3개 중 2개만 한 경우
    SELECT NULL AS has_purchase, has_review, has_favorite, COUNT(1) AS user_cnt
    FROM action_flag
    GROUP BY has_review, has_favorite
    
    UNION ALL
    
    SELECT has_purchase, NULL AS has_review, has_favorite, COUNT(1) AS user_cnt
    FROM action_flag
    GROUP BY has_purchase, has_favorite
    
    UNION ALL
    
    SELECT has_purchase,has_review, NULL AS has_favorite, COUNT(1) AS user_cnt
    FROM action_flag
    GROUP BY has_purchase, has_review
    
    UNION ALL 
    
    -- 3개 중 1개만 한 경우
    SELECT NULL AS has_purchase, NULL AS has_review, has_favorite, COUNT(1) AS user_cnt
    FROM action_flag
    GROUP BY has_favorite
    
    UNION ALL
    
    SELECT has_purchase, NULL AS has_review, NULL AS has_favorite, COUNT(1) AS user_cnt
    FROM action_flag
    GROUP BY has_purchase
    
    UNION ALL
    
    SELECT NULL AS has_purchase,has_review, NULL AS has_favorite, COUNT(1) AS user_cnt
    FROM action_flag
    GROUP BY has_review
    
    UNION ALL 
    -- 액션과 상관없이 모든 사용자 집계
    SELECT NULL AS has_purchase, NULL AS has_review, NULL AS has_favorite, COUNT(1) AS user_cnt
    FROM action_flag
)

SELECT *
from action_venn_diagram
;

각 지표에 맞게 직접 조회한 다음, UNION ALL로 데이터를 결합하는 형식이라 성능이 좋지 않습니다. 이러한 문제를 CUBE 구문을 사용하면 쉽게 해결할 수 있습니다.

WITH
action_flag AS (
    SELECT
           user_id,
           SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase,
           SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END)) AS has_review,
           SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite
    FROM action_log
    GROUP BY user_id
)
, action_venn_diagram AS (
    SELECT 
           has_purchase,
           has_review,
           has_favorite,
           COUNT(1) AS user_cnt
    FROM action_flag
    GROUP BY CUBE(has_purchase, has_review, has_favorite)

)

SELECT *
from action_venn_diagram;

CUBE

CUBE구문은 grouping 에서 컬럼의 다차원 소계를 생성하는데 사용합니다. 위 CUBE문을 사용한 결과는 다음과 같습니다.

has_purchase	has_review	has_favorite	user_cnt
0	0	0	12
0	0	1	2
0	0		5
0	1	0	412
0		0	1
1	0	0	24

중간에 값이 NULL인 레코드는 해당 액션의 동작유무를 모르는 경우입니다.

CUBE는 다음과 같이 사용할 수 있습니다.

SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY CUBE (C1, C2, C3);


-- 특정 컬럼 제외 가능


SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY C1 CUBE (C2, C3);


GROUP BY CUBE(C1,C2,C3) 는 다음과 같이8개가 발생함
GROUPING SETS (
	(C1,C2,C3),
    (C1,C2),
    (C1,C3),
    (C2,C3),
    (C1),
    (C2),
    (C3),
    ()
)