LAG関数を使用して次の行をチェック

はじめに

終了時間が次の開始時間と1時間以内かチェックしてLAG関数を使用してチェックした結果をCASE文で番号を0 or 1を別名で付けて使用することがあったので記事にしたいと思います。
前提として使用しているDBはpostgreSQLになります。
SQLクライアントソフトウェアはDBeaverになります。

LAG関数とは

ウィンドウ関数の1つで定義されているものです。
構文は以下です。

LAG(項目) OVER(ORDER BY 項目)

指定した項目が一行下にずれて取得できるような関数になっています。

 

LAG関数の使用例

まずはテーブルの作成やデータ投入のサンプルです。

テーブルの作成
CREATE TABLE public.manage_date (
  start_date timestamp NULL,
  end_date timestamp NULL
);
データ投入
INSERT INTO public.manage_date (start_date,end_date) VALUES
('2024-06-05 21:03:00.000','2024-06-06 10:03:00.000'),
('2024-06-06 11:00:00.000','2024-06-06 14:00:00.000'),
('2024-06-07 01:06:23.000','2024-06-07 02:05:53.000'),
('2024-06-07 02:08:53.000','2024-06-07 02:13:53.000'),
('2024-06-07 02:16:23.000','2024-06-07 02:37:53.000'),
('2024-06-07 02:39:53.000','2024-06-07 04:16:23.000'),
('2024-06-07 04:28:53.000','2024-06-07 05:38:23.000'),
('2024-06-07 05:40:23.000','2024-06-07 05:44:53.000'),
('2024-06-07 05:46:23.000','2024-06-07 05:52:23.000'),
('2024-06-07 05:53:23.000','2024-06-07 05:55:23.000'),
('2024-06-07 05:55:53.000','2024-06-07 06:45:53.000'),
('2024-06-07 07:06:23.000','2024-06-07 07:09:23.000'),
('2024-06-07 08:09:24.000','2024-06-07 09:09:23.000');
実行例
SQL
select
  *,
  LAG(start_date) OVER(ORDER BY start_date) as lag_start_date
from manage_date
実行結果

 

本題

実行例
SQL
select
  *,
  LAG(end_date) OVER(ORDER BY start_date) as lag_end_date,
  case
    when LAG(end_date) OVER(ORDER BY start_date) + INTERVAL '1 hour' < start_date THEN 0 ELSE 1
  end as is_new_group
from manage_date md

実行結果

実行内容

LAG関数で1行ずれたend_dateの日付と次の行のstart_dateで比較をして
1時間以内の場合は1、違う場合には0として別名を付けてSELECTをしました。

まとめ

実際に使用したときにはもっといろいろな条件などがあるのですが、
SQLで全件取得してプログラム側でチェックしようとしていたところ、SQLの関数等を利用して絞ってからSELECTできるので色々なことができるのだなと思いました。
ウィンドウ関数もLAGだけではなく、他にもあるのでうまく活用できるようになっていきたいなと感じました。


--------------------------
システム開発のご要望・ご相談はこちらから

コメントを残す

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