■ はじめに

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

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

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

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

【１】Window関数
【２】構文
　１）OVER PARTITION BY (グループ分け)
　２）OVER ORDER BY (順序指定)
　３）OVER ROWS BETWEEN (レコード範囲)
　４）補足：組み合わせについて
【３】利用可能なWindow関数
【４】独自のWindow関数
【５】サンプル
　例１：最新5年間の売上増減を表示
　例２：マラソン大会の5kmペースの前後比較をする

【１】Window関数

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

使用例

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

* PostgreSQL / Redshift でも使える

【２】構文

１）OVER PARTITION BY (グループ分け)

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

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

構文

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

２）OVER ORDER BY (順序指定)

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

構文

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

３）OVER ROWS BETWEEN (レコード範囲)

構文

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

４）補足：組み合わせについて

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

サンプル

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

【３】利用可能なWindow関数

* 他にもいっぱいあるけど、、、

#	関数	説明
1	lag()	現在の行よりソート状態での前の行の値を返す
2	lead()	現在の行よりソート状態での後の行の値を返す
3	row_number()	Window内での現在行の数を返す
4	rank()	Window内での現在行の順位を返す

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

【４】独自のWindow関数

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

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

【５】サンプル

例１：最新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
;

例２：マラソン大会の5kmペースの前後比較をする

create table marathon(
  km numeric,
  pace time
);

insert into
 marathon
values
(5,'00:14:53'),
(10,'00:29:46'),
(15,'00:44:32'),
(20,'00:59:21'),
(25,'01:14:09'),
(30,'01:28:59'),
(35,'01:44:01'),
(40,'01:58:40'),
(42.195,'02:04:56');

select
 km,
 pace,
 lag(pace, 1) over (order by km) as lag_pace, --前の結果を取得
 lead(pace, 1) over (order by km) as lead_pace --後の結果を取得
from
  marathon;

km	pace	lag_pace	lead_pace
5	00:14:53		00:29:46
10	00:29:46	00:14:53	00:44:32
15	00:44:32	00:29:46	00:59:21
20	00:59:21	00:44:32	01:14:09
25	01:14:09	00:59:21	01:28:59
30	01:28:59	01:14:09	01:44:01
35	01:44:01	01:28:59	01:58:40
40	01:58:40	01:44:01	02:04:56
42.195	02:04:56	01:58:40

参考文献

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
前後のレコードの値を取得するSQL – LAGとLEAD | Box Code

プログラムの超個人的なメモ

Memo for Programming.

【SQL】Window関数～ LAG / LEAD ～

■ はじめに

目次

【１】Window関数

【２】構文

１）OVER PARTITION BY (グループ分け)

２）OVER ORDER BY (順序指定)

３）OVER ROWS BETWEEN (レコード範囲)

４）補足：組み合わせについて

【３】利用可能なWindow関数

【４】独自のWindow関数

【５】サンプル

例１：最新5年間の売上増減を表示

例２：マラソン大会の5kmペースの前後比較をする

参考文献

関連記事