【PostgreSQL】前後の行を取得できるLAG LEAD関数の使い方
概要
PostgreSQLで使用できるウィンドウ関数の中にLAGとLEAD関数があります。これらは指定した項目を行方向にずらすことができます。今回はこのLAG,LEAD関数の具体的な使用方法についてメモします。
環境
PostgreSQL 11.5
準備
まずはPostgreSQLに適当なテスト用のテーブルとデータを用意します。以下にSQLの例を載せます。
CREATE TABLE lag_lead_test(
num INTEGER
);
INSERT INTO lag_lead_test
VALUES
(1)
,(2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
,(9)
,(10)テーブルオブジェクトを作成したくない場合はWITH句を使ってクエリで生成しても大丈夫です。
WITH lag_lead_test AS (
SELECT
*
FROM
(
VALUES
(1)
,(2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
,(9)
,(10)
) col(num)
)
LAG(), LEAD()の使い方
まずは一番シンプルな引数のオプションをデフォルトで使う場合です。引数のオプションを指定しない場合、LAGでは指定した項目が一行下にずれ、LEADは一行上にずれます。また、データが存在しない箇所はNULLが入ります。
書き方としては、LAGとLEADの引数に項目を入れ、OVER句で並び替え順を入れます。
LAG(項目) OVER(ORDER BY 項目)
LEAD(項目) OVER(ORDER BY 項目)
SQL例
SELECT
*
,LAG(num) OVER(ORDER BY num) as lag
,LEAD(num) OVER(ORDER BY num) as lead
FROM lag_lead_test結果
| num | lag | lead |
| 1 | 2 | |
| 2 | 1 | 3 |
| 3 | 2 | 4 |
| 4 | 3 | 5 |
| 5 | 4 | 6 |
| 6 | 5 | 7 |
| 7 | 6 | 8 |
| 8 | 7 | 9 |
| 9 | 8 | 10 |
| 10 | 9 |
次にオプションを指定する場合です。
LAG, LEADにずらす行数とデータがない場合の補間値を指定できます。
LAG(項目,ずらす行数,補間値) OVER(ORDER BY 項目)
LEAD(項目,ずらす行数,補間値) OVER(ORDER BY 項目)
SQL例
SELECT
*
,lag(num,2,0) OVER(ORDER BY num) as lag
,lead(num,2,0) OVER(ORDER BY num) as lead
FROM lag_lead_test結果
| num | lag | lead |
| 1 | 0 | 3 |
| 2 | 0 | 4 |
| 3 | 1 | 5 |
| 4 | 2 | 6 |
| 5 | 3 | 7 |
| 6 | 4 | 8 |
| 7 | 5 | 9 |
| 8 | 6 | 10 |
| 9 | 7 | 0 |
| 10 | 8 | 0 |
赤字の部分がオプションを指定しなかった場合との違いの箇所です。
便利な例
時系列データの場合などに、時刻差分などが取れるため便利です
SQL例
WITH time_lead_test AS (
SELECT
*
FROM
(
VALUES
('2021/7/17 11:00:00')
,('2021/7/17 11:02:00')
,('2021/7/17 11:05:00')
,('2021/7/17 11:10:00')
,('2021/7/17 11:11:00')
,('2021/7/17 11:18:00')
,('2021/7/17 11:20:00')
,('2021/7/17 11:27:00')
,('2021/7/17 11:35:00')
,('2021/7/17 11:40:00')
) col(datetime)
)
SELECT
datetime
,lead(datetime) OVER(ORDER BY datetime) AS lead
,CAST(lead(datetime) OVER(ORDER BY datetime) AS TIMESTAMP)-CAST(datetime AS TIMESTAMP) AS timespan
FROM time_lead_test結果
| datetime | lead | timespan |
| 2021/7/17 11:00 | 2021/7/17 11:02 | 0:02:00 |
| 2021/7/17 11:02 | 2021/7/17 11:05 | 0:03:00 |
| 2021/7/17 11:05 | 2021/7/17 11:10 | 0:05:00 |
| 2021/7/17 11:10 | 2021/7/17 11:11 | 0:01:00 |
| 2021/7/17 11:11 | 2021/7/17 11:18 | 0:07:00 |
| 2021/7/17 11:18 | 2021/7/17 11:20 | 0:02:00 |
| 2021/7/17 11:20 | 2021/7/17 11:27 | 0:07:00 |
| 2021/7/17 11:27 | 2021/7/17 11:35 | 0:08:00 |
| 2021/7/17 11:35 | 2021/7/17 11:40 | 0:05:00 |
| 2021/7/17 11:40 |
PostgreSQLを一から勉強するにはコチラがおすすめです
