Raggruppare con ROLLUP, CUBE e GROUPING SETS

Analisi delle serie temporali in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Rollup gerarchici con 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

Aggregazione cartesiana con 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

Definire i gruppi con 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

Passiamo alla pratica!

Analisi delle serie temporali in SQL Server