一.基本介紹

1.LAG 和 LEAD 概述

在數(shù)據(jù)庫(kù)查詢(xún)中，開(kāi)窗函數(shù)（Window Functions）是一種強(qiáng)大的工具，用于在結(jié)果集的子集上執(zhí)行計(jì)算。LAG和LEAD是兩個(gè)常用的開(kāi)窗函數(shù)，它們?cè)试S你在當(dāng)前行的前面或后面的行中訪(fǎng)問(wèn)數(shù)據(jù)。
在數(shù)據(jù)庫(kù)中，LAG和LEAD函數(shù)的全稱(chēng)是：

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

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

2.作用特性

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

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

3.LAG 函數(shù)

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

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é)果集范圍時(shí)，返回的默認(rèn)值。

4.LEAD 函數(shù)

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

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é)果集范圍時(shí)，返回的默認(rèn)值。

二.使用案例

1.LAG 示例

考慮一個(gè)名為sales的表，包含銷(xiāo)售數(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ù)，你可以獲取前一天的銷(xiāo)售額：

SELECT sale_date                                             -- 日期
     , revenue                                               -- 當(dāng)前銷(xiāo)售額
     , LAG(revenue) OVER (ORDER BY sale_date) AS lag_revenue -- 前一行的銷(xiāo)售額
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ù)，你可以獲取后一天的銷(xiāo)售額：

SELECT sale_date                                               -- 日期
     , revenue                                                 -- 日期
     , LEAD(revenue) OVER (ORDER BY sale_date) AS lead_revenue -- 后一行的銷(xiāo)售額
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         |

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

3.總結(jié)說(shuō)明

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

LAG 和 LEAD 主要用來(lái)計(jì)算當(dāng)前行的前后 N 行的這種場(chǎng)景，一般情況下我們會(huì)對(duì)數(shù)據(jù)進(jìn)行排序，因?yàn)橹挥性谟行虻那闆r下，前面多少行和后面多少行才有意義。

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

總結(jié)

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

最新評(píng)論