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], ...
)

让我们分析以上语法：

• 这个表达将根据当前行之前指定偏移量的行进行评估。它可以是列，表达式或子查询。
• 这个抵消是一个正整数，指定行数那 来在要从中访问数据的当前行之前。的抵消可以是表达式，子查询或列。默认为1。
• 这个LAG()函数将返回default_value 万一抵消超出了分区的范围。
• PARTITION BY子句将行划分为应用了LAG()函数的分区。默认情况下，如果用户忽略PARTITION BY子句，该函数会将整个结果集视为一个分区。
• 这个排序子句指定每个分区中行的顺序LAG()函数已应用。

范例1：

让我们为演示建立一个新表，名称为销售量：

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

向其中添加一些数据：

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);

在这里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;

输出：

范例2：

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

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

输出：