IT Learning

実践形式でITのお勉強

PostgreSQL SQL

【PostgreSQL】前後の行を取得できるLAG LEAD関数の使い方

投稿日:2021年7月17日 更新日:

概要

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

結果

numlaglead
12
213
324
435
546
657
768
879
9810
109

次にオプションを指定する場合です。

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

結果

numlaglead
103
204
315
426
537
648
759
8610
970
1080

赤字の部分がオプションを指定しなかった場合との違いの箇所です。

 

 

便利な例

時系列データの場合などに、時刻差分などが取れるため便利です

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

結果

datetimeleadtimespan
2021/7/17 11:002021/7/17 11:020:02:00
2021/7/17 11:022021/7/17 11:050:03:00
2021/7/17 11:052021/7/17 11:100:05:00
2021/7/17 11:102021/7/17 11:110:01:00
2021/7/17 11:112021/7/17 11:180:07:00
2021/7/17 11:182021/7/17 11:200:02:00
2021/7/17 11:202021/7/17 11:270:07:00
2021/7/17 11:272021/7/17 11:350:08:00
2021/7/17 11:352021/7/17 11:400:05:00
2021/7/17 11:40

PostgreSQLを一から勉強するにはコチラがおすすめです

Related

-PostgreSQL, SQL

執筆者:


comment

メールアドレスが公開されることはありません。 が付いている欄は必須項目です