Seskupení pomocí ROLLUP, CUBE a GROUPING SETS

Time Series Analysis in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Hierarchické souhrny s 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

Kartézská agregace s 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

Definice skupin pomocí 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

Pojďme si procvičit!

Time Series Analysis in SQL Server