MySQL中LAG()函數和LEAD()函數如何使用

MySQL中LAG()函數和LEAD()函數如何使用

在MySQL中，LAG()和LEAD()是兩個非常有用的窗口函數，它們允許你在查詢結果中訪問當前行之前或之后的行的數據。這兩個函數在處理時間序列數據、計算差異、生成排名等場景中非常有用。本文將詳細介紹LAG()和LEAD()函數的使用方法，并通過示例幫助你更好地理解它們的應用。

1. 什么是窗口函數？

在深入討論LAG()和LEAD()之前，有必要先了解一下窗口函數的概念。窗口函數（Window Function）是一種在查詢結果集的子集（稱為“窗口”）上執行計算的函數。與聚合函數不同，窗口函數不會將多行合并為一行，而是為每一行返回一個值。

窗口函數通常與OVER()子句一起使用，OVER()子句定義了窗口的范圍和排序方式。常見的窗口函數包括ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD()等。

2. LAG()函數

LAG()函數用于訪問當前行之前的某一行的數據。它的基本語法如下：

LAG(expression, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

• expression: 要訪問的列或表達式。
• offset: 指定要訪問的行數，默認為1，表示前一行。
• default_value: 如果指定的行不存在（例如，當前行是第一行），則返回的默認值。如果未指定，默認值為NULL。
• PARTITION BY: 可選，用于將數據分區，函數將在每個分區內獨立計算。
• ORDER BY: 指定行的排序方式。

2.1 示例：計算銷售額的環比增長

假設我們有一個銷售數據表sales，包含以下數據：

CREATE TABLE sales (
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, amount) VALUES
('2023-01-01', 1000.00),
('2023-01-02', 1500.00),
('2023-01-03', 2000.00),
('2023-01-04', 2500.00),
('2023-01-05', 3000.00);

我們想要計算每天的銷售額與前一天的銷售額的差值（即環比增長）?？梢允褂?code>LAG()函數來實現：

SELECT
    sale_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS previous_amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS growth
FROM
    sales;

結果如下：

在這個例子中，LAG(amount, 1, 0)返回了前一天的銷售額，如果沒有前一天的數據（即第一行），則返回默認值0。

2.2 示例：分區計算

假設我們有一個包含多個產品銷售額的表product_sales，我們想要計算每個產品每天的銷售額與前一天的銷售額的差值?？梢允褂?code>PARTITION BY子句來實現：

CREATE TABLE product_sales (
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO product_sales (product_id, sale_date, amount) VALUES
(1, '2023-01-01', 1000.00),
(1, '2023-01-02', 1500.00),
(1, '2023-01-03', 2000.00),
(2, '2023-01-01', 500.00),
(2, '2023-01-02', 700.00),
(2, '2023-01-03', 900.00);

查詢語句如下：

SELECT
    product_id,
    sale_date,
    amount,
    LAG(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) AS previous_amount,
    amount - LAG(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) AS growth
FROM
    product_sales;

結果如下：

在這個例子中，PARTITION BY product_id將數據按產品ID分區，LAG()函數在每個分區內獨立計算。

3. LEAD()函數

LEAD()函數與LAG()函數類似，但它用于訪問當前行之后的某一行的數據。它的基本語法如下：

LEAD(expression, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

• expression: 要訪問的列或表達式。
• offset: 指定要訪問的行數，默認為1，表示后一行。
• default_value: 如果指定的行不存在（例如，當前行是最后一行），則返回的默認值。如果未指定，默認值為NULL。
• PARTITION BY: 可選，用于將數據分區，函數將在每個分區內獨立計算。
• ORDER BY: 指定行的排序方式。

3.1 示例：計算銷售額的同比增長

繼續使用sales表，我們想要計算每天的銷售額與后一天的銷售額的差值（即同比增長）?？梢允褂?code>LEAD()函數來實現：

SELECT
    sale_date,
    amount,
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_amount,
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) - amount AS growth
FROM
    sales;

結果如下：

在這個例子中，LEAD(amount, 1, 0)返回了后一天的銷售額，如果沒有后一天的數據（即最后一行），則返回默認值0。

3.2 示例：分區計算

同樣地，我們可以在product_sales表中使用LEAD()函數來計算每個產品每天的銷售額與后一天的銷售額的差值：

SELECT
    product_id,
    sale_date,
    amount,
    LEAD(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) AS next_amount,
    LEAD(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) - amount AS growth
FROM
    product_sales;

結果如下：

在這個例子中，PARTITION BY product_id將數據按產品ID分區，LEAD()函數在每個分區內獨立計算。

4. 總結

LAG()和LEAD()函數是MySQL中非常強大的工具，它們允許你在查詢結果中訪問當前行之前或之后的行的數據。通過使用這兩個函數，你可以輕松地計算時間序列數據中的差異、生成排名、分析趨勢等。

在實際應用中，LAG()和LEAD()函數通常與OVER()子句一起使用，OVER()子句定義了窗口的范圍和排序方式。你還可以使用PARTITION BY子句將數據分區，使函數在每個分區內獨立計算。

希望本文對你理解和使用LAG()和LEAD()函數有所幫助。通過掌握這些窗口函數，你將能夠更高效地處理和分析數據。