こんにちは、株式会社KIYONOのエンジニアです。

今回は、分析する際などに非常に便利なwindow関数について、window関数とは何か？構文、どのようなシーンで活用できるのかなどをご紹介させて頂きたいと思います。

window関数って何？って方、構文ぐらいならわかるけど、どんな場面で使えばいいのかわからないって方にご覧いただければと思います。

window関数とは？

——————————————————————————

MySQLリファレンス

ウィンドウ関数は、一連のクエリー行に対して集計のような操作を実行します。 ただし、集計操作ではクエリー が単一の結果行にグループ化されますが、ウィンドウ関数ではクエリー行ごとに結果が生成されます

 

BigQueryドキュメント

分析関数（ウィンドウ関数ともいいます）は、行のグループに対して値を計算し、各行に対して 1 つの結果を返します。これは、行のグループに対して 1 つの結果を返す集計関数とは異なります。
分析関数には、評価対象の行を囲む行のウィンドウを定義する OVER 句が含まれています。各行について、選択された行のウィンドウを入力として使用して分析関数の結果が計算され、場合によっては集計が行われます。
分析関数を使用すると、移動平均の計算、項目のランク付け、累積合計の計算、その他の分析を行うことができます。

完結に説明すると、ウィンドウ関数は、結果行の集約を行わずに集計・分析のための計算を行うことのできる関数。

———————————————————————————————————————————–

ウィンドウ関数と集計関数の違い

• ウィンドウ関数には、AVG・SUM・MAX・MINなどの集計関数と同じ関数がある。
• 集計関数との違いは、出力結果が異なる。
• 集計関数は、グループごとの、レコードの集約を行なった１行の結果を返す。
• ウィンドウ関数では、レコードの集約は行わず、入力と同じ数のレコードを返す。

集計関数の処理結果のイメージ

出力データ

・集約関数は、１レコードに集約される。

ウィンドウ関数の処理結果のイメージ

出力データ

•ウィンドウ関数は、対象のレコードはそのまま残る。

window関数構文

window関数　OVER句([PARTITION BY句][OREDER BY句][フレーム句])無名ウィンドウ構文

無名ウィンドウ構文

SELECT store_id, 
       item, 
       price, 
       AVG(price) OVER(ORDER BY store_id ROWS BETWEEN 2 
                              PRECEDING AND CURRENT ROW) AS price_avg 
  FROM Company;

SELECT
    store_id,
    item,
    price,
    AVG(price) OVER W AS price_avg
  FROM Company
WINDOW W AS(ORDER BY shohin_id ROWS BETWEEN 2 PRECEDING
                                        AND CURRENT ROW) ; 

OVRE句

ウィンドウ関数を使いますよという、宣言になります。OVER句の後ろに、ウィンドウ関数を作る形になります。

PARTITION BY句

PARTITION BY句で指定したカラムをもとに、レコードを分割する。

ORDER BY句

OREDER BY句で指定した絡むを基準に、レコードをソートする。

フレーム句

カレントレコードを基準とした、処理範囲の調節をする。
・ROWS: 移動単位をレコードで設定する。
・RANGE: 移動単位をカラムで設定する。
・n PRECEDING: nだけ前へ移動する。
・n FOLLOWING: nだけ後ろへ移動する。

window関数のユースケース

————————————————————————————-

SUM

月ごとの累計会員数を合計として出力。

SELECT created_at,　 
       register,
       SUM(register) OVER (ORDER BY created_at) as moving_sum 
  FROM bakery;

・移動合計を見る時などに活用できます、累計会員数や累計売上、累計来店数など。

AVG

累計平均会員数を、平均として出力。

SELECT
    created_at,　
    register,　　
    AVG(register) OVER (ORDER BY created_at) as moving_avg,
    SUM(register) OVER (ORDER BY created_at) as moving_sum
FROM bakery;

・株価や気温など移動平均を用いる際に活用できる。

COUNT

レコード数をカウントする。

SELECT implementation_date, 
       subject, 
       user_id, 
       score, 
       COUNT(user_id) OVER (PARTITION BY user_id) as total_tests 
  FROM scores 
ORDER BY implementation_date, subject, user_id;

LAG/LEAD

指定レコード分、前の値・後ろの値を付与する。
月ごとの売り上げデータの、前月、後月との差分を出力。

SELECT created_at, 
       sales, 
       sales - LAG(sales, 1, 0) OVER (ORDER BY created_at) AS diff
  FROM bakery;

・売上データの、前日・後日、前月・来月などを見比べる時。
・変更履歴の変更前、変更後を確認するとき。

RANK

順位付けを行う。
売り上げが高い順にランキングをつける。

SELECT store_id, 
       sales, 
       RANK() OVER (PARTITION BY store_id ORDER BY sales DESC) as ranking 
  FROM bakery;

・順位付け等行う際に活用できる。

NTILE

指定したグループ数で分類してランク付けを行う。分類は近い値でグルーピングされる。

SELECT store_id, 
       sales, 
       NTILE(3) OVER (ORDER BY sales desc) as g_rank 
  FROM bakery;

DENSE_RANK

グループのランクを振る。
同率順位があっても順位が繰り下がらないところが rank() との違い。

SELECT store_id, 
       sales, 
       DENSE_RANK() OVER (PARTITION BY store_id ORDER BY sales desc) AS d_rnk, 
       RANK() OVER (PARTITION BY store_id ORDER BY sales desc) as rnk 
  FROM bakery;

PERCENT_RANK

パーセントランクを算出する。

ランク最上位を 0 としてランクをパーセントで振る。
ランクの範囲は 0 〜 1
パーセントランクの計算式は（rank – 1）/（ウィンドウまたはパーティションの行数 – 1）

SELECT store_id, 
       sales, 
       RANK() OVER (PARTITION BY store_id ORDER BY sales desc) as rnk, 
       PERCENT_RANK() OVER (PARTITION BY store_id ORDER BY sales desc) as p_rnk 
  FROM bakery;

FIRST_VALUE / LAST_VALUE / NTH_VALUE

グループにおいてそれぞれ最初・最後・指定行の値を振る。

SELECT store_id, 
       SUM(sales), 
       FIRST_VALUE(sales) OVER (PARTITION BY store_id ORDER BY sales) as first, 
       LAST_VALUE(sales) OVER (PARTITION BY store_id ORDER BY sales ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last, 
       NTH_VALUE(sales ,3) OVER (PARTITION BY store_id ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as third_value 
  FROM bakery;

・ユーザーの初回購入と最終購入などで活用できる。

まとめ

いかがだったでしょうか？このようにウィンドウ関数を使うことで分析する際に便利な関数が揃っています。

今回紹介した関数以外にも、様々な便利で活用的な関数が揃っているので是非興味がある方は調べて実際に活用してみてはいかがでしょうか！！