はじめに
終了時間が次の開始時間と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だけではなく、他にもあるのでうまく活用できるようになっていきたいなと感じました。
