よしたく blog

ITエンジニアとして自分が知らなかったことをまとめています

【SQL】LAG関数・LEAD関数の動きを確認する

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: 0002id: 0001の money を参照していて、id: 0003id: 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 を変更してみる

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

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

FROM
  LAG_TEST

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

先程は、id: 0002id: 0001の money を参照していて、id: 0003id: 0002の money を参照していた。 今は、id: 0002nullとなりid: 0003id: 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関数の引数を増やしてみる。 この部分はデフォルトを指定する部分になる。 offset2 のままにして、後ろに 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 BYPARTITON 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 BYmonthを指定して、昨年との値を月ごとに比較する。

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