【SQL】Window関数 / 分析関数 ~ LAG / LEAD ~

■ はじめに

SQL の Window関数 (分析関数) について扱う。
PostgeSQLだけでなく、Redshiftでも使える。

今回は、その中で、 LAG / LEAD に絞る。

なお、ROW_NUMBER / RANK については、
以前やった以下の関連記事を参照のこと。

ROW_NUMBER / RANK + PARTITION BY
https://dk521123.hatenablog.com/entry/2021/03/11/210937

目次

【1】Window関数
【2】構文
 1)OVER PARTITION BY (グループ分け)
 2)OVER ORDER BY (順序指定)
 3)OVER ROWS BETWEEN (レコード範囲)
 4)補足:組み合わせについて
【3】利用可能なWindow関数
【4】独自のWindow関数
【5】サンプル
 例1:最新5年間の売上増減を表示

【1】Window関数

* テーブルから部分集合を切り出して処理をかける機能
* 集約関数は、GroupBy句で区切られた集合に対しての処理するのに対し、
 Windowで区切られた集合に対しての処理

使用例

* サブクエリー等を使わずに1つ前・後の行の値を取得可能

DB

* PostgreSQL / Redshift でも使える

【2】構文

1)OVER PARTITION BY (グループ分け)

* OVER 句にて、 PARTITION BY 句で指定した
 項目の値ごとに部分集合を作る
* 以下の関連記事でも扱っている。

https://dk521123.hatenablog.com/entry/2021/03/11/210937

構文

-- パーティション分割
関数(...) OVER (PARTITION BY カラム名, ...)

2)OVER ORDER BY (順序指定)

* OVER 句にて、部分集合内を
 ORDER BY 句で指定した条件でソート

構文

-- 区間ごとに並び替え
関数(...) OVER (ORDER BY カラム名, ...)

3)OVER ROWS BETWEEN (レコード範囲)

構文

-- 区間ごとに並び替え
関数(...) OVER (ROWS BETWEEN 開始位置 AND 終了位置)

4)補足:組み合わせについて

* PARTITION BY と ORDER BY の組み合わせ も使用可能

サンプル

-- 項目「category」に対して、PARTITION BYして
-- 項目 date1, date2 と 複数指定した場合
RANK() OVER (PARTITION BY category date1 DESC, date2 DESC) AS rank_by_latest

【3】利用可能なWindow関数

* 他にもいっぱいあるけど、、、
# 関数 説明
1 lag() 現在の行よりソート状態での前の行の値を返す
2 lead() 現在の行よりソート状態での後の行の値を返す
3 row_number() Window内での現在行の数を返す
4 rank() Window内での現在行の順位を返す

PostgreSQL
https://www.postgresql.jp/document/8.4/html/functions-window.html

【4】独自のWindow関数

* 独自Window関数作成可能みたい

https://stackoverflow.com/questions/13790028/how-to-create-a-custom-windowing-function-for-postgresql-running-average-examp

【5】サンプル

例1:最新5年間の売上増減を表示

以下の関連記事で行った「最新5年間の売上増減を表示」を
LAG() と OVER(ORDER BY) を使って実装してみる。

考え方

[1] OVER(ORDER BY year ASC) で年を昇順に並べ替える
[2] 呼び出される LAG(sales) で前の売上 (sales) を取得する

https://dk521123.hatenablog.com/entry/2021/08/26/222043

SQL文 - LAG()のサンプル

SELECT
 main.year AS year,
 CASE
   WHEN main.sales = main.previous_sales THEN ''
   WHEN main.sales > main.previous_sales THEN ''
   WHEN main.sales < main.previous_sales THEN ''
   ELSE '-'
 END AS up_or_down,
 main.sales AS sales,
 main.previous_sales AS previous_sales,
 main.sales - main.previous_sales AS diff
FROM
(
SELECT
 year AS year,
 sales AS sales,
 -- ★ここに注目★
 LAG(sales) OVER(ORDER BY year ASC) AS previous_sales
FROM sales
ORDER BY
 year DESC
) AS main
;

参考文献

https://qiita.com/niwasawa/items/6b9a771ce94090059aab
https://dev.classmethod.jp/articles/sql-window-function/
https://excel-ubara.com/vba_sql/vba_SQL023.html
PostgreSQL
https://lets.postgresql.jp/documents/technical/window_functions/1
https://qiita.com/HiromuMasuda0228/items/0b20d461f1a80bd30cfc
http://everything-you-do-is-practice.blogspot.com/2017/09/postgresql-lag-lead.html

関連記事

ROW_NUMBER / RANK + PARTITION BY
https://dk521123.hatenablog.com/entry/2021/03/11/210937
SQL】ストアドプロシージャ / ファンクション
https://dk521123.hatenablog.com/entry/2010/08/06/112528
PostgreSQL】ストアドファンクション
https://dk521123.hatenablog.com/entry/2014/04/21/000100
WITH句 ~ 共通テーブル式
https://dk521123.hatenablog.com/entry/2012/07/26/013620
SQL View (ビュー)
https://dk521123.hatenablog.com/entry/2011/11/14/021253
SQLで変化(増加/変化なし/減少)について考える
https://dk521123.hatenablog.com/entry/2021/08/26/222043
グループ内のMAXデータを取得する
https://dk521123.hatenablog.com/entry/2018/10/06/230841