PostgreSQL SQL

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

投稿日:

概要

PostgreSQLで使用できるウィンドウ関数の中にLAGとLEAD関数があります。これらは指定した項目を行方向にずらすことができます。今回はこのLAG,LEAD関数の具体的な使用方法についてメモします。

  

準備

まずは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, SQL

執筆者:


comment

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

関連記事

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

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

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

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

WindowsにPostgreSQLをインストールしてサーバー化

Contents1 目的2 環境3 PostgreSQLインストール4 外部接続許可設定5 Firewall開放6 接続テスト7 まとめ 目的 自分の身の回りのデータ(特に口座残高などのお金関連)を管 …

PostgreSQL 累積値を計算するSQL

Contents1 目的2 Step1 : テーブル用意3 Step2 : 累積値を計算するSQL 目的 ある値の累積値をSQLで計算する。 Step1 : テーブル用意 以下のようなテーブルを用意し …

PostgreSQL カンマ付き文字列金額データを数値変換

BIツールなどで金額データを扱いたい場合に、金額データがカンマ区切りの文字列データの場合があります。 例:306,327 このような文字列データを数値に変換します。 SQL 以下のようなデータを持つテ …

言語切り替え

カテゴリー