Fungsi window

SQL Server Menengah

Ginger Grant

Instructor

Fungsi Windowing

Pengelompokan data di 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     |
+------------+----------+---------------+----------------+

Sintaks window di T-SQL

Buat window dengan klausa OVER
PARTITION BY membuat frame
Jika tanpa PARTITION BY, frame mencakup seluruh tabel
Untuk mengurutkan hasil, gunakan ORDER BY
Memungkinkan agregasi dibuat bersamaan dengan window

. . . 
-- Buat pengelompokan data Window
OVER (PARTITION BY SalesYear ORDER BY SalesYear)

Fungsi window (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|
...          |          |             |            |          |
+------------+----------+-------------+------------+----------+

Fungsi window (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|
...
+------------+----------+------------+------------+----------+

Perhatikan hitungan dimulai ulang untuk tiap window di kolom QuotaPerYear

Ayo berlatih!

SQL Server Menengah