Các hàm cửa sổ phổ biến

SQL Server trung cấp

Ginger Grant

Instructor

FIRST_VALUE() và LAST_VALUE()

FIRST_VALUE() trả về giá trị đầu tiên trong cửa sổ
LAST_VALUE() trả về giá trị cuối cùng trong cửa sổ

FIRST_VALUE

FIRST_VALUE() và LAST_VALUE() trong T-SQL

Lưu ý: với FIRST_VALUE và LAST_VALUE cần có 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

Kết quả

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

Lấy giá trị tiếp theo với LEAD()

Cho phép truy vấn giá trị của hàng tiếp theo
Cột NextQuota được tạo bằng LEAD()
Cần ORDER BY để sắp xếp các hàng

LEAD() trong T-SQL

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

Lấy giá trị trước với LAG()

Cho phép truy vấn giá trị của hàng trước
Cột PreviousQuota được tạo bằng LAG()
Cần ORDER BY để sắp xếp các hàng

LAG() trong T-SQL

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

Ayo berlatih!

SQL Server trung cấp