こんにちは！kossyです！
今回は、特定のレコードの前後のレコードを取得する LAGとLEAD関数を試してみたので、
備忘録としてブログに残してみたいと思います。
環境
MySQL 8系
LAG・LEAD関数とは？
まずはドキュメントを見てみましょう。
LAG
パーティション内の現在行よりoffset行だけ前の行で評価されたvalueを返す。 該当する行がない場合、その代わりとしてdefault(valueと同じ型でなければならない)を返す。 offsetとdefaultは共に現在行について評価される。 省略された場合、offsetは1となり、defaultはNULLになる。
出典: 9.21. ウィンドウ関数
LEAD
パーティション内の現在行よりoffset行だけ後の行で評価されたvalueを返す。 該当する行がない場合、その代わりとしてdefault(valueと同じ型でなければならない)を返す。 offsetとdefaultは共に現在行について評価される。 省略された場合、offsetは1となり、defaultはNULLになる。
出典: 9.21. ウィンドウ関数
「現在行よりoffset行だけ前/後の行」が肝ですね。次の項で実際にSQLを実行してみます。
実際に試してみる
上記のサイトでLAGとLEAD関数を試すことができますので活用します。
Schema SQLの部分に以下を入力します。
CREATE TABLE users ( id INT ); CREATE TABLE scores ( id INT, math INT, exam_date DATE, user_id INT ); INSERT INTO users (id) VALUES (1); INSERT INTO scores (id, math, exam_date, user_id) VALUES (1, 100, '2021-07-21', 1); INSERT INTO scores (id, math, exam_date, user_id) VALUES (2, 80, '2021-06-21', 1); INSERT INTO scores (id, math, exam_date, user_id) VALUES (3, 90, '2021-05-21', 1); INSERT INTO scores (id, math, exam_date, user_id) VALUES (4, 75, '2021-04-21', 1); INSERT INTO scores (id, math, exam_date, user_id) VALUES (5, 85, '2021-03-21', 1);
生徒テーブルと試験結果テーブルを作成しています。科目は一旦数学だけです。
次に、作成したテーブルに対して実行するSQLを書いてみます。まずはLAGです。
select id, exam_date, math, lag(math) over(order by exam_date desc) as next_math_score from scores where scores.user_id = 1 order by exam_date desc;
実行結果は以下です。
lag(math) over(order by exam_date desc) as next_math_score と select文に記載することで、
次の行の値を列として出力することができます。
次に、LEAD関数も試してみましょう。
select id, exam_date, math, lead(math) over(order by exam_date desc) as previous_math_score from scores where scores.user_id = 1 order by exam_date desc;
実行結果は以下です。
LEAD関数の場合は、前の行の値を列として出力されています。
おわりに
LAGとLEAD関数は、前回の結果や今回の結果を一緒に出力したい場合に使えるウィンドウ関数かと思います。
他のウィンドウ関数(SUMやMIN、MAX)と比べると使う機会は少ないかもしれませんが、覚えておいて損はないでしょう。
