LAG、LEAD は MySQL8.0 で使える Window 関数の一種です。MySQL 以外でも使えます。Window 関数は複数行を同時に取り扱った結果を、行をまとめずに元々の行に付け足すことのできる関数です。Window 関数を用いることでサブクエリを JOIN したり、アプリケーション上で結果を結合する手間から解放されやすいです。
MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.21.1 Window 関数の説明#function_lag
MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.21.1 Window 関数の説明#function_LEAD
LAG、LEAD は端的に言うと前後の行を参照できる関数です。LAGが前、LEADが後です。LEADに関しては先読みで覚えるのもよさそうです。これを用いると次の様にして時系列データの行と行の間の時間を結果の表に追加できます。
create table data_logs
(
created_at datetime
);
insert into data_logs (created_at)
values ('2021-09-23 13:56:59'),
('2021-09-23 13:58:48'),
('2021-09-23 13:58:52'),
('2021-09-23 14:22:04'),
('2021-09-23 14:22:46'),
('2021-09-23 14:24:12'),
('2021-09-23 14:24:20'),
('2021-09-23 15:07:53'),
('2021-09-23 15:28:28'),
('2021-09-23 15:32:09'),
('2021-09-23 15:32:53');
select created_at as 現在のレコードの生成日時,
LEAD(created_at, 1) OVER (order by created_at) as 一つ先のレコードの生成日時,
UNIX_TIMESTAMP(LEAD(created_at, 1) OVER (order by created_at)) - UNIX_TIMESTAMP(created_at) as レコードとレコードの間の差分秒数
from data_logs
order by created_at
何をどうやっているかというと created_at 降順で並べた中の一行先を LEAD 関数で抜き出し、現在の行との差分を出しています。この一行先を抜き出す部分とその引数の意味が次です。
LEAD(created_at, 1) OVER (order by created_at) LEAD(抜き出す列名, n行先を参照するか) OVER (参照する表の定義)
LEAD の引数は比較的わかりやすいですが、OVERはあいまいです。というのもこの部分は Window 関数の肝の部分であり、表現できるものが多岐にわたるためです。例では”全体を created_at 昇順で並べた表”となっています。
この例では OVER の後に同じ文を繰り返していますが、これは次の様に任意の名前にまとめられます。
select
created_at as 現在のレコードの生成日時,
lead(created_at) over (created_at_asc) as 一つ先のレコードの生成日時,
UNIX_TIMESTAMP(lead(created_at) over (created_at_asc)) - UNIX_TIMESTAMP(created_at) as レコードとレコードの間の差分秒数
from data_logs
# WINDOW [名前] AS [Window関数で参照する表の定義]
WINDOW created_at_asc AS (order by created_at)
order by created_at;
WITH 句みたいな感じです。プログラム中でこういったクエリを構築する時は変数か何かに格納すれば読みやすさを保てますが、生SQLではそうはいきません。代わりに名前付けをして可読性を高めることになります。
OVER 以下で定義しているのは参照に関する定義です。このため次の様にして Window 関数で参照する範囲とは無関係にクエリ結果を並び替えることもできます。
select
created_at as 現在のレコードの生成日時,
lead(created_at) over (created_at_asc) as 一つ先のレコードの生成日時,
UNIX_TIMESTAMP(lead(created_at) over (created_at_asc)) - UNIX_TIMESTAMP(created_at) as レコードとレコードの間の差分秒数
from data_logs
# WINDOW [名前] AS [Window関数で参照する表の定義]
WINDOW created_at_asc AS (order by created_at)
order by created_at desc;
# 現在のレコードの生成日時 一つ先のレコードの生成日時 レコードとレコードの間の差分秒数
# 2021-09-23 15:32:53 null null
# 2021-09-23 15:32:09 2021-09-23 15:32:53 44
# 2021-09-23 15:28:28 2021-09-23 15:32:09 221
# 2021-09-23 15:07:53 2021-09-23 15:28:28 1235
# 2021-09-23 14:24:20 2021-09-23 15:07:53 2613
一方で WHERE は Window 関数の定義にも干渉します。Window 関数の範囲を絞り込みたいのであれば、クエリ本体に付け、そうでないならば Window 関数入りのクエリをサブクエリとして改めて WHERE することになります。
select
created_at as 現在のレコードの生成日時,
lead(created_at) over (created_at_asc) as 一つ先のレコードの生成日時,
UNIX_TIMESTAMP(lead(created_at) over (created_at_asc)) - UNIX_TIMESTAMP(created_at) as レコードとレコードの間の差分秒数
from data_logs
# 中で WHERE すると参照範囲も変わるので差分が null のレコードが現れます
WHERE '2021-09-23 14:00:00' <= created_at and created_at < '2021-09-23 15:00:00'
WINDOW created_at_asc AS (order by created_at)
order by created_at
# 現在のレコードの生成日時 一つ先のレコードの生成日時 レコードとレコードの間の差分秒数
# 2021-09-23 14:22:04 2021-09-23 14:22:46 42
# 2021-09-23 14:22:46 2021-09-23 14:24:12 86
# 2021-09-23 14:24:12 2021-09-23 14:24:20 8
# 2021-09-23 14:24:20
select * from
(
select
created_at as 現在のレコードの生成日時,
lead(created_at) over (created_at_asc) as 一つ先のレコードの生成日時,
UNIX_TIMESTAMP(lead(created_at) over (created_at_asc)) - UNIX_TIMESTAMP(created_at) as レコードとレコードの間の差分秒数
from data_logs
WINDOW created_at_asc AS (order by created_at)
order by created_at
) t
# 外で WHERE すると参照範囲は変わらないため差分が null のレコードは現れません
WHERE '2021-09-23 14:00:00' <= 現在のレコードの生成日時 and 現在のレコードの生成日時 < '2021-09-23 15:00:00'
# 現在のレコードの生成日時 一つ先のレコードの生成日時 レコードとレコードの間の差分秒数
# 2021-09-23 14:22:04 2021-09-23 14:22:46 42
# 2021-09-23 14:22:46 2021-09-23 14:24:12 86
# 2021-09-23 14:24:12 2021-09-23 14:24:20 8
# 2021-09-23 14:24:20 2021-09-23 15:07:53 2613
例では LEAD で時系列データを扱う使い方のみでしたが、Window 関数は多数ある他、これ以外にも色々な使い方ができます。Window 関数をうまいこと使うと GROUP BY したりサブクエリをこねくり回したりせずとも多様な表現が SQL で書けるようになります。
