집계 창 함수

Snowflake의 Window 함수

Jake Roach

Field Data Engineer

집계 창 함수

      m_id  |       wd     |   cb  |  num_workouts  |  total_cb  |  cb_vs_average 
     ------ | ------------ | ----- | -------------- | ---------- | ---------------
     m_192  |  2024-01-01  |  105  |        4       |     432    |      -3
     m_192  |  2024-01-03  |  156  |        4       |     432    |      48
     m_192  |  2024-01-04  |   69  |        4       |     432    |     -39
     m_192  |  2024-01-10  |  102  |        4       |     432    |      -6

     m_74   |  2024-02-10  |  374  |        3       |    1274    |     -50.67   
     m_74   |  2024-02-13  |  396  |        3       |    1274    |     -28.67
     m_74   |  2024-02-14  |  504  |        3       |    1274    |     -79.33

     m_233  |  2024-03-05  |  51   |        2       |     132    |     -15
     m_233  |  2024-03-12  |  81   |        2       |     132    |      15

집계 창 함수, AVG

SELECT
    <fields>,


    -- ORDER BY 불필요
    AVG(<1>) OVER(
        PARTITION BY <2>
    ) AS <alias>


...

<1>: 평균을 낼 열

<2>: 데이터를 분할할 필드

요약 지표를 계산하고 창의 각 레코드에 표시

+, -, *, / 같은 연산자로 결과를 활용
AVG, COUNT, SUM
ORDER BY 불필요

AVG

SELECT
    member_id AS m_id, 
    calories_burned AS cb,


    -- 먼저 평균 소모 칼로리
    AVG(calories_burned) OVER(
        PARTITION BY member_id
    ) AS avg_cb,


    -- 다음으로 평균 대비 차이
    calories_burned - AVG(calories_burned) OVER(
        PARTITION BY member_id
    ) AS cb_vs_average


FROM fitness.workouts;


    m_id  |   cb  |   avg_cb  |  cb_vs_average
   ------ | ----- | --------- | ---------------
   m_192  |  105  |    108    |       -3
   m_192  |  156  |    108    |       48
   m_192  |   69  |    108    |      -39
   m_192  |  102  |    108    |       -6

   m_74   |  374  |   424.67  |     -50.67   
   m_74   |  396  |   424.67  |     -28.67
   m_74   |  504  |   424.67  |      79.33

   m_233  |   51  |     66    |      -15
   m_233  |   81  |     66    |       15

COUNT

SELECT
    member_id AS m_id, 
    workout_date AS wd,

    -- COUNT에는 '*' 사용 가능
    COUNT(*) OVER(
        PARTITION BY member_id
    ) AS num_workouts

FROM fitness.workouts
ORDER BY member_id, workout_date;

COUNT에는 * 사용 가능!

   m_id  |      wd      |  num_workouts
  ------ | ------------ | --------------
  m_192  |  2024-01-01  |        4     
  m_192  |  2024-01-03  |        4     
  m_192  |  2024-01-04  |        4     
  m_192  |  2024-01-10  |        4     

  m_74   |  2024-02-10  |        3     
  m_74   |  2024-02-13  |        3     
  m_74   |  2024-02-14  |        3     

  m_233  |  2024-03-05  |        2      
  m_233  |  2024-03-12  |        2

SUM

SELECT
    member_id AS m_id, 
    calories_burned AS cb,


    -- 회원별 총 소모 칼로리
    SUM(calories_burned) OVER(
        PARTITION BY member_id
    ) AS total_cb,


    -- 전체 대비 비율
    calories_burned / SUM(calories_burned) OVER(
        PARTITION BY member_id
    ) AS total_cb


FROM fitness.workouts;


       m_id  |   cb  |  total_cb  |  prop_cb 
      ------ | ----- | ---------- | ---------
      m_192  |  105  |     432    |   0.2431
      m_192  |  156  |     432    |   0.3611
      m_192  |   69  |     432    |   0.1597
      m_192  |  102  |     432    |   0.2361

      m_74   |  374  |    1274    |   0.2935
      m_74   |  396  |    1274    |   0.3108
      m_74   |  504  |    1274    |   0.3956

      m_233  |  51   |     132    |   0.3864
      m_233  |  81   |     132    |   0.6136

회원 운동 평가

SELECT
    member_id AS m_id, workout_date AS wd, calories_burned AS cb,


    COUNT(*) OVER(                                  -- 운동 횟수
        PARTITION BY member_id
    ) AS num_workouts,


    SUM(calories_burned) OVER(                      -- 회원별 총 소모 칼로리
        PARTITION BY member_id
    ) AS total_cb,


    calories_burned - AVG(calories_burned) OVER(    -- 평균 대비 차이
        PARTITION BY member_id
    ) AS cb_vs_average


FROM fitness.workouts;

회원 운동 평가

      m_id  |       wd     |   cb  |  num_workouts  |  total_cb  |  cb_vs_average 
     ------ | ------------ | ----- | -------------- | ---------- | ---------------
     m_192  |  2024-01-01  |  105  |        4       |     432    |      -3
     m_192  |  2024-01-03  |  156  |        4       |     432    |      48
     m_192  |  2024-01-04  |   69  |        4       |     432    |     -39
     m_192  |  2024-01-10  |  102  |        4       |     432    |      -6

     m_74   |  2024-02-10  |  374  |        3       |    1274    |     -50.67   
     m_74   |  2024-02-13  |  396  |        3       |    1274    |     -28.67
     m_74   |  2024-02-14  |  504  |        3       |    1274    |      79.33

     m_233  |  2024-03-05  |  51   |        2       |     132    |     -15
     m_233  |  2024-03-12  |  81   |        2       |     132    |      15

Ayo berlatih!

Snowflake의 Window 함수