使用 ROLLUP、CUBE 和 GROUPING SETS 分组

SQL Server 中的时间序列分析

Maham Faisal Khan

Senior Data Science Content Developer

使用 ROLLUP 的分层汇总

SELECT
    t.Month,
    t.Day,
    SUM(t.Events) AS Events
FROM Table
GROUP BY
    t.Month,
    t.Day
WITH ROLLUP
ORDER BY
    t.Month,
    t.Day;

Month	Day	Events
NULL	NULL	100
1	NULL	60
1	1	3
1	2	4
...	...	...
2	NULL	40
2	1	8

使用 CUBE 的笛卡尔聚合

SELECT
    t.IncidentType,
    t.Office,
    SUM(t.Events) AS Events
FROM Table
GROUP BY
    t.IncidentType,
    t.Office
WITH CUBE
ORDER BY
    t.IncidentType,
    t.Office;

IncidentType	Office	Events
NULL	NULL	250
NULL	NY	70
NULL	CT	180
T1	NULL	55
T1	NY	30
T1	CT	25

用 GROUPING SETS 定义分组集

SELECT
    t.IncidentType,
    t.Office,
    SUM(t.Events) AS Events
FROM Table
GROUP BY GROUPING SETS
(
  (t.IncidentType, t.Office),
  ()
)
ORDER BY
    t.IncidentType,
    t.Office;

IncidentType	Office	Events
NULL	NULL	250
T1	NY	30
T1	CT	25
T2	NY	10
T2	CT	110
T3	NY	30
T3	CT	45

让我们来练习！

SQL Server 中的时间序列分析