八月 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_value是offset超出分区范围时的返回值。如果省略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;
1) 在结果集上使用 LEAD() 函数的示例
以下查询返回按年份列出的总销售额：
SELECT
year,
SUM(amount)
FROM sales
GROUP BY year
ORDER BY year;
本例使用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;
这是输出：
在这个例子中：
- 首先，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;
2) 在分区上使用 LEAD() 函数的示例
以下语句使用LEAD()函数将每个产品组当前年份的销售额与下一年的销售额进行比较：
SELECT
year,
amount,
group_id,
LEAD(amount,1) OVER (
PARTITION BY group_id
ORDER BY year
) next_year_sales
FROM
sales;
下图显示了输出：
在这个例子中：
PARTITION BY子句将行分配到由组 ID 指定的产品组（或分区）中。ORDER BY子句对每个产品组中的行按年份进行升序排序。LEAD()函数返回每个产品组当前年份下一年的销售额。
在本教程中，您学习了如何使用 PostgreSQL 的LEAD()函数，来访问当前行之后特定物理偏移处的行。
了解更多
PostgreSQL 教程：窗口函数
PostgreSQL 文档：窗口函数