SQLを学び始めた時、一番分かりにくかったのがウィンドウ関数（分析関数）です。
データベースを、単にデータを出し入れする場所として利用する分には、ウィンドウ関数を使うことは無いのですが、データを分析しようとする場合、あるととても便利な機能です。
ウィンドウ関数を使わないでも同様のクエリを書くことは可能なのですが、ウィンドウ関数を使うとクエリが短く書けることが多いです。
私も完全にマスターしているわけでは無いのですが、ここでは、ざっくりしたウィンドウ関数についての考え方をまとめたいと思います。
通常のSQLでできないこと
通常のSQLでは、FROMでデータを取得する対象テーブルを指定し、そこからレコードを1行取り出し、SELECTでその1行のレコードのデータを使って演算して結果を出力します。
サンプルとして、都市別のデイリーのコロナ新規感染者数データを見てみます。
SELECT dt, city, cases FROM tbl_covid
| dt | city | cases |
|---|---|---|
| 2022-01-01 | Tokyo | 86 |
| 2022-01-01 | Osaka | 70 |
| ... | ... | ... |
各行で出力されるデータは、取り出した1行のレコードの中に入っているものだけで、他の行のデータを使うことはできません。
例えば、今日の人数と昨日の人数を取得して、その差分を出すといったようなことはできないのです。
他の行のデータを使う方法
しかし、他の行のデータを使いたいケースがあります。
例えば、全データ平均値よりどれだけ多い・少ないを出したい場合などです。
そういった場合は、クエリの中で更にクエリ（サブクエリ）を呼び出すことによってできます。
SELECT tbl_base.dt, tbl_base.city, tbl_base.cases - ( SELECT AVG(tbl_sub.cases) FROM tbl AS tbl_sub ) AS diff_avg_cases FROM tbl AS tbl_base
これをウィンドウ関数を使って書くと下記のようになります。
SELECT tbl_base.dt, tbl_base.city, tbl_base.cases - ( AVG(tbl_base.cases) OVER() ) AS diff_avg_cases FROM tbl AS tbl_base
「集計関数」+「OVER()」でウィンドウ関数となります。
ウィンドウ関数のイメージ
ウィンドウ関数が呼び出される様子を図にしてみました。
FROMより対象テーブルが指定されます- テーブルから1行のレコードが取り出されます
- レコードからカラムの値が取り出され、
SELECTで演算され出力されます SELECTの中のウィンドウ関数により、対象テーブルが再度呼び出されます- テーブルから対象範囲（ウィンドウ）のデータが取り出されます
- 対象範囲のデータを元に、集計関数で演算し、その結果値が
SELECTに渡ります
対象テーブルから対象範囲（ウィンドウ）を取り出して演算することから、ウィンドウ関数と呼ばれます。
また、ウィンドウ関数は行集合に対して演算を行う集計関数で、演算結果は必ず単一の値になります。
最初のSELECTの時は1行のレコードが取り出されるのに対し、ウィンドウ関数では複数のレコードがウィンドウとして取り出される点が大きく違いますね。
PARTITION BY
PARTITION BYでウィンドウ関数の対象範囲カラムを指定することができます。
例えば、前述の例ではテーブルの全データの平均値を求めたのですが、当日の全都市平均との差分を求めるには、PARTITION BYでdtを固定することにより集計できます。
SELECT tbl_base.dt, tbl_base.city, tbl_base.cases - AVG(tbl_base.cases) OVER( PARTITION BY tbl_base.dt ) AS diff_avg_cases FROM tbl AS tbl_base
また、他の都市の平均ではなく、その都市の他の日の平均との差分を求める場合は、PARTITION BYでcityを固定することにより集計できます。
SELECT tbl_base.dt, tbl_base.city, tbl_base.cases - AVG(tbl_base.cases) OVER( PARTITION BY tbl_base.city ) AS diff_avg_cases FROM tbl AS tbl_base
フレームとORDER BY
前述のとおり、PARTITION BYでウィンドウ関数のテーブル内の集計対象範囲が決まります。
そして、「フレーム」を使うと、その範囲内での開始と終了位置を指定することができ、更に対象範囲を絞り込むことができます。
例えば、ニュースなどでよく見る、各都市の7日移動平均は下記のクエリになります。
SELECT tbl_base.dt, tbl_base.city, AVG(tbl_base.cases) OVER( PARTITION BY tbl_base.city ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS mov_avg_7d FROM tbl AS tbl_base
ROWS BETWEENの後、「開始行」と「終了行」を指定します。
6 PRECEDINGで現在行の6行前、つまり6日前を示し、CURRENT ROWが現在行を示し、それの平均値を取るので7日平均が集計されます。
実は、このクエリは間違っています。6行前と現在行で7日移動平均を取るには、データの並びが日付で昇順になっている必要があります。
そして、そのデータの並び順を指定する方法としてORDER BYを使います。
以上を踏まえた、正しいクエリは下記になります。
SELECT tbl_base.dt, tbl_base.city, AVG(tbl_base.cases) OVER( PARTITION BY tbl_base.city ORDER BY tbl_base.dt ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS mov_avg_7d FROM tbl AS tbl_base
ウィンドウのイメージ
ウィンドウ関数が呼び出され、対象範囲が決まる様子を図にしてみました。
PARTITION BYでグループが固定されるORDER BYでデータの並びが決まるROWS BETWEENで範囲の開始行と終了行が決まる
「PARTITION BYはカラムを指定し、ROWS BETWEENで開始・終了を指定し、その開始・終了を指定するには、ORDER BYの並び指定が必要」という構造になります。
まとめ
繰り返しになりますが、ウィンドウ関数は下記のような流れになります。
SELECTの中から再度テーブルを呼び出す- テーブルの中から指定範囲のデータを取り出す
- 取り出されたデータを集計関数に渡して演算する
「テーブルが再び呼び出される」という所と「取り出されたレコードグループに対して集計関数が実行される」という所を抑えておけば、ウィンドウ関数のイメージは概ね理解できるかと思います。
応用
集計関数と行の開始・終了位置の指定方法は色々あるので、どういったものがあるかザッと目を通しておくとよいかと思います。
参考までに、ウィンドウ関数のよくある使い方例を記載しました。
各都市別、月初からの累計数
SELECT tbl_base.*, SUM(tbl_base.cases) OVER( PARTITION BY tbl_base.city, DATE_TRUNC(tbl_base.dt, MONTH) ORDER BY tbl_base.dt ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS val FROM tbl AS tbl_base
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWで、グループの最初から現在行までを指定しています
行番号
SELECT tbl_base.*, ROW_NUMBER() OVER( ORDER BY tbl_base.dt ASC, tbl_base.city ASC ) AS val FROM tbl AS tbl_base
一つ前の値
SELECT tbl_base.*, LAG(tbl_base.cases) OVER( PARTITION BY tbl_base.city ORDER BY tbl_base.dt ASC ) AS val FROM tbl AS tbl_base
月末の行
WITH tbl_num AS ( SELECT tbl_base.*, ROW_NUMBER() OVER( PARTITION BY tbl_base.city, DATE_TRUNC(tbl_base.dt, MONTH) ORDER BY tbl_base.dt DESC ) AS num FROM tbl AS tbl_base ) SELECT * EXCEPT(num) FROM tbl_num WHERE num=1
- 月でグループ化し、日付降順で並べてナンバリングし、
num=1で並びの最初の行を抽出しています
ROW_NUMBER()やLAG()関数など、いくつかの関数は、開始・終了位置を指定できないものがあるので、都度ドキュメントを読んで使い方を確認してください。
その他感想など
SELECTからSELECTを呼び出すサブクエリを使用していて、クエリが重くなったりエラーになるような場合でも、ウィンドウ関数を使ったクエリに書き換えると軽く動作するケースがあります。
SELECTの中で、他の行の値を使いたいケースがあれば、ウィンドウ関数のことを思い出してあげてください。
