八月 28, 2023
摘要:在本教程中,您将学习如何使用 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 文档:窗口函数