首頁 » SQL 視窗函數 » SQL LAG

SQL LAG

摘要：在本教學中，您將學習如何使用 SQL LAG() 函數存取目前列之前列的資料。

SQL LAG() 函數概述

SQL LAG() 是一個視窗函數，可讓您存取指定實體偏移量處的列，該列位於目前列之前。

換句話說，透過使用 LAG() 函數，您可以從目前列存取前一列的資料，或從目前列之前的第二列，或從目前列之前的第三列，依此類推。

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

要從目前列往回存取資料的列數。偏移量必須是非負整數。如果省略，則預設為一。

default_value

如果未指定前述列，則傳回 default_value。例如，當偏移量為 2 時，第一列的傳回值為 default_value。如果未給定 default_value 且未找到前述列，則預設傳回 NULL。

PARTITION BY 子句

PARTITION BY 子句將列組織成一個或多個分割區，LAG() 函數會應用於這些分割區。如果省略 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，因為沒有前一年的薪資。第二列和第三列會從第一列和第二列取得薪資，並將其填入 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() 函數存取目前列之前列的資料。

這個教學對您有幫助嗎？
SQL FIRST_VALUE
上一篇
SQL FIRST_VALUE
下一篇
SQL LAST_VALUE
SQL LAST_VALUE