Tăng độ phức tạp của cửa sổ

SQL Server trung cấp

Ginger Grant

Instructor

Ôn lại phép gom nhóm

SELECT SalesPerson, SalesYear, CurrentQuota, 
       SUM(CurrentQuota)  
       OVER (PARTITION BY SalesYear) AS YearlyTotal, 
       ModifiedDate as ModDate
FROM SaleGoal

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

Thêm ORDER BY vào phép gom nhóm

SELECT SalesPerson, SalesYear, CurrentQuota, 
       SUM(CurrentQuota)  
       OVER (PARTITION BY SalesYear ORDER BY SalesPerson) AS YearlyTotal, 
       ModifiedDate as ModDate
FROM SaleGoal

+------------+----------+------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota|YearTotal   | ModDate  |
+------------+----------+------------+------------+----------+
|Bob         |2011      |28000.00    |35000.00    |2011-04-16|
|Bob         |2011      |7000.00     |35000.00    |2011-07-17| 
|Mary        |2011      |367000.00   |958000.00   |2011-10-17|
|Mary        |2011      |556000.00   |958000.00   |2012-04-15|
|Bob         |2012      |70000.00    |401000.00   |2012-07-15|
|Bob         |2012      |154000.00   |401000.00   |2012-10-16|
...
+------------+----------+------------+------------+----------+

Tạo tổng luỹ kế với ORDER BY

SELECT SalesPerson, SalesYear, CurrentQuota, 
       SUM(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) as RunningTotal, 
       ModifiedDate as ModDate
FROM SaleGoal

+------------+----------+------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota|RunningTotal| ModDate  |
+------------+----------+------------+------------+----------+
|Bob         |2011      |28000.00    |28000.00    |2011-04-16|
|Mary        |2011      |367000.00   |395000.00   |2011-07-17| 
|Mary        |2011      |556000.00   |951000.00   |2011-10-17|
|Bob         |2011      |7000.00     |958000.00   |2012-04-15|
|Bob         |2012      |70000.00    |70000.00    |2012-01-15|
|Mary        |2012      |502000.00   |572000.00   |2012-01-16|
...
+------------+----------+------------+------------+----------+

Thêm số thứ tự hàng

ROW_NUMBER() đánh số tuần tự các hàng trong cửa sổ
Cần có ORDER BY khi dùng ROW_NUMBER()

Thêm số thứ tự hàng trong T-SQL

SELECT SalesPerson, SalesYear, CurrentQuota,  
       ROW_NUMBER() 
       OVER (PARTITION BY SalesPerson ORDER BY SalesYear) AS QuotabySalesPerson
FROM SaleGoal

+------------+----------+------------+------------------+
|SalesPerson |SalesYear |CurrentQuota|QuotabySalesPerson|
+------------+----------+------------+------------------+
|Bob         |2011      |28000.00    |1                 |
|Bob         |2011      |7000.00     |2                 | 
|Bob         |2011      |70000.00    |3                 |
|Bob         |2011      |154000.00   |4                 |
|Bob         |2012      |70000.00    |5                 |
|Bob         |2012      |107000.00   |6                 |
|Bob         |2012      |91000.00    |7                 |
|Mary        |2011      |367000.00   |1                 |
...
+------------+----------+------------+------------------+

Hãy thực hành!

SQL Server trung cấp