MySQLで列のLAG（前の行との比較）を計算する方法

MySQLで列のLAG（前行参照）を実現するには

MySQLで列の「LAG」、すなわち前の行の値を参照しながら現在の行と比較する処理を行いたい場合は、ユーザー定義変数とサブクエリのLEFT JOINを組み合わせることで実現できます。まずは準備として、サンプル用のテーブルを作成しましょう。テーブル作成のクエリは次のとおりです。

mysql> create table LagDemo
    -> (
    -> UserId int,
    -> UserValue int
    -> );
Query OK, 0 rows affected (1.74 sec)

サンプルデータの挿入

続いて、INSERTコマンドを使ってテーブルに複数のレコードを挿入します。クエリは次のとおりです。

mysql> insert into LagDemo values(12,158);
Query OK, 1 row affected (0.61 sec)
mysql> insert into LagDemo values(18,756);
Query OK, 1 row affected (0.21 sec)
mysql> insert into LagDemo values(15,346);
Query OK, 1 row affected (0.25 sec)
mysql> insert into LagDemo values(87,646);
Query OK, 1 row affected (0.14 sec)
mysql> insert into LagDemo values(27,334);
Query OK, 1 row affected (0.11 sec)
mysql> insert into LagDemo values(90,968);
Query OK, 1 row affected (0.08 sec)
mysql> insert into LagDemo values(84,378);
Query OK, 1 row affected (0.10 sec)
mysql> insert into LagDemo values(85,546);
Query OK, 1 row affected (0.56 sec)

登録データの確認

SELECT文ですべてのレコードを表示し、データが正しく登録されたことを確認しておきましょう。

mysql> select *from LagDemo;

出力結果

+--------+-----------+
| UserId | UserValue |
+--------+-----------+
|     12 |       158 |
|     18 |       756 |
|     15 |       346 |
|     87 |       646 |
|     27 |       334 |
|     90 |       968 |
|     84 |       378 |
|     85 |       546 |
+--------+-----------+
8 rows in set (0.00 sec)

列のLAGを求めるクエリ

ここからが本題です。次のクエリでは、同じテーブルに対して2つのサブクエリを用意し、ユーザー定義変数を使ってそれぞれに行番号を振っています。その上でLEFT JOINで結合し、各行のUserValueを2行前のUserValueで割った値を「LAG」列として取得します。

mysql> SET @f := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @s := 2;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT l1.UserId, l1.UserValue , l1.UserValue / l2.UserValue AS 'LAG'
-> FROM
-> (SELECT if(@f, @f:=@f+1, @f:=1) as RowNumber, UserId, UserValue FROM LagDemo) AS l1
-> LEFT JOIN
-> (SELECT if(@s, @s:=@s+1, @s:=1) as RowNumber, UserId, UserValue FROM LagDemo) AS l2
-> ON l1.RowNumber = l2.RowNumber;

出力結果

+--------+-----------+--------+
| UserId | UserValue |    LAG |
+--------+-----------+--------+
|     12 |       158 |   NULL |
|     18 |       756 |   NULL |
|     15 |       346 | 2.1899 |
|     87 |       646 | 0.8545 |
|     27 |       334 | 0.9653 |
|     90 |       968 | 1.4985 |
|     84 |       378 | 1.1317 |
|     85 |       546 | 0.5640 |
+--------+-----------+--------+
8 rows in set (0.00 sec)

結果の見方とポイント

• 先頭の2行は、比較対象となる「2行前」のデータが存在しないため、LAG列がNULLになっています。
• 3行目以降は、たとえばUserId=15の行であれば 346 ÷ 158 ≒ 2.19 のように、2行前のUserValueに対する現在行のUserValueの比率が計算されていることがわかります。
• 変数@sの初期値を変更すると、さかのぼる行数（オフセット）を調整できます。例えば「SET @s := 1」とすれば、直前の行との比較に変わります。

補足：MySQL 8.0以降ならLAG()関数が使える

MySQL 8.0以降の環境では、ウィンドウ関数のLAG()を利用することで、ユーザー定義変数や自己結合を使わずに、同様の処理をより簡潔に記述できます。

mysql> SELECT UserId,
    ->        UserValue / LAG(UserValue, 2) OVER (ORDER BY UserId) AS LAG
    -> FROM LagDemo;

LAG()の第2引数には、さかのぼる行数を指定できます（省略時は1行）。なお、SELECT文中で「@変数 := 値」のように代入を行う書き方はMySQL 8.0.13以降で非推奨となっているため、新しくシステムを構築する場合はLAG()などのウィンドウ関数を使うことをおすすめします。