ROLLUP và CUBE

Thống kê tóm tắt và Window Functions trong PostgreSQL

Michel Semaan

Data Scientist

Tổng theo cấp nhóm

Huy chương của Trung Quốc và Nga tại Thế vận hội Mùa hè 2008 theo hạng huy chương

| 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    |

Cách làm cũ

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;

Giới thiệu 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 là tiểu mệnh đề GROUP BY thêm các hàng cho tổng theo cấp nhóm
GROUP BY Country, ROLLUP(Medal) sẽ đếm toàn bộ tổng theo cấp Country và Medal, sau đó chỉ đếm tổng theo cấp Country và điền Medal bằng null cho các hàng này

ROLLUP - Truy vấn

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 có thứ bậc, khử gộp từ cột trái nhất sang phải nhất
- ROLLUP(Country, Medal) bao gồm tổng theo cấp Country
- ROLLUP(Medal, Country) bao gồm tổng theo cấp Medal
- Cả hai đều có tổng cộng (grand total)

ROLLUP - Kết quả

| 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    |

Các tổng theo cấp nhóm chứa null; hàng toàn null là tổng cộng
Lưu ý không có tổng theo cấp Medal, vì dùng ROLLUP(Country, Medal) chứ không phải ROLLUP(Medal, Country)

Giới thiệu 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 là một dạng ROLLUP không thứ bậc
Tạo mọi tổ hợp gộp nhóm có thể có
- CUBE(Country, Medal) đếm theo cấp Country, cấp Medal, và tổng cộng

CUBE - Kết quả

| 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    |

Lưu ý đã có tổng theo cấp Medal

ROLLUP vs CUBE

Nguồn

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

Dùng ROLLUP khi dữ liệu có thứ bậc (ví dụ: thành phần ngày) và bạn không cần mọi tổ hợp gộp nhóm
Dùng CUBE khi bạn cần mọi tổ hợp gộp nhóm

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

Các hàng trên + phần sau

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

Ayo berlatih!

Thống kê tóm tắt và Window Functions trong PostgreSQL