MySQL LEAD() and LAG()用法及代碼示例

MySQL中的LEAD()和LAG()函數用於獲取其分區內任何行的前後值。這些函數稱為非聚合窗口函數。

窗口函數是對分區或窗口的每一行執行操作的那些函數。這些函數為每個查詢行生成結果，這與將它們分組並在單個行中生成結果的聚合函數不太可能。

• 發生操作的行稱為當前行。
• 表現為當前行或使用哪個函數在當前行上不透明的行的集合稱為“窗口”。

LAG()函數用於從當前行之前的行中獲取值。

LEAD()函數用於從成功當前行的行中獲取值。

用法:
對於LEAD()函數-

LEAD(expr, N, default) 
          OVER (Window_specification | Window_name)

對於LAG()函數-

LAG(expr, N, default) 
          OVER (Window_specification | Window_name)

函數中的N和默認參數是可選的。

參數：

1. expr:它可以是列或任何內置函數。
2. N:它是一個正值，它確定當前行之前/之後的行數。如果在查詢中將其省略，則其默認值為1。
3. default:如果在當前行之前/之後沒有行N行的情況下，它是函數返回的默認值。如果缺少，則默認為NULL。
4. OVER()：它定義了如何將行劃分為組。如果OVER()為空，則函數使用所有行計算結果。
5. Window_specification:它由查詢分區子句組成，該子句確定查詢行的分區和排序方式。
6. Window_name:如果在查詢的其他位置指定了窗口，則使用此Window_name對其進行引用。

例：
考慮一個“contest”表：

c_id	start_date	end_date
1	2015-02-01	2015-02-04
2	2015-02-02	2015-02-05
3	2015-02-03	2015-02-07
4	2015-02-04	2015-02-06
5	2015-02-06	2015-02-09
6	2015-02-08	2015-02-10
7	2015-02-10	2015-02-11

在上表中，“c_id”代表比賽ID，“start_date”和“end_date”分別代表比賽的開始日期和結束日期。

問題描述：我們必須找出下一場比賽將要結束的天數，即沒有。兩場比賽舉行的日期。

查詢：

Select c_id, start_date, end_date, 
        end_date - lead (start_date) 
        over (order by start_date) 
               + 1 as 'no_of_days' 
                   from contest;

在上麵的查詢中，“end_date”返回當前比賽的結束日期，線索(start_date)返回下一個比賽的開始日期。因此，這些日期之間的差加1將返回否。比賽將在幾天後發生衝突。

此處，窗口規範由“order by”子句給出，該子句表示lead()函數將按其“start_date”的升序對表進行操作。由於它們不是分區子句，因此整個表被視為單個窗口。

輸出：

c_id	start_date	end_date	no_of_days
1	2015-02-01	2015-02-04	3
2	2015-02-02	2015-02-05	3
3	2015-02-03	2015-02-07	4
4	2015-02-04	2015-02-06	1
5	2015-02-06	2015-02-09	2
6	2015-02-08	2015-02-10	1
7	2015-02-10	2015-02-11	NULL

由於比賽7(即c_id = 7)之後沒有比賽。因此，lead(start_date)返回NULL值。

注意：LEAD()和LAG()函數始終與OVER()一起使用。缺少over子句將引發錯誤。