ROLLUP і CUBE

Підсумкова статистика та віконні функції в PostgreSQL

Michel Semaan

Data Scientist

Підсумки на рівні груп

Медалі Китаю та Росії на Літніх Олімпійських іграх 2008 за типом медалі

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | Total  | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | Total  | 143    |

Старий підхід

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, Medal
ORDER BY Country ASC, Medal ASC

UNION ALL

SELECT
  Country, 'Total', COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, 2
ORDER BY Country ASC;

Знайомство з ROLLUP

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, ROLLUP(Medal)
ORDER BY Country ASC, Medal ASC;

ROLLUP — підпункт GROUP BY, що додає рядки з підсумками для рівнів групування
GROUP BY Country, ROLLUP(Medal) підрахує підсумки для рівнів Country і Medal, а потім лише для Country, заповнивши Medal значеннями null у цих рядках

ROLLUP — запит

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY ROLLUP(Country, Medal)
ORDER BY Country ASC, Medal ASC;

ROLLUP є ієрархічним: «розагрегація» від лівого стовпця до правого
- ROLLUP(Country, Medal) включає підсумки на рівні Country
- ROLLUP(Medal, Country) включає підсумки на рівні Medal
- Обидва включають загальний підсумок

ROLLUP — результат

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | null   | 327    |

Підсумки на рівні груп містять null; рядок з усіма null — це загальний підсумок
Зверніть увагу: немає підсумків на рівні Medal, бо це ROLLUP(Country, Medal), а не ROLLUP(Medal, Country)

Знайомство з CUBE

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY CUBE(Country, Medal)
ORDER BY Country ASC, Medal ASC;

CUBE — неієрархічний різновид ROLLUP
Він генерує всі можливі підсумки на рівні груп
- CUBE(Country, Medal) рахує підсумки рівнів Country, Medal і загальний підсумок

CUBE — результат

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | Bronze | 113    |
| null    | Gold   | 117    |
| null    | Silver | 97     |
| null    | null   | 327    |

Зверніть увагу: додано підсумки на рівні Medal

ROLLUP vs CUBE

Джерело

| Year | Quarter | Sales |
|------|---------|-------|
| 2008 | Q1      | 12    |
| 2008 | Q2      | 15    |
| 2009 | Q1      | 21    |
| 2009 | Q2      | 27    |

Використовуйте ROLLUP, коли дані ієрархічні (наприклад, компоненти дати) і не потрібні всі можливі підсумки
Використовуйте CUBE, коли потрібні всі можливі підсумки на рівні груп

ROLLUP(Year, Quarter)

| Year | Quarter | Sales |
|------|---------|-------|
| 2008 | null    | 27    |
| 2009 | null    | 48    |
| null | null    | 75    |

CUBE(Year, Quarter)

Рядки вище + такі:

| Year | Quarter | Sales |
|------|---------|-------|
| null | Q1      | 33    |
| null | Q2      | 42    |

Давайте потренуємось!

Підсумкова статистика та віконні функції в PostgreSQL