ウィンドウ関数

中級 SQL Server

Ginger Grant

Instructor

ウィンドウ関数

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

T-SQLのウィンドウ構文

OVER 句でウィンドウを作成
PARTITION BY でフレームを定義
PARTITION BY がない場合、フレームは全表
並べ替えは ORDER BY
集計をウィンドウと同時に計算可能

. . . 
-- ウィンドウによるデータグループ化
OVER (PARTITION BY SalesYear ORDER BY SalesYear)

ウィンドウ関数（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|
...          |          |             |            |          |
+------------+----------+-------------+------------+----------+

ウィンドウ関数（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|
...
+------------+----------+------------+------------+----------+

QuotaPerYear 列では各ウィンドウごとにカウントがリセットされます

Let's practice!

中級 SQL Server