PostgreSQL 教程: lead 函数

八月 25, 2023

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

PostgreSQL LEAD() 函数介绍

PostgreSQL 的LEAD()函数提供对当前行后面指定物理偏移量的行的访问。

这意味着从当前行开始,LEAD()函数可以访问下一行、下一行之后的数据,依此类推。

LEAD()函数对于将当前行的值与当前行之后的行的值进行比较非常有用。

下面说明了LEAD()函数的语法:

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

在这个语法中:

expression

expression根据与当前行的指定偏移量针对下一行进行计算。expression可以是必须计算为单个值的列、表达式、子查询。而且它不能是一个窗口函数

offset

offset是一个正整数,指定从当前行向前访问数据的行数。offset可以是表达式、子查询或列。

如果不指定offset,则默认为 1。

default_value

default_valueoffset超出分区范围时的返回值。如果省略default_value,则默认为 NULL。

PARTITION BY 子句

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

默认情况下,如果省略PARTITION BY子句,则将整个结果集作为一个分区。

ORDER BY 子句

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

PostgreSQL LEAD() 函数示例

让我们设置一个新表来进行演示。

首先,创建一个新表,名为sales

CREATE TABLE sales(
	year SMALLINT CHECK(year > 0),
	group_id INT NOT NULL,
	amount DECIMAL(10,2) NOT NULL,
	PRIMARY KEY(year,group_id)
);

其次,插入一些行sales表中:

INSERT INTO 
	sales(year, group_id, amount) 
VALUES
	(2018,1,1474),
	(2018,2,1787),
	(2018,3,1760),
	(2019,1,1915),
	(2019,2,1911),
	(2019,3,1118),
	(2020,1,1646),
	(2020,2,1975),
	(2020,3,1516);

第三步,从sales表中查询数据:

SELECT * FROM sales;

Sales sample table

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

以下查询返回按年份列出的总销售额:

SELECT 
	year, 
	SUM(amount)
FROM sales
GROUP BY year
ORDER BY year;

PostgreSQL LEAD Function - Sales by years

本例使用LEAD()函数返回本年和下一年的销售额:

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

这是输出:

PostgreSQL LEAD Function over a result set example

在这个例子中:

  • 首先,CTE 返回按年份汇总的销售额。
  • 然后,外部查询使用LEAD()函数返回每行下一年的销售额。

以下示例使用两个公共表表达式来返回当前年份和下一年之间的销售差异:

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

PostgreSQL LEAD Function over result set example

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

以下语句使用LEAD()函数将每个产品组当前年份的销售额与下一年的销售额进行比较:

SELECT
	year, 
	amount,
	group_id,
	LEAD(amount,1) OVER (
		PARTITION BY group_id
		ORDER BY year
	) next_year_sales
FROM
	sales;

下图显示了输出:

PostgreSQL LEAD Function over a partition example

在这个例子中:

  • PARTITION BY子句将行分配到由组 ID 指定的产品组(或分区)中。
  • ORDER BY子句对每个产品组中的行按年份进行升序排序。
  • LEAD()函数返回每个产品组当前年份下一年的销售额。

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