PostgreSQL 教程: lag 函数

摘要：在本教程中，您将学习如何使用 PostgreSQL 的LAG()函数，访问当前行之前特定物理偏移量的行。

目录

PostgreSQL LAG() 函数介绍

PostgreSQL 的LAG()函数提供对当前行之前指定物理偏移量的行的访问。换句话说，LAG()函数可以从当前行访问前一行的数据，或者前一行之前的行，依此类推。

LAG()函数对于比较当前行和前一行的值非常有用。

LAG()函数的语法如下所示：

LAG(expression [,offset [,default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

在这个语法中：

expression

expression是根据当前行之前指定偏移量的行进行计算的。它可以是列、表达式或 子查询。expression必须返回单个值，并且不能是 窗口函数。

offset

offset是一个正整数，指定要从中访问数据的当前行之前的行数。offset可以是表达式、子查询或列。如果不指定则默认为 1。

default_value

如果offset超出分区范围，LAG()函数将返回default_value。如果省略default_value，则该函数将返回 NULL。

PARTITION BY 子句

PARTITION BY子句将行划分为应用LAG()函数的分区。

默认情况下，如果省略PARTITION BY子句，该函数会将整个结果集视为单个分区。

ORDER BY 子句

ORDER BY子句指定应用LAG()函数的每个分区中的行的顺序。

PostgreSQL LAG() 函数示例

我们将使用LEAD()函数教程中的sales表进行演示。

这是sales表的数据：

1) 在结果集上使用 LAG() 函数的示例

本例使用LAG()函数返回本年和上一年的销售额：

WITH cte AS (
	SELECT 
		year, 
		SUM(amount) amount
	FROM sales
	GROUP BY year
	ORDER BY year
) 
SELECT
	year, 
	amount,
	LAG(amount,1) OVER (
		ORDER BY year
	) previous_year_sales
FROM
	cte;

这是输出：

在这个例子中：

• 首先，CTE 返回按年份汇总的净销售额。
• 然后，外部查询使用LAG()函数返回每行上一年的销售额。第一行的previous_year_sales列中有 NULL，因为第一行没有上一年。

下面示例使用两个公共表表达式来返回当前年份和往年之间的销售额差异：

WITH cte AS (
	SELECT 
		year, 
		SUM(amount) amount
	FROM sales
	GROUP BY year
	ORDER BY year
), cte2 AS (
	SELECT
		year, 
		amount,
		LAG(amount,1) OVER (
			ORDER BY year
		) previous_year_sales
	FROM
		cte
)	
SELECT 
	year, 
	amount, 
	previous_year_sales,  
	(previous_year_sales - amount) variance
FROM 
	cte2;

2) 在分区上使用 LAG() 函数的示例

本示例使用LAG()函数来比较每个产品组当前年份的销售额与上一年的销售额：

SELECT
	year, 
	amount,
	group_id,
	LAG(amount,1) OVER (
		PARTITION BY group_id
		ORDER BY year
	) previous_year_sales
FROM
	sales;

该图显示了输出：

在这个例子中：

• PARTITION BY子句将行分配到由组 ID 指定的产品组（或分区）中。
• ORDER BY子句按年份升序对每个产品组中的行进行排序。
• LAG()函数应用于每个分区以返回上一年的销售额。

在本教程中，您学习了如何使用 PostgreSQL 的LAG()函数，访问当前行之前特定物理偏移量的行。

了解更多

PostgreSQL 教程： 窗口函数

PostgreSQL 文档： 窗口函数