ROLLUP และ CUBE

PostgreSQL Summary Stats and Window Functions

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 คือ subclause ของ GROUP BY ที่เพิ่มแถวสำหรับการรวมข้อมูลระดับกลุ่ม
GROUP BY Country, ROLLUP(Medal) จะนับยอดรวมระดับ Country และ Medal แล้วนับเฉพาะระดับ Country โดยใส่ null ใน Medal สำหรับแถวเหล่านั้น

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 เทียบกับ CUBE

Source

| 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 Summary Stats and Window Functions