常見視窗函式

SQL Server 中級

Ginger Grant

Instructor

FIRST_VALUE() 與 LAST_VALUE()

FIRST_VALUE() 傳回視窗中的第一個值
LAST_VALUE() 傳回視窗中的最後一個值

FIRST_VALUE

T-SQL 中的 FIRST_VALUE() 與 LAST_VALUE()

注意：使用 FIRST_VALUE 與 LAST_VALUE 時需要 ORDER BY

-- Select the columns
SELECT SalesPerson, SalesYear, CurrentQuota, 
    -- First value from every window
       FIRST_VALUE(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS StartQuota, 
    -- Last value from every window 
       LAST_VALUE(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS EndQuota, 
       ModifiedDate as ModDate
FROM SaleGoal

結果

+------------+----------+------------+----------+----------+----------+
|SalesPerson |SalesYear |CurrentQuota|StartQuota| EndQuota |ModDate   |
+------------+----------+------------+----------+----------+----------+
|Bob         |2011      |28000.00    |28000.00  |91000.00  |2011-04-16|
|Bob         |2011      |7000.00     |28000.00  |91000.00  |2011-07-17| 
|Bob         |2011      |91000.00    |28000.00  |91000.00  |2011-10-17|
|Bob         |2012      |140000.00   |140000.00 |107000.00 |2012-01-15|
|Bob         |2012      |70000.00    |140000.00 |107000.00 |2012-04-15|
|Bob         |2012      |154000.00   |140000.00 |107000.00 |2012-07-16|
|Bob         |2012      |107000.00   |140000.00 |107000.00 |2012-10-16|
...
+------------+----------+------------+----------+----------+----------+

用 LEAD() 取得下一個值

可取得下一列的值
使用 LEAD() 建立 NextQuota 欄位
需要使用 ORDER BY 排序列

T-SQL 中的 LEAD()

SELECT SalesPerson, SalesYear, CurrentQuota, 
-- Create a window function to get the values from the next row
       LEAD(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS NextQuota, 
       ModifiedDate AS ModDate
FROM SaleGoal

+------------+----------+------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota|NextQuota   | ModDate  |
+------------+----------+------------+------------+----------+
|Bob         |2011      |28000.00    |367000.00   |2011-04-15|
|Mary        |2011      |367000.00   |556000.00   |2011-04-16| 
|Mary        |2011      |556000.00   |7000.00     |2011-07-15| 
|Bob         |2011      |7000.00     |NULL        |2011-07-17|
|Bob         |2012      |70000.00    |502000.00   |2012-01-15|
|Mary        |2012      |502000.00   |154000.00   |2012-01-16| 
...
+------------+----------+------------+------------+----------+

用 LAG() 取得前一個值

可取得前一列的值
使用 LAG() 建立 PreviousQuota 欄位
需要使用 ORDER BY 排序列

T-SQL 中的 LAG()

SELECT SalesPerson, SalesYear, CurrentQuota, 
-- Create a window function to get the values from the previous row
       LAG(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS PreviousQuota, 
       ModifiedDate AS ModDate
FROM SaleGoal

+------------+----------+------------+-------------+----------+
|SalesPerson |SalesYear |CurrentQuota|PreviousQuota |ModDate  |
+------------+----------+------------+-------------+----------+
|Bob         |2011      |28000.00    |NULL         |2011-04-15|
|Mary        |2011      |367000.00   |28000.00     |2011-04-16| 
|Mary        |2011      |556000.00   |367000.00    |2011-07-15|
|Bob         |2011      |7000.00.00  |556000.00    |2011-07-17|
|Bob         |2012      |7000.00     |NULL         |2012-01-15|
|Mary        |2012      |502000.00   |7000.00      |2012-01-16|
...
+------------+----------+------------+------------+----------+

一起來練習吧！

SQL Server 中級