[SQL] 最強の分析ツールと言われるWindow関数について私が学んだこと

宇都宮洋

2016.08.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

Window関数とはなんなのか？
分析関数とも言われる比較的新しい機能で、分析SQLを書く上で最強の武器だそうです。

ただ理解をするのが難しく
Windowとは何か？
Windowは何を分けているのか？
自分が学んだ時に理解に苦労したPartitionBy句とOrderBy句、frame定義について僕が理解していることや影響範囲について書こうと思います。

今回もいつものサンプルデータを使います。
手を動かしながら動作を確認したい方はこちらをどうぞ！

Window関数は以下のような物があります。個々の関数についての説明は今回は割愛します。
集約関数をWindow関数として使うことができる
- sum() Window内の合計値を算出
- min() Window内の最小値を算出
- max() Window内の最大値を算出
分析関数
- row_number() Window内での現在行の数を返す
- rank() Window内での現在行の順位を返す
- ntile() Window内を引数で指定した値で分割して現在行のランクを算出詳しくはこちら

今回のSQL

サンプルのpaymentテーブルを使って
営業日ごとの売り上げを算出し、売り上げがその月の中で何番目に良かったのかを算出します。
元データは以下のようになっています。

select * from 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はどこに該当するのか？
スクリーンショット 2016-08-31 11.46.30 のコピーこの赤い線で区切られた集合が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/

[SQL] 最強の分析ツールと言われるWindow関数について私が学んだこと

はじめに

今回のSQL

Windowとは

over

PARTITION BY

ORDER BY

frame定義

最後に

参考

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

[SQL] 最強の分析ツールと言われるWindow関数について私が学んだこと

はじめに

今回のSQL

Windowとは

over

PARTITION BY

ORDER BY

frame定義

最後に

参考

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

ORDER BY　

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩