Gruppering med ROLLUP, CUBE och GROUPING SETS

Tidsserieanalys i SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Hierarkiska summor med 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

Kartesisk aggregering med 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

Definiera grupperingsuppsättningar med 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

Nu kör vi en övning!

Tidsserieanalys i SQL Server