![[SQL] 最強の分析ツールと言われるWindow関数について私が学んだこと](https://devio2023-media.developers.io/wp-content/uploads/2015/07/sql.png)
[SQL] 最強の分析ツールと言われるWindow関数について私が学んだこと
はじめに
Window関数とはなんなのか？
分析関数とも言われる比較的新しい機能で、分析SQLを書く上で最強の武器だそうです。
ただ理解をするのが難しく
Windowとは何か？
Windowは何を分けているのか？
自分が学んだ時に理解に苦労したPartitionBy句とOrderBy句、frame定義について僕が理解していることや影響範囲について書こうと思います。
今回もいつものサンプルデータを使います。
手を動かしながら動作を確認したい方はこちらをどうぞ！
Window関数は以下のような物があります。個々の関数についての説明は今回は割愛します。
集約関数をWindow関数として使うことができる
- sum() Window内の合計値を算出
- min() Window内の最小値を算出
- max() Window内の最大値を算出
分析関数
- row_number() Window内での現在行の数を返す
- rank() Window内での現在行の順位を返す
- ntile() Window内を引数で指定した値で分割して現在行のランクを算出 詳しくはこちら
今回のSQL
サンプルのpaymentテーブルを使って
営業日ごとの売り上げを算出し、売り上げがその月の中で何番目に良かったのかを算出します。
元データは以下のようになっています。
payment_id | customer_id | staff_id | rental_id | amount | payment_date
------------+-------------+----------+-----------+--------+----------------------------
17503 | 341 | 2 | 1520 | 7.99 | 2007-02-15 22:25:46.996577
17504 | 341 | 1 | 1778 | 1.99 | 2007-02-16 17:23:14.996577
17505 | 341 | 1 | 1849 | 7.99 | 2007-02-16 22:41:45.996577
17506 | 341 | 2 | 2829 | 2.99 | 2007-02-19 19:39:56.996577
17507 | 341 | 2 | 3130 | 7.99 | 2007-02-20 17:31:48.996577
17508 | 341 | 1 | 3382 | 5.99 | 2007-02-21 12:33:49.996577
17509 | 342 | 2 | 2190 | 5.99 | 2007-02-17 23:58:17.996577
17510 | 342 | 1 | 2914 | 5.99 | 2007-02-20 02:11:44.996577
17511 | 342 | 1 | 3081 | 2.99 | 2007-02-20 13:57:39.996577
17512 | 343 | 2 | 1547 | 4.99 | 2007-02-16 00:10:50.996577
17513 | 343 | 1 | 1564 | 6.99 | 2007-02-16 01:15:33.996577
17514 | 343 | 2 | 1879 | 0.99 | 2007-02-17 01:26:00.996577
17515 | 343 | 2 | 1922 | 0.99 | 2007-02-17 04:32:51.996577
17516 | 343 | 2 | 2461 | 6.99 | 2007-02-18 18:26:38.996577
17517 | 343 | 1 | 2980 | 8.99 | 2007-02-20 07:03:29.996577
17518 | 343 | 1 | 3407 | 0.99 | 2007-02-21 14:42:28.996577
17519 | 344 | 1 | 1341 | 3.99 | 2007-02-15 10:54:44.996577
17520 | 344 | 2 | 1475 | 4.99 | 2007-02-15 19:36:27.996577
17521 | 344 | 1 | 1731 | 0.99 | 2007-02-16 14:00:38.996577
17522 | 345 | 2 | 1210 | 0.99 | 2007-02-15 01:26:17.996577
17523 | 345 | 1 | 1457 | 4.99 | 2007-02-15 18:34:15.996577
17524 | 345 | 2 | 1550 | 0.99 | 2007-02-16 00:27:01.996577
14596 rows 以下省略
このデータに以下のSQLを実行して
select
date_trunc('month',payment_date) as monthly
,date_trunc('day',payment_date) as daily
,sum(amount) as daily_amount
,rank() over(
partition by
date_trunc('month',payment_date)
order by
sum(amount) desc) as monthly_rank
from
payment
group by
date_trunc('month',payment_date)
,date_trunc('day',payment_date)
order by
monthly,daily;
13~14行目で営業日ごとにグルーピングして
4行目で各営業日の売り上げ合計を算出して表示
5~9行目でWindow関数を用いて、各営業日の売り上げがその月の営業日の中で何番目に高かったのかをランキングしています。
5~9行目が今回ご紹介するWindow関数になります。
実行すると以下のようになります。
month | daily | daily_amount | monthly_rank
---------------------+---------------------+--------------+--------------
2007-02-01 00:00:00 | 2007-02-14 00:00:00 | 116.73 | 8
2007-02-01 00:00:00 | 2007-02-15 00:00:00 | 1188.92 | 4
2007-02-01 00:00:00 | 2007-02-16 00:00:00 | 1154.18 | 6
2007-02-01 00:00:00 | 2007-02-17 00:00:00 | 1188.17 | 5
2007-02-01 00:00:00 | 2007-02-18 00:00:00 | 1275.98 | 2
2007-02-01 00:00:00 | 2007-02-19 00:00:00 | 1290.90 | 1
2007-02-01 00:00:00 | 2007-02-20 00:00:00 | 1219.09 | 3
2007-02-01 00:00:00 | 2007-02-21 00:00:00 | 917.87 | 7
2007-03-01 00:00:00 | 2007-03-01 00:00:00 | 2808.24 | 2
2007-03-01 00:00:00 | 2007-03-02 00:00:00 | 2550.05 | 7
2007-03-01 00:00:00 | 2007-03-16 00:00:00 | 299.28 | 10
2007-03-01 00:00:00 | 2007-03-17 00:00:00 | 2442.16 | 8
2007-03-01 00:00:00 | 2007-03-18 00:00:00 | 2701.76 | 3
2007-03-01 00:00:00 | 2007-03-19 00:00:00 | 2617.69 | 5
2007-03-01 00:00:00 | 2007-03-20 00:00:00 | 2669.89 | 4
2007-03-01 00:00:00 | 2007-03-21 00:00:00 | 2868.27 | 1
2007-03-01 00:00:00 | 2007-03-22 00:00:00 | 2586.79 | 6
2007-03-01 00:00:00 | 2007-03-23 00:00:00 | 2342.43 | 9
2007-04-01 00:00:00 | 2007-04-05 00:00:00 | 273.36 | 13
2007-04-01 00:00:00 | 2007-04-06 00:00:00 | 2077.14 | 6
2007-04-01 00:00:00 | 2007-04-07 00:00:00 | 1984.28 | 8
2007-04-01 00:00:00 | 2007-04-08 00:00:00 | 2227.84 | 5
2007-04-01 00:00:00 | 2007-04-09 00:00:00 | 2067.86 | 7
2007-04-01 00:00:00 | 2007-04-10 00:00:00 | 1973.18 | 9
2007-04-01 00:00:00 | 2007-04-11 00:00:00 | 1940.32 | 10
2007-04-01 00:00:00 | 2007-04-12 00:00:00 | 1930.48 | 11
2007-04-01 00:00:00 | 2007-04-26 00:00:00 | 347.21 | 12
2007-04-01 00:00:00 | 2007-04-27 00:00:00 | 2673.57 | 3
2007-04-01 00:00:00 | 2007-04-28 00:00:00 | 2622.73 | 4
2007-04-01 00:00:00 | 2007-04-29 00:00:00 | 2717.60 | 2
2007-04-01 00:00:00 | 2007-04-30 00:00:00 | 5723.89 | 1
2007-05-01 00:00:00 | 2007-05-14 00:00:00 | 514.18 | 1
Windowとは
集約関数はGroupBy句で区切られた集合に対しての処理
Window関数はWindowで区切られた集合に対しての処理
では今回のケースで言えばWindowはどこに該当するのか？
この赤い線で区切られた集合がWindowになります。
上でご紹介したSQLのrank()関数はこのように区切られたWindowの中でランキングを出す役割を果たしています。
以下Window関数の使い方のご紹介です。
over
,rank() over(
partition by
date_trunc('month',payment_date)
order by
sum(amount) desc) as monthly_rank
全てのウィンドウ関数にはOver句が必要です。
Window関数は、クエリによって選択された行の一部（あるいは全ての行）を対象とするのですが、Over句を使ってその対象範囲を指定します。
今回使用しているrank()関数はover以降の条件にしたがって順位付けをします。
PARTITION BY
partition by
date_trunc('month',payment_date)
PartitionBy句を使ってWindowをどのように分けるのかを指定します。
指定しなければWindowで区切られない状態(つまり全体)で関数が実行されます。
ORDER BY
order by
sum(amount) desc) as monthly_rank
Windowで区切られた集合に対してどのように並べ替えるのかを指定します。
rank()関数はOrderBy句で指定された値のランキングを表示します。
他にも順番が意味を持つ関数を使う場合はOrderBy句の指定は重要です。
今回のSQLの場合、各営業日の売り上げのランキングを出すためにWindow関数内のOrderBy句でsum(amount)を指定していますが、このタイミングでは並び順はWindow内ではsum(amount)の降順になっています。
order by monthly,daily;
その後、外側のOrderBy句で最終的に見やすい並び順にしています。
この辺りのSQLの実行順序を知りたい方はこちらをご参照ください。
frame定義
Window内での影響範囲を指定します。
frameの影響範囲を見るために上のSQLを少し変更しました。
select
date_trunc('month',payment_date) as monthly
,date_trunc('day',payment_date) as daily
,sum(amount) as daily_amount
,sum(sum(amount)) over(
partition by
date_trunc('month',payment_date)
order by
date_trunc('day',payment_date) asc
rows between 1 preceding and 1 following) as three_day_amount
from
payment
group by
date_trunc('month',payment_date)
,date_trunc('day',payment_date)
order by
monthly,daily;
5行目と10行目を変更して、現在行と直近前後１日の売り上げの合計値を算出して表示しています。(10行目のrows between〜がframe定義になります)
- preceding 前の何行目までをWindow関数の影響範囲にするかを指定
- following 後の何行目までをWindow関数の影響範囲にするかを指定
指定しない時のデフォルト値はWindow内の先頭から対象行までになります。
monthly | daily | daily_amount |three_day_amount
---------------------+---------------------+--------------+--------------
2007-02-01 00:00:00 | 2007-02-14 00:00:00 | 116.73 | 1305.65
2007-02-01 00:00:00 | 2007-02-15 00:00:00 | 1188.92 | 2459.83
2007-02-01 00:00:00 | 2007-02-16 00:00:00 | 1154.18 | 3531.27
2007-02-01 00:00:00 | 2007-02-17 00:00:00 | 1188.17 | 3618.33
2007-02-01 00:00:00 | 2007-02-18 00:00:00 | 1275.98 | 3755.05
2007-02-01 00:00:00 | 2007-02-19 00:00:00 | 1290.90 | 3785.97
2007-02-01 00:00:00 | 2007-02-20 00:00:00 | 1219.09 | 3427.86
2007-02-01 00:00:00 | 2007-02-21 00:00:00 | 917.87 | 2136.96
2007-03-01 00:00:00 | 2007-03-01 00:00:00 | 2808.24 | 5358.29
2007-03-01 00:00:00 | 2007-03-02 00:00:00 | 2550.05 | 5657.57
2007-03-01 00:00:00 | 2007-03-16 00:00:00 | 299.28 | 5291.49
2007-03-01 00:00:00 | 2007-03-17 00:00:00 | 2442.16 | 5443.20
2007-03-01 00:00:00 | 2007-03-18 00:00:00 | 2701.76 | 7761.61
2007-03-01 00:00:00 | 2007-03-19 00:00:00 | 2617.69 | 7989.34
2007-03-01 00:00:00 | 2007-03-20 00:00:00 | 2669.89 | 8155.85
2007-03-01 00:00:00 | 2007-03-21 00:00:00 | 2868.27 | 8124.95
2007-03-01 00:00:00 | 2007-03-22 00:00:00 | 2586.79 | 7797.49
2007-03-01 00:00:00 | 2007-03-23 00:00:00 | 2342.43 | 4929.22
2007-04-01 00:00:00 | 2007-04-05 00:00:00 | 273.36 | 2350.50
2007-04-01 00:00:00 | 2007-04-06 00:00:00 | 2077.14 | 4334.78
2007-04-01 00:00:00 | 2007-04-07 00:00:00 | 1984.28 | 6289.26
2007-04-01 00:00:00 | 2007-04-08 00:00:00 | 2227.84 | 6279.98
2007-04-01 00:00:00 | 2007-04-09 00:00:00 | 2067.86 | 6268.88
2007-04-01 00:00:00 | 2007-04-10 00:00:00 | 1973.18 | 5981.36
2007-04-01 00:00:00 | 2007-04-11 00:00:00 | 1940.32 | 5843.98
2007-04-01 00:00:00 | 2007-04-12 00:00:00 | 1930.48 | 4218.01
2007-04-01 00:00:00 | 2007-04-26 00:00:00 | 347.21 | 4951.26
2007-04-01 00:00:00 | 2007-04-27 00:00:00 | 2673.57 | 5643.51
2007-04-01 00:00:00 | 2007-04-28 00:00:00 | 2622.73 | 8013.90
2007-04-01 00:00:00 | 2007-04-29 00:00:00 | 2717.60 | 11064.22
2007-04-01 00:00:00 | 2007-04-30 00:00:00 | 5723.89 | 8441.49
2007-05-01 00:00:00 | 2007-05-14 00:00:00 | 514.18 | 514.18
結果を見てみるとWindow内で直近前後１日の売り上げを合計しているのかがわかります。あとWindowの境界を見るとWindowを超えて集計をしていないのも確認できますね。影響範囲はあくまでもWindowの中だけということがわかります。
最後に
Window関数便利ですね。 後日Window関数を使った便利なSQLをご紹介したいと思います。
参考
http://www.atmarkit.co.jp/ait/articles/0509/27/news125.html
http://chopl.in/post/2012/12/01/window-function-tutorial/
http://postd.cc/window_functions_postgresql/
