SQL の分析関数である LAG と LEAD を使うと、現在の行の値と前後の行の値を比較できる。 今回は LAG を使って、動きを確認していく。 LAG は前の行、LEAD は後ろの行という違いだけで、構文は 同じになるので、LEAD を知りたい場合は適宜置換してもらえると！
実行環境
テストの場所としては以前も使った、SQL Fiddleで試した。 ブラウザ上でサクサク試せるので便利！
エンジンのバージョンは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 関数で手を動かしてみた。 そのまま手を動かせるものにしたので、実行して確認してもらえれば👌
