ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [PostgreSQL] CUBE문을 사용해 사용자 액션 집계하기
    DB/PostgreSQL 2021. 2. 28. 18:51

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

    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),
        ()
    )

    댓글