概要: このチュートリアルでは、SQLのLAG()
関数を使用して、現在の行から前の行のデータにアクセスする方法を学習します。
SQL LAG()関数の概要
SQL LAG()
はウィンドウ関数であり、現在の行より前の特定の物理的なオフセットにある行にアクセスできます。
言い換えれば、LAG()
関数を使用することで、現在の行から前の行のデータ、またはその前の2行目、3行目といったデータにアクセスできます。
LAG()
関数は、現在の行と前の行の差を計算する場合などに非常に役立ちます。
以下に、LAG()
関数の構文を示します。
LAG(return_value [,offset[, default_value ]]) OVER (
PARTITION BY expr1, expr2,...
ORDER BY expr1 [ASC | DESC], expr2,...
)
Code language: SQL (Structured Query Language) (sql)
LAG()
関数の各要素を詳しく見ていきましょう。
return_value
指定されたオフセットに基づいた戻り値。現在の行から特定のオフセットにある行の列を指定できます。
offset
データにアクセスする現在の行から遡る行数。オフセットは非負の整数でなければなりません。省略した場合は1がデフォルトになります。
default_value
前の行が指定されていない場合、default_value
が返されます。たとえば、オフセットが2の場合、最初の行の戻り値はdefault_value
になります。default_value
が指定されておらず、前の行が見つからない場合は、デフォルトでNULLが返されます。
PARTITION BY句
PARTITION BY
句は、LAG()
関数が適用される1つ以上のパーティションに、行を整理します。PARTITION BY
句を省略した場合は、結果全体が単一のパーティションとして扱われます。
ORDER BY句
ORDER BY
句は、LAG()
関数が適用される各パーティション内の行の順序を指定します。
SQL LAG()関数の例
従業員の給与履歴を格納するbasic_pays
という名前の新しいテーブルを作成します。
CREATE TABLE basic_pays (
employee_id int,
fiscal_year INT,
salary DECIMAL(10 , 2 ),
PRIMARY KEY (employee_id, fiscal_year)
);
Code language: SQL (Structured Query Language) (sql)
次のスクリプトは、basic_pays
テーブルにデータを挿入します。
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2017,24000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2017,17000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2017,17000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2017,9000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(104,2017,6000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(105,2017,4800); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(106,2017,4800); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(107,2017,4200); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(108,2017,12000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(109,2017,9000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(110,2017,8200); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(111,2017,7700); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(112,2017,7800); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(113,2017,6900); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(114,2017,11000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(115,2017,3100); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(116,2017,2900); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(117,2017,2800); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(118,2017,2600); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(119,2017,2500); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(120,2017,8000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(121,2017,8200); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(122,2017,7900); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(123,2017,6500); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(126,2017,2700); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(145,2017,14000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(146,2017,13500); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(176,2017,8600); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(177,2017,8400); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(178,2017,7000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(179,2017,6200); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(192,2017,4000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(193,2017,3900); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(200,2017,4400); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(201,2017,13000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(202,2017,6000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(203,2017,6500); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(204,2017,10000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(205,2017,12000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(206,2017,8300); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2018,25920); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2018,18190); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2018,18360); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2018,9720); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(104,2018,6060); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(105,2018,4992); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(106,2018,5040); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(107,2018,4284); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(108,2018,12360); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(109,2018,9540); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(110,2018,8692); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(111,2018,7931); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(112,2018,8580); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(113,2018,7107); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(114,2018,11440); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(115,2018,3131); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(116,2018,3161); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(117,2018,2940); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(118,2018,2652); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(119,2018,2650); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(120,2018,8800); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(121,2018,8364); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(122,2018,8611); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(123,2018,6565); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(126,2018,2808); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(145,2018,14560); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(146,2018,14580); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(176,2018,9202); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(177,2018,8988); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(178,2018,7630); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(179,2018,6448); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(192,2018,4320); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(193,2018,4173); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(200,2018,4620); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(201,2018,13000); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(202,2018,6360); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(203,2018,7085); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(204,2018,10100); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(205,2018,12360); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(206,2018,8632); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2020,26179.2); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2020,19463.3); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2020,19278); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2020,10206); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(104,2020,6605.4); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(105,2020,5391.36); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(106,2020,5191.2); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(107,2020,4498.2); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(108,2020,13472.4); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(109,2020,9826.2); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(110,2020,9561.2); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(111,2020,8248.24); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(112,2020,9352.2); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(113,2020,7107); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(114,2020,12012); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(115,2020,3224.93); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(116,2020,3287.44); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(117,2020,3175.2); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(118,2020,2864.16); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(119,2020,2782.5); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(120,2020,9152); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(121,2020,8531.28); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(122,2020,8697.11); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(123,2020,6630.65); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(126,2020,2920.32); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(145,2020,16016); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(146,2020,14871.6); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(176,2020,9938.16); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(177,2020,9167.76); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(178,2020,7858.9); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(179,2020,6641.44); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(192,2020,4406.4); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(193,2020,4339.92); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(200,2020,4712.4); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(201,2020,14040); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(202,2020,6614.4); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(203,2020,7155.85); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(204,2020,10908); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(205,2020,12730.8); INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(206,2020,8890.96);
A) パーティションをまたがるSQL LAG()関数の使用例
次のステートメントは、すべての従業員の当年度と前年度の給与を返します。
SELECT
employee_id,
fiscal_year,
salary,
LAG(salary) OVER (
PARTITION BY employee_id
ORDER BY fiscal_year) previous_salary
FROM
basic_pays;
Code language: SQL (Structured Query Language) (sql)
以下は部分的な出力です。

この例では
- まず、
PARTITION BY
句によって、結果セットが従業員IDごとにグループに分割されました。 - 次に、各グループに対して、
ORDER BY
句によって行が会計年度の昇順にソートされました。 - 最後に、
LAG()
関数が各グループの行に個別に適用されました。各グループの最初の行は、前年度の給与がないためNULL
でした。2行目と3行目は、1行目と2行目の給与を取得してprevious_salary
列に入力しました。
前年比の給与増減は、次のクエリを使用して求めることができます。
SELECT
employee_id,
fiscal_year,
salary,
previous_salary,
CONCAT(ROUND(( salary - previous_salary ) * 100 /previous_salary,0),'%') YoY
FROM
( SELECT
employee_id,
fiscal_year,
salary,
LAG(salary,1,0) OVER (
PARTITION BY employee_id
ORDER BY fiscal_year) previous_salary
FROM
basic_pays
) t;
Code language: SQL (Structured Query Language) (sql)
次の図は部分的な出力です。

このチュートリアルでは、SQLのLAG()
関数を使用して、現在の行から前の行のデータにアクセスする方法を学習しました。