一.基本介紹

1.LAG 和 LEAD 概述

在數(shù)據(jù)庫查詢中，開窗函數(shù)（Window Functions）是一種強大的工具，用于在結(jié)果集的子集上執(zhí)行計算。LAG和LEAD是兩個常用的開窗函數(shù)，它們允許你在當(dāng)前行的前面或后面的行中訪問數(shù)據(jù)。
在數(shù)據(jù)庫中，LAG和LEAD函數(shù)的全稱是：

• LAG函數(shù)的全稱是"LAST VALUE"。
• LEAD函數(shù)的全稱是"LEAD VALUE"。

這兩個函數(shù)是 SQL 標(biāo)準(zhǔn)中的開窗函數(shù)，它們允許在查詢結(jié)果的子集上執(zhí)行類似于聚合函數(shù)的計算，但不會導(dǎo)致結(jié)果集的單一行。它們的目的是在當(dāng)前行的前面（LAG）或后面（LEAD）的行中訪問數(shù)據(jù)，使得在處理時間序列數(shù)據(jù)或需要比較相鄰行之間的值時非常有用。

2.作用特性

Lag 和 Lead 分析函數(shù)可以在同一次查詢中取出同一字段的前 N 行的數(shù)據(jù)(Lag)和后 N 行的數(shù)據(jù)(Lead)作為獨立的列。

在實際應(yīng)用當(dāng)中，若要用到取今天和昨天的某字段差值時，Lag 和 Lead 函數(shù)的應(yīng)用就顯得尤為重要。當(dāng)然，這種操作可以用表的自連接實現(xiàn)，但是 LAG 和 LEAD 與 left join、right join 等自連接相比，效率更高，SQL 更簡潔。

3.LAG 函數(shù)

LAG函數(shù)用于獲取結(jié)果集中當(dāng)前行之前的某一行的值。語法如下：

LAG (expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY sort_expression)

• expression: 要檢索的列或表達(dá)式。
• offset: 要返回的行數(shù)，如果不指定，默認(rèn)為 1，即上一行。
• default: 當(dāng)指定的行數(shù)超出結(jié)果集范圍時，返回的默認(rèn)值。

4.LEAD 函數(shù)

LEAD函數(shù)用于獲取結(jié)果集中當(dāng)前行之后的某一行的值。語法如下：

LEAD (expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY sort_expression)

• expression: 要檢索的列或表達(dá)式。
• offset: 要返回的行數(shù)，如果不指定，默認(rèn)為 1，即下一行。
• default: 當(dāng)指定的行數(shù)超出結(jié)果集范圍時，返回的默認(rèn)值。

二.使用案例

1.LAG 示例

考慮一個名為sales的表，包含銷售數(shù)據(jù)：

CREATE TABLE sales (
    sale_date DATE,
    revenue INT
);

INSERT INTO sales VALUES
('2023-01-01', 100),
('2023-01-02', 150),
('2023-01-03', 200),
('2023-01-04', 120);

使用LAG函數(shù)，你可以獲取前一天的銷售額：

SELECT sale_date                                             -- 日期
     , revenue                                               -- 當(dāng)前銷售額
     , LAG(revenue) OVER (ORDER BY sale_date) AS lag_revenue -- 前一行的銷售額
FROM sales;

結(jié)果：

| sale_date  | revenue | lag_revenue |
|------------|---------|-------------|
| 2023-01-01 | 100     | NULL        |
| 2023-01-02 | 150     | 100         |
| 2023-01-03 | 200     | 150         |
| 2023-01-04 | 120     | 200         |

2.LEAD 示例

使用LEAD函數(shù)，你可以獲取后一天的銷售額：

SELECT sale_date                                               -- 日期
     , revenue                                                 -- 日期
     , LEAD(revenue) OVER (ORDER BY sale_date) AS lead_revenue -- 后一行的銷售額
FROM sales;

結(jié)果：

| sale_date  | revenue | lead_revenue |
|------------|---------|--------------|
| 2023-01-01 | 100     | 150          |
| 2023-01-02 | 150     | 200          |
| 2023-01-03 | 200     | 120          |
| 2023-01-04 | 120     | NULL         |

在這兩個示例中，LAG和LEAD函數(shù)通過ORDER BY子句按銷售日期對結(jié)果集進(jìn)行排序。這允許你在時間序列數(shù)據(jù)中訪問前一行或后一行的值，以進(jìn)行比較或計算差異等操作。

3.總結(jié)說明

• 時間序列分析： 在時間序列數(shù)據(jù)中，你可能需要比較當(dāng)前時間點的值與前一時間點或后一時間點的值，以便分析趨勢、變化或周期性。
• 差異計算： 你可能需要計算當(dāng)前行與前一行或后一行的差異，例如，計算每日銷售額的增長或減少量。
• 移動平均計算： 通過結(jié)合LAG和LEAD函數(shù)，你可以計算移動平均值，以平滑數(shù)據(jù)并更好地理解趨勢。
• 排名和百分比計算： 通過比較當(dāng)前行與前一行或后一行的值，你可以進(jìn)行排名或計算百分比變化，從而了解相對于其他行的位置或變化。
• 查找極值點： 通過比較當(dāng)前值與相鄰值，你可以輕松地識別極值點，例如找到峰值或谷值。
• 數(shù)據(jù)填充： 你可以使用LAG和LEAD函數(shù)來填充缺失的數(shù)據(jù)。如果某些行的數(shù)據(jù)缺失，你可以使用相鄰行的值進(jìn)行填充。
• 數(shù)據(jù)平滑計算： 你可以使用LAG和LEAD函數(shù)來進(jìn)行數(shù)據(jù)平滑計劃,讓結(jié)果繪制的曲線更加平滑。這種操作可以代替表的自聯(lián)接，并且 LAG 和 LEAD 有更高的效率。

LAG 和 LEAD 主要用來計算當(dāng)前行的前后 N 行的這種場景，一般情況下我們會對數(shù)據(jù)進(jìn)行排序，因為只有在有序的情況下，前面多少行和后面多少行才有意義。

LAG 和 LEAD 可以用在某些場景下代替自關(guān)聯(lián)的寫法。

總結(jié)

到此這篇關(guān)于Mysql基礎(chǔ)學(xué)習(xí)之LAG與LEAD開窗函數(shù)的文章就介紹到這了,更多相關(guān)Mysql LAG與LEAD開窗函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論