Fönsterfunktioner

SQL Server för medelnivå

Ginger Grant

Instructor

Fönsterfunktioner

Gruppera data i T-SQL

SELECT SalesPerson, SalesYear, 
       CurrentQuota, ModifiedDate
FROM SaleGoal
WHERE SalesYear = 2011

+------------+----------+---------------+----------------+
|SalesPerson |SalesYear | CurrentQuota  | ModifiedDate   |
+------------+----------+---------------+----------------+
| Bob        | 2011     | 28000.00      | 2011-04-16     |
| Bob        | 2011     |  7000.00      | 2011-07-16     |
| Bob        | 2011     | 91000.00      | 2011-10-16     |
| Mary       | 2011     |367000.00      | 2011-04-16     |
| Mary       | 2011     |556000.00      | 2011-07-16     |
| Mary       | 2011     |502000.00      | 2011-10-16     |
+------------+----------+---------------+----------------+

Fönstersynax i T-SQL

Skapa fönstret med klausulen OVER
PARTITION BY definierar ramen
Utan PARTITION BY utgörs ramen av hela tabellen
Använd ORDER BY för att sortera resultaten
Möjliggör aggregeringar samtidigt som fönstret skapas

. . . 
-- Create a Window data grouping
OVER (PARTITION BY SalesYear ORDER BY SalesYear)

Fönsterfunktioner (SUM)

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| 
|Mary        |2011      |367000.00    |1551000.00  |2011-04-16|
|Mary        |2011      |556000.00    |1551000.00  |2011-07-15|
|Bob         |2012      |70000.00     |1859000.00  |2012-01-15|
|Bob         |2012      |154000.00    |1859000.00  |2012-04-16|
|Bob         |2012      |107000.00    |1859000.00  |2012-07-16|
...          |          |             |            |          |
+------------+----------+-------------+------------+----------+

Fönsterfunktioner (COUNT)

SELECT SalesPerson, SalesYear, CurrentQuota, 
       COUNT(CurrentQuota) 
       OVER (PARTITION BY SalesYear) AS QuotaPerYear, 
       ModifiedDate AS ModDate
FROM SaleGoal

+------------+----------+------------+--------------+----------+
|SalesPerson |SalesYear |CurrentQuota|QuotaPerYear  | ModDate  |
+------------+----------+------------+--------------+----------+
|Bob         |2011      |28000.00    |4             |2011-04-16|
|Bob         |2011      |7000.00     |4             |2011-07-17| 
|Mary        |2011      |367000.00   |4             |2011-04-16|
|Mary        |2011      |556000.00   |4             |2011-07-15|
|Bob         |2012      |70000.00    |8             |2012-01-15|
|Bob         |2012      |154000.00   |8             |2012-04-15|
|Bob         |2012      |107000.00   |8             |2012-10-16|
...
+------------+----------+------------+------------+----------+

Observera att räkningen börjar om för varje fönster i kolumnen QuotaPerYear

Nu kör vi en övning!

SQL Server för medelnivå