常见窗口函数

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|
...
+------------+----------+------------+------------+----------+

Passons à la pratique !

SQL Server 中级