主页 » SQL 窗口功能 » SQL LAG

SQL LAG

总结：在本教程中，您将学习如何使用 SQL LAG() 函数根据当前行访问前一行的 data。

SQL LAG() 函数概览

SQL LAG() 是一个窗口函数，它提供对当前行之前的指定物理偏移位置的行进行访问。

换句话说，通过使用 LAG() 函数，您可以从当前行访问前一行的 data，或从当前行之前的第二行访问，或从当前行之前的第三行访问，依此类推。

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

从用于访问 data 的当前行向后数的行数。如果跳过，偏移量必须是非负整数。默认值为一。

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 表中插入 data

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() 函数的示例

以下语句返回所有员工当年的 salary 和上年的 salary

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，因为没有上年的 salary。第二行和第三行从第一行和第二行获取 salary，并将它们填充到 previous_salary 列中。

您可以使用以下查询找到同比 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() 函数根据当前行访问前一行的 data。

本教程是否有用？
SQL FIRST_VALUE
以前
SQL FIRST_VALUE
下一步
SQL LAST_VALUE
SQL LAST_VALUE