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;

輸出：