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を一から勉強するにはコチラがおすすめです

-PostgreSQL, SQL

執筆者:


comment

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

関連記事

横持ちデータを縦持ちへ変換するSQL

Contents1 目的2 環境3 使用データ4 縦持ちにするSQL 目的 BIなどでグラフを書く場合、データが横持になっているとうまくグラフ化できない、といったケースがあります。ということでSQLで …

PostgreSQL 同一日内の入出金データのグラフ化

最近は自分の口座の残高や入出金履歴などは銀行のオンランサイトのサービスにログインしてインターネット経由で確認することができるようになっています。今銀行サイトからダウンロードした口座の入出金データをグラ …

【MySQL】特定の文字で文字列を分割する

Contents1 概要2 環境3 準備4 SUBSTRINGとLOCATEの組み合わせで分割 概要 データベースにおいて、一つのカラム内に文字列が入っており、特定の文字列で分割したいケースがたまにあ …

【PostgreSQL】縦持ちデータを横持ち変換し前年同月との比較をする

Contents1 目的2 元データ3 横持にするSQL4 結果5 まとめ 目的 システムから取り出したデータは正規化されており縦持ちのデータ構造が多いですが、口座の残高やカードの支払い金額など、前年 …

A5M2のテーブルエディタでNULL値に戻す方法

Contents1 概要2 実証環境3 ケース4 NULLに戻したいとき5 データ型が文字列以外の場合 概要 データベースのSQL開発ツールとして無料で使用でき、高機能なA5M2というツールがあります …

言語切り替え

カテゴリー