ウィンドウ関数でのデータのパーティション分割

Snowflake のウィンドウ関数

Jake Roach

Field Data Engineer

データのランク付け

ここでは、結果セット全体のレコードをランク付けしています。

      user_id  |    event_name   |  km_traveled  |  closest_attendees 
      -------  | --------------- | ------------- | ----------------- 
      user_81  |  Lunar Drift    |      0.5      |         1         
      user_02  |  Lunar Drift    |      1.1      |         2       
      user_33  |  Crimson Arc    |      8.6      |         3          
      user_33  |  Neon Prophet   |      8.6      |         3         
      user_15  |  VibeStorm      |      17       |         5          
      user_94  |  The Dusk Owls  |      41       |         6        
      user_47  |  Lunar Drift    |      61       |         7   
      user_56  |  Crimson Arc    |      116      |         8

パーティションを使ったデータのランク付け

次は、指定したウィンドウごとにデータをランク付けします。

各会場までの移動距離に基づきコンサート参加者をランク付けしたテーブル

PARTITION BY

SELECT
    user_id,
    event_name,
    distance_traveled,

    RANK() OVER(
        -- Create window by event_name
        PARTITION BY event_name
        ORDER BY km_traveled
    ) AS closest_concert_goer

FROM CONCERTS.attendance;

PARTITION BY は関数を適用するレコードのウィンドウを作成します

PARTITION BY は OVER(...) 内で ORDER BY の前に記述します
GROUP BY と似ていますが、レコードを「集約」しません

パーティションを使ったデータのランク付け

SELECT
    level,
    price,

    RANK() OVER(
        PARTITION BY level
        ORDER BY price DESC
    ) AS price_rank

FROM CONCERTS.attendance;

PARTITION BY はウィンドウを作成します

      level  |   price   | price_rank 
    -------- | --------- | -----------
       100   |    765    |      1
       100   |    617    |      2
       100   |    490    |      3
       100   |    490    |      3

                  ...

       200   |    212    |      1
       200   |    207    |      2

                  ...

FIRST_VALUE による集計メトリクスの生成

    FIRST_VALUE(<1>) OVER(
        PARTITION BY <2>
        ORDER BY <3>
    ) AS <alias>

FIRST_VALUE はウィンドウ内の最初の値を取得します

<1>: 返すカラム

<2>: パーティション分割するフィールド

<3>: 最初のレコードを決定するフィールド

AVG による集計メトリクスの生成

AVG(<1>) OVER(
    PARTITION BY <2>
    -- No need to ORDER BY!
) AS <alias>

AVG は各ウィンドウのフィールドの平均値を求めます

<1>: 平均を取るカラム

<2>: パーティション分割するフィールド

ORDER BY は不要です！

顧客満足度

SELECT
    user_id, event_name, satisfaction_score,


    FIRST_VALUE(satisfaction_score) OVER(
        PARTITION BY event_name   -- Satisfaction score for the closest concert-goer
        ORDER BY km_traveled
    ) AS first_score,


    -- Find the average satisfcation score for a "window" of records
    AVG(satisfaction_score) OVER(
        PARTITION BY event_name
    ) AS average_score


FROM CONCERTS.attendance;

顧客満足度


      user_id  |   event_name   |  satisfaction_score  |  first_score  |  average_score 
     --------- | -------------- | -------------------- | ------------- | ---------------

      user_26  |  Pulse Theory  |          71          |       98      |      84.5      
      user_92  |  Pulse Theory  |          98          |       98      |      84.5      

                                          ...

      user_57  |   Nova Sway    |           4          |       22      |      29.3      
      user_39  |   Nova Sway    |          22          |       22      |      29.3      
      user_44  |   Nova Sway    |          62          |       22      |      29.3      

                                          ...

演習に挑戦しましょう！

Snowflake のウィンドウ関数