Группировка с 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