GROUPING SETS, CUBE, ROLLUP

← Phép toán tập hợp - UNION, INTERSECT, EXCEPT | → Truy vấn con - Subquery, ANY, ALL, EXISTS

Bạn sẽ học được

Tạo báo cáo nhiều cấp tổng hợp (tổng, tổng con, tổng chung) trong một lần quét bảng thay vì nhiều truy vấn UNION ALL.

---

1. Bảng ví dụ

DROP TABLE IF EXISTS sales;
 
CREATE TABLE sales (
    brand VARCHAR NOT NULL,
    segment VARCHAR NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (brand, segment)
);
 
INSERT INTO sales (brand, segment, quantity)
VALUES
    ('ABC', 'Premium', 100),
    ('ABC', 'Basic',   200),
    ('XYZ', 'Premium', 100),
    ('XYZ', 'Basic',   300);

 brand | segment | quantity
-------+---------+----------
 ABC   | Premium |      100
 ABC   | Basic   |      200
 XYZ   | Premium |      100
 XYZ   | Basic   |      300

---

2. Grouping set là gì?

Một grouping set là một bộ cột dùng để nhóm, viết trong ngoặc đơn: (brand, segment), (brand), (segment), ().

Với 2 cột brand và segment, có 4 cách nhóm:

Grouping set	Câu truy vấn tương ứng	Kết quả
(brand, segment)	GROUP BY brand, segment	4 dòng chi tiết
(brand)	GROUP BY brand	ABC=300, XYZ=400
(segment)	GROUP BY segment	Basic=500, Premium=200
()	SELECT sum(quantity)	700

2.1. Cách làm thủ công — dài và chậm

SELECT brand, segment, sum(quantity) FROM sales GROUP BY brand, segment
UNION ALL
SELECT brand, NULL,    sum(quantity) FROM sales GROUP BY brand
UNION ALL
SELECT NULL,  segment, sum(quantity) FROM sales GROUP BY segment
UNION ALL
SELECT NULL,  NULL,    sum(quantity) FROM sales;

 brand | segment | sum
-------+---------+-----
 XYZ   | Basic   | 300
 ABC   | Premium | 100
 ABC   | Basic   | 200
 XYZ   | Premium | 100
 ABC   | [NULL]  | 300
 XYZ   | [NULL]  | 400
 [NULL]| Basic   | 500
 [NULL]| Premium | 200
 [NULL]| [NULL]  | 700
(9 rows)

Hai vấn đề: câu lệnh dài dòng, và PostgreSQL phải quét bảng sales 4 lần.

---

3. GROUPING SETS

SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY GROUPING SETS (
    (brand, segment),
    (brand),
    (segment),
    ()
);

 brand | segment | sum
-------+---------+-----
 [NULL]| [NULL]  | 700
 XYZ   | Basic   | 300
 ABC   | Premium | 100
 ABC   | Basic   | 200
 XYZ   | Premium | 100
 ABC   | [NULL]  | 300
 XYZ   | [NULL]  | 400
 [NULL]| Basic   | 500
 [NULL]| Premium | 200
(9 rows)

Kết quả giống hệt nhưng ngắn hơn nhiều, và PostgreSQL chỉ quét bảng một lần.

Cú pháp tổng quát:

SELECT c1, c2, ham_tong_hop(c3)
FROM ten_bang
GROUP BY GROUPING SETS (
    (c1, c2),
    (c1),
    (c2),
    ()
);

---

4. Hàm GROUPING() — phân biệt NULL thật và NULL tổng hợp

Vấn đề: trong kết quả trên, NULL ở cột brand có thể là:

1. NULL do dòng tổng hợp (không nhóm theo brand), hoặc
2. NULL thật sự có trong dữ liệu.

Hàm GROUPING(cot) giải quyết điều này:

• Trả về 0 nếu cột đó có trong grouping set hiện tại.
• Trả về 1 nếu cột đó không trong grouping set (tức là đang tổng hợp).

SELECT
    GROUPING(brand)   AS g_brand,
    GROUPING(segment) AS g_segment,
    brand,
    segment,
    sum(quantity)
FROM sales
GROUP BY GROUPING SETS ((brand), (segment), ())
ORDER BY brand, segment;

 g_brand | g_segment | brand  | segment | sum
---------+-----------+--------+---------+-----
       0 |         1 | ABC    | [NULL]  | 300
       0 |         1 | XYZ    | [NULL]  | 400
       1 |         0 | [NULL] | Basic   | 500
       1 |         0 | [NULL] | Premium | 200
       1 |         1 | [NULL] | [NULL]  | 700
(5 rows)

Đọc bảng:

• g_brand = 0 → dòng này là tổng con theo brand.
• g_segment = 0 → dòng này là tổng con theo segment.
• Cả hai = 1 → dòng tổng chung.

4.1. Dùng GROUPING() để đặt nhãn dễ đọc

SELECT
    CASE WHEN GROUPING(brand) = 1   THEN '— Tất cả nhãn hiệu —' ELSE brand   END AS nhan_hieu,
    CASE WHEN GROUPING(segment) = 1 THEN '— Tất cả phân khúc —' ELSE segment END AS phan_khuc,
    sum(quantity) AS so_luong
FROM sales
GROUP BY GROUPING SETS ((brand, segment), (brand), (segment), ())
ORDER BY GROUPING(brand), GROUPING(segment), brand, segment;

      nhan_hieu       |      phan_khuc       | so_luong
----------------------+----------------------+----------
 ABC                  | Basic                |      200
 ABC                  | Premium              |      100
 XYZ                  | Basic                |      300
 XYZ                  | Premium              |      100
 ABC                  | — Tất cả phân khúc — |      300
 XYZ                  | — Tất cả phân khúc — |      400
 — Tất cả nhãn hiệu — | Basic                |      500
 — Tất cả nhãn hiệu — | Premium              |      200
 — Tất cả nhãn hiệu — | — Tất cả phân khúc — |      700

Sắp xếp theo GROUPING(...) trước đảm bảo các dòng chi tiết nằm trên, tổng con ở giữa, tổng chung cuối cùng.

4.2. Lọc theo GROUPING() trong HAVING

Chỉ lấy các dòng tổng con theo brand:

SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY GROUPING SETS ((brand), (segment), ())
HAVING GROUPING(brand) = 0;

 brand | segment | sum
-------+---------+-----
 ABC   | [NULL]  | 300
 XYZ   | [NULL]  | 400

---

5. ROLLUP — tổng hợp theo cấp bậc

ROLLUP(c1, c2, c3) sinh ra các grouping set theo thứ tự giảm dần từ phải sang trái:

ROLLUP(c1, c2, c3)  →  (c1, c2, c3)
                       (c1, c2)
                       (c1)
                       ()

Với n cột, ROLLUP sinh ra n + 1 grouping set.

SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY ROLLUP (brand, segment)
ORDER BY brand, segment;

 brand | segment | sum
-------+---------+-----
 ABC   | Basic   | 200
 ABC   | Premium | 100
 ABC   | [NULL]  | 300      ← tổng con của ABC
 XYZ   | Basic   | 300
 XYZ   | Premium | 100
 XYZ   | [NULL]  | 400      ← tổng con của XYZ
 [NULL]| [NULL]  | 700      ← tổng chung
(7 rows)

So với GROUPING SETS đầy đủ (9 dòng), ROLLUP thiếu hai dòng tổng theo segment — vì ROLLUP chỉ đi theo một chiều.

ROLLUP dùng cho dữ liệu có thứ bậc

Ví dụ điển hình: ROLLUP(quoc_gia, tinh, huyen) — huyện thuộc tỉnh, tỉnh thuộc quốc gia. Tổng của một huyện lẻ không có ý nghĩa nếu không biết nó thuộc tỉnh nào.

Thứ tự cột rất quan trọng: ROLLUP(brand, segment) ≠ ROLLUP(segment, brand).

5.1. Rollup một phần

Có thể cố định một số cột, chỉ rollup phần còn lại:

SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY brand, ROLLUP (segment)
ORDER BY brand, segment;

 brand | segment | sum
-------+---------+-----
 ABC   | Basic   | 200
 ABC   | Premium | 100
 ABC   | [NULL]  | 300
 XYZ   | Basic   | 300
 XYZ   | Premium | 100
 XYZ   | [NULL]  | 400
(6 rows)

Không có dòng tổng chung, vì brand luôn có mặt trong mọi grouping set.

5.2. Ví dụ thực tế — doanh thu theo năm/quý/tháng

SELECT
    EXTRACT(YEAR    FROM payment_date) AS nam,
    EXTRACT(QUARTER FROM payment_date) AS quy,
    EXTRACT(MONTH   FROM payment_date) AS thang,
    round(sum(amount), 2) AS doanh_thu
FROM payment
GROUP BY ROLLUP (
    EXTRACT(YEAR    FROM payment_date),
    EXTRACT(QUARTER FROM payment_date),
    EXTRACT(MONTH   FROM payment_date)
)
ORDER BY nam, quy, thang;

Cho ra: doanh thu từng tháng → tổng từng quý → tổng từng năm → tổng tất cả.

---

6. CUBE — mọi tổ hợp

CUBE(c1, c2) sinh ra tất cả các tổ hợp con của các cột:

CUBE(c1, c2)  →  (c1, c2)
                 (c1)
                 (c2)
                 ()

Với n cột, CUBE sinh ra 2ⁿ grouping set.

SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY CUBE (brand, segment)
ORDER BY brand, segment;

 brand | segment | sum
-------+---------+-----
 ABC   | Basic   | 200
 ABC   | Premium | 100
 ABC   | [NULL]  | 300
 XYZ   | Basic   | 300
 XYZ   | Premium | 100
 XYZ   | [NULL]  | 400
 [NULL]| Basic   | 500
 [NULL]| Premium | 200
 [NULL]| [NULL]  | 700
(9 rows)

Đây chính là 9 dòng của GROUPING SETS đầy đủ ở mục 3 — CUBE(a, b) là cách viết tắt của GROUPING SETS ((a,b), (a), (b), ()).

6.1. Cube một phần

SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY brand, CUBE (segment)
ORDER BY brand, segment;

6.2. Cảnh báo về số lượng nhóm

Số cột	ROLLUP	CUBE
2	3	4
3	4	8
4	5	16
5	6	32
10	11	1.024

CUBE tăng theo hàm mũ

CUBE với 10 cột trên bảng 10 triệu dòng sẽ tạo ra hàng trăm triệu dòng kết quả. Chỉ dùng CUBE với 2–4 cột, hoặc chỉ định chính xác các grouping set bạn cần bằng GROUPING SETS.

---

7. So sánh ba cách

	Sinh ra	Số grouping set	Dùng khi
GROUPING SETS (...)	Đúng những gì bạn liệt kê	Tùy bạn	Cần kiểm soát chính xác
ROLLUP(a, b, c)	Cắt dần từ phải	n + 1	Dữ liệu có thứ bậc
CUBE(a, b, c)	Mọi tổ hợp	2ⁿ	Phân tích đa chiều

Quan hệ tương đương:

ROLLUP (a, b)  ≡  GROUPING SETS ((a, b), (a), ())
CUBE   (a, b)  ≡  GROUPING SETS ((a, b), (a), (b), ())

Có thể kết hợp trong cùng một GROUP BY:

GROUP BY GROUPING SETS (
    ROLLUP (nam, quy),
    CUBE (vung, kenh),
    (san_pham)
)

---

8. Ứng dụng: báo cáo doanh thu DVD Rental

SELECT
    CASE WHEN GROUPING(co.country) = 1
         THEN '★ TOÀN CẦU' ELSE co.country END AS quoc_gia,
    CASE WHEN GROUPING(ci.city) = 1
         THEN '☆ Cả nước' ELSE ci.city END AS thanh_pho,
    count(DISTINCT c.customer_id) AS so_khach,
    round(sum(p.amount), 2)       AS doanh_thu
FROM payment p
JOIN customer c ON c.customer_id = p.customer_id
JOIN address a  ON a.address_id  = c.address_id
JOIN city ci    ON ci.city_id    = a.city_id
JOIN country co ON co.country_id = ci.country_id
WHERE co.country IN ('Vietnam', 'Japan', 'China')
GROUP BY ROLLUP (co.country, ci.city)
ORDER BY GROUPING(co.country), co.country,
         GROUPING(ci.city),    ci.city;

Kết quả: doanh thu từng thành phố → tổng từng quốc gia → tổng toàn bộ, tất cả trong một lần quét.

---

9. Bài tập

Dùng CSDL dvdrental:

1. Đếm số phim theo rating và rental_duration, kèm tổng con cho từng rating và tổng chung. Dùng ROLLUP.
2. Làm lại bài 1 nhưng dùng CUBE — kết quả khác nhau ở điểm nào?
3. Tính doanh thu theo staff_id và tháng, kèm tổng theo nhân viên và tổng chung.
4. Dùng GROUPING SETS để lấy chỉ hai loại tổng: theo rating và theo rental_duration, không cần chi tiết và không cần tổng chung.
5. Thêm nhãn “TỔNG” thay cho NULL trong kết quả bài 1.
6. CUBE(a, b, c, d, e) tạo ra bao nhiêu grouping set? Còn ROLLUP thì sao?

Đáp án

1.

SELECT rating, rental_duration, count(*) AS so_phim
FROM film
GROUP BY ROLLUP (rating, rental_duration)
ORDER BY rating, rental_duration;

2.

SELECT rating, rental_duration, count(*) AS so_phim
FROM film
GROUP BY CUBE (rating, rental_duration)
ORDER BY rating, rental_duration;

CUBE có thêm các dòng tổng theo rental_duration (bỏ qua rating) — tức (NULL, 3), (NULL, 4)… mà ROLLUP không có.

3.

SELECT p.staff_id,
       date_trunc('month', p.payment_date)::date AS thang,
       round(sum(p.amount), 2) AS doanh_thu
FROM payment p
GROUP BY ROLLUP (p.staff_id, date_trunc('month', p.payment_date))
ORDER BY p.staff_id, thang;

4.

SELECT rating, rental_duration, count(*) AS so_phim
FROM film
GROUP BY GROUPING SETS ((rating), (rental_duration))
ORDER BY GROUPING(rating), rating, rental_duration;

Đây chính là lúc GROUPING SETS hơn hẳn ROLLUP/CUBE: bạn chỉ định đúng những gì cần.

5.

SELECT
   CASE WHEN GROUPING(rating) = 1
        THEN 'TỔNG' ELSE rating::text END AS xep_hang,
   CASE WHEN GROUPING(rental_duration) = 1
        THEN 'TỔNG' ELSE rental_duration::text END AS so_ngay_thue,
   count(*) AS so_phim
FROM film
GROUP BY ROLLUP (rating, rental_duration)
ORDER BY GROUPING(rating), rating,
         GROUPING(rental_duration), rental_duration;

Chú ý rating::text và rental_duration::text — CASE yêu cầu mọi nhánh cùng kiểu.

6. CUBE với 5 cột → 2⁵ = 32 grouping set. ROLLUP với 5 cột → 5 + 1 = 6 grouping set.

---

10. Tóm tắt

• GROUPING SETS cho phép nhiều cách nhóm trong một lần quét bảng — thay thế UNION ALL dài dòng.
• GROUPING(cot) trả về 1 nếu cột đang bị tổng hợp, 0 nếu đang được nhóm theo → dùng để đặt nhãn và lọc.
• ROLLUP(a,b,c) cắt dần từ phải sang trái, sinh n+1 nhóm — dùng cho dữ liệu có thứ bậc, thứ tự cột quan trọng.
• CUBE(a,b,c) sinh mọi tổ hợp, 2ⁿ nhóm — dùng cho phân tích đa chiều, cẩn thận với nhiều cột.
• Có thể trộn: GROUP BY a, ROLLUP(b, c) hoặc GROUP BY GROUPING SETS (ROLLUP(...), CUBE(...)).

---

← Phép toán tập hợp - UNION, INTERSECT, EXCEPT | → Truy vấn con - Subquery, ANY, ALL, EXISTS