ROLLUP और CUBE

PostgreSQL Summary Stats और 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 एक 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 बनाम CUBE

Source

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

जब आपके पास हायरार्किकल डेटा हो (जैसे, date parts) और आप सभी संभावित ग्रुप-स्तर एग्रीगेशन नहीं चाहते, तो 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 और Window Functions