Funcții de fereastră

SQL Server intermediar

Ginger Grant

Instructor

Gruparea datelor în 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     |
+------------+----------+---------------+----------------+

Sintaxa ferestrelor în T-SQL

Fereastra se creează cu clauza OVER
PARTITION BY definește cadrul ferestrei
Fără PARTITION BY, cadrul este întregul tabel
Utilizați ORDER BY pentru a ordona rezultatele
Permite crearea de agregări în același timp cu fereastra

. . . 
-- Creare grupare prin fereastră
OVER (PARTITION BY SalesYear ORDER BY SalesYear)

Funcții de fereastră (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|
...          |          |             |            |          |
+------------+----------+-------------+------------+----------+

Funcții de fereastră (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|
...
+------------+----------+------------+------------+----------+

Numărătoarea se reia pentru fiecare fereastră în coloana QuotaPerYear

Să exersăm!

SQL Server intermediar