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 関数で手を動かしてみた。
そのまま手を動かせるものにしたので、実行して確認してもらえれば👌