Pencere karmaşıklığını artırma

Orta Düzey SQL Server

Ginger Grant

Instructor

Toplamları gözden geçirme

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

Bir toplamaya ORDER BY ekleme

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

ORDER BY ile kümülatif toplam oluşturma

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

Satır numarası ekleme

ROW_NUMBER() penceredeki satırları sırayla numaralandırır
ROW_NUMBER() kullanırken ORDER BY zorunludur

T-SQL'de satır numarası ekleme

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

Hadi pratik yapalım!

Orta Düzey SQL Server