SQL LAG()関数

概要: このチュートリアルでは、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)

以下は部分的な出力です。

SQL LAG Function Over Partition Example

この例では

  • まず、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 Function YoY example

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

このチュートリアルは役に立ちましたか?