SQL の分析関数である LAG と LEAD を使うと、現在の行の値と前後の行の値を比較できる。今回は LAG を使って、動きを確認していく。 LAG は前の行、LEAD は後ろの行という違いだけで、構文は同じになるので、LEAD を知りたい場合は適宜置換してもらえると！

実行環境

テストの場所としては以前も使った、SQL Fiddleで試した。ブラウザ上でサクサク試せるので便利！

sqlfiddle.com

yoshitaku-jp.hatenablog.com

エンジンのバージョンはPostgreSQL 9.6を使っている。

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

まずは小さなデータを投入してシンプルな動きを確認する。その後、少しずつパラメータを追加していき、さらに動きを確認する。

データ投入

id,money,date の列からなっていて、3 行分のデータが入っている。わかりやすくするため 1 ずつ数字を上がっていくものとした。

CREATE TABLE LAG_TEST
(id     CHAR(4) NOT NULL,
 money  INTEGER ,
 date      DATE ,
  PRIMARY KEY (id));

BEGIN TRANSACTION;
INSERT INTO LAG_TEST VALUES ('0001', 1000, '2021-01-01');
INSERT INTO LAG_TEST VALUES ('0002', 2000, '2021-02-01');
INSERT INTO LAG_TEST VALUES ('0003', 3000, '2021-03-01');
COMMIT;

シンプルな実行

SQL を組み立てて実行する。 LAG 関数は次の形で指定する。

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

実際の SQL にすると次になる。 money の列を対象に、1 行ずつ前のものを参照している。並び順は date に従っている。

SELECT
  id,
  money,
  date,
  LAG(money, 1) OVER (ORDER BY date)

FROM
  LAG_TEST

id: 0002はid: 0001の money を参照していて、id: 0003はid: 0002の money を参照している。無事に 1 つ前の行の値を見ていることがわかる。

id	money	date	lag
0001	1000	2021-01-01	(null)
0002	2000	2021-02-01	1000
0003	3000	2021-03-01	2000

offset を変更してみる

先程は offset を 1 にして、1 つ前の行を参照することとした。 offset の値を 2 に変えてみる。

SELECT
  id,
  money,
  date,
  LAG(money, 2) OVER (ORDER BY date)

FROM
  LAG_TEST

idが0003の行が参照している値が変化した。

先程は、id: 0002はid: 0001の money を参照していて、id: 0003はid: 0002の money を参照していた。今は、id: 0002はnullとなりid: 0003はid: 0001の money を参照している。これは参照する行が 2 つ前の行の値を見ることとなったからである。

id	money	date	lag
0001	1000	2021-01-01	(null)
0002	2000	2021-02-01	(null)
0003	3000	2021-03-01	1000

デフォルトを設定してみる

次にLag関数の引数を増やしてみる。この部分はデフォルトを指定する部分になる。 offset は 2 のままにして、後ろに 0 を追加する。

LAG(money, 2, 0) OVER (ORDER BY date)

SELECT
  id,
  money,
  date,
  LAG(money, 2, 0) OVER (ORDER BY date)

FROM
  LAG_TEST

「offset を変更してみる」では、参照していない行はnullとなっていたが、今回は 0 が入っている。 defaultを設定すればその値が、設定しなければnullを設定することがわかった。

id	money	date	lag
0001	1000	2021-01-01	0
0002	2000	2021-02-01	0
0003	3000	2021-03-01	1000

OVER 句

次に OVER 句を見ていく。 OVER 句の中ではORDER BYとPARTITON BYを指定できる。

ORDER BY

ORDER BYは通常の SQL と同様、並び替えができる。ここではdescを末尾に設定し、並び順を逆転させている。

SELECT
  id,
  money,
  date,
  LAG(money, 2, 0) OVER (ORDER BY date desc)

FROM
  LAG_TEST

ここで注意するべきことは、LAG 関数で参照した後に並び替えるのではなく、並び替えてから LAG 関数が適用されていることである。

id	money	date	lag
0003	3000	2021-03-01	0
0002	2000	2021-02-01	0
0001	1000	2021-01-01	3000

LAG関数を適用してから...

| id   | money | date       | lag  |
| ---- | ----- | ---------- | ---- |
| 0001 | 1000  | 2021-01-01 | 0    |
| 0002 | 2000  | 2021-02-01 | 0    |
| 0003 | 3000  | 2021-03-01 | 1000 |

dateで並び替えているわけではない。

| id   | money | date       | lag  |
| ---- | ----- | ---------- | ---- |
| 0003 | 3000  | 2021-03-01 | 1000 |
| 0002 | 2000  | 2021-02-01 | 0    |
| 0001 | 1000  | 2021-01-01 | 0    |

PARTITON BY

PARTITON BYを使うとGROUP BYと似たようなことができる。 GROUP BYはグループでまとめた行を表示するが、PARTITON BYはグループ単位で仕切って行を表示するイメージになる。

PARTITION BY	GROUP BY
グループ単位で仕切る	グループ単位でまとめる

ここで少しデータを増やす。 4 月分のデータと、比較用の昨年度データを投入した。

CREATE TABLE LAG_TEST
(id     CHAR(4) NOT NULL,
 year  INTEGER ,
 month  INTEGER ,
 day  INTEGER ,
 money  INTEGER ,
 date      DATE ,
 PRIMARY KEY (id));

BEGIN TRANSACTION;
INSERT INTO LAG_TEST VALUES ('0001','2020','01','01', 100, '2020-01-01');
INSERT INTO LAG_TEST VALUES ('0002','2020','02','01', 200, '2020-02-01');
INSERT INTO LAG_TEST VALUES ('0003','2020','03','01', 300, '2020-03-01');
INSERT INTO LAG_TEST VALUES ('0004','2020','04','01', 400, '2020-04-01');
INSERT INTO LAG_TEST VALUES ('0005','2021','01','01', 500, '2021-01-01');
INSERT INTO LAG_TEST VALUES ('0006','2021','02','01', 600, '2021-02-01');
INSERT INTO LAG_TEST VALUES ('0007','2021','03','01', 700, '2021-03-01');
INSERT INTO LAG_TEST VALUES ('0008','2021','04','01', 800, '2021-04-01');
COMMIT;

PARTITION BYにmonthを指定して、昨年との値を月ごとに比較する。

SELECT
  year,
  month,
  money,
  LAG(money, 1, 0) OVER (PARTITION BY month ORDER BY date) AS 昨年の値,
FROM
  LAG_TEST

PARTITON BYはグループ単位で仕切って行を表示するイメージ

と書いたが、1 月、2 月と月ごとにまとめられ、その中で比較がおこなわれているのがわかる。 3 行の| 2020 | 2 | 200 | 0 |は前の行の500を参照していないことからも理解できると思う。

year	month	money	昨年の値
2020	1	100	0
2021	1	500	100
2020	2	200	0
2021	2	600	200
2020	3	300	0
2021	3	700	300
2020	4	400	0
2021	4	800	400

まとめ

LAG 関数を使いながら動きを確認できた。 LEAD 関数は LAG と置き換えるだけと書いたが実際そのとおりになる。試しに最後の SQL を LEAD 関数に置き換えてみる。列名も「翌年の値」と変更しておく。

SELECT
  year,
  month,
  money,
  LEAD(money, 1, 0) OVER (PARTITION BY month ORDER BY date) AS 翌年の値
FROM
  LAG_TEST

year	month	money	翌年の値
2020	1	100	500
2021	1	500	0
2020	2	200	600
2021	2	600	0
2020	3	300	700
2021	3	700	0
2020	4	400	800
2021	4	800	0

分析というと、現在から過去に向かっていくイメージを持つ人が多くいると思ったので、LEAD 関数ではなく LAG 関数で手を動かしてみた。そのまま手を動かせるものにしたので、実行して確認してもらえれば👌