PivotTable và GROUP BY

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

• Ánh xạ PivotTable sang GROUP BY từng bước.
• Cách đặt trường vào Rows, Columns, Values trong SQL.
• Lọc trước và sau khi tổng hợp (WHERE và HAVING).
• Cách tạo báo cáo chéo hai chiều.
• Những khác biệt khiến SQL ổn định hơn với dữ liệu lớn.

Khái niệm cốt lõi

PivotTable có bốn vùng: Rows, Columns, Values, Filters. SQL có bốn thành phần tương ứng:

Hiểu ánh xạ này là đủ để chuyển phần lớn báo cáo quen thuộc sang SQL.

Từ PivotTable đơn giản

Giả sử bạn kéo city vào Rows, doanh_thu vào Values (Sum). Trong SQL:

SELECT c.city,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY c.city
ORDER BY doanh_thu DESC;

• GROUP BY c.city tạo một dòng mỗi thành phố.
• SUM(...) là Values.
• WHERE là filter áp trước khi tổng hợp.

Điều quan trọng: mọi cột trong SELECT không nằm trong hàm tổng hợp đều phải có trong GROUP BY. Đây là quy tắc SQL chuẩn, khác Excel — Excel tự gom nhóm, SQL bắt bạn khai báo.

Hai chiều: Rows và Columns

PivotTable đặt city ở Rows và category ở Columns. SQL có hai cách.

Cách 1 — nhóm theo cả hai chiều, kết quả dạng dài:

SELECT c.city,
       p.category,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'completed'
GROUP BY c.city, p.category
ORDER BY c.city, p.category;

Kết quả nhiều dòng hơn nhưng dễ lọc, dễ vẽ biểu đồ và không cần biết trước danh sách category.

Cách 2 — tạo cột bằng FILTER, kết quả dạng rộng giống PivotTable:

SELECT c.city,
       SUM(oi.quantity * oi.unit_price)
         FILTER (WHERE p.category = 'Điện tử') AS dien_tu,
       SUM(oi.quantity * oi.unit_price)
         FILTER (WHERE p.category = 'Gia dụng') AS gia_dung,
       SUM(oi.quantity * oi.unit_price) AS tong
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'completed'
GROUP BY c.city;

FILTER là cú pháp PostgreSQL, gọn hơn CASE WHEN ... THEN ... END. Nhược điểm: phải biết trước danh sách category; có category mới thì phải sửa truy vấn. Cách 1 linh hoạt hơn cho khám phá, cách 2 tiện cho báo cáo cố định.

Lọc trước và sau tổng hợp

Đây là chỗ nhiều người mới nhầm:

• Lọc dòng đầu vào (chỉ tính đơn completed) → WHERE.
• Lọc nhóm kết quả (chỉ giữ thành phố có doanh thu trên 10 triệu) → HAVING.

SELECT c.city,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY c.city
HAVING SUM(oi.quantity * oi.unit_price) > 10000000
ORDER BY doanh_thu DESC;

Nếu đặt điều kiện doanh thu vào WHERE, truy vấn lỗi vì hàm tổng hợp chưa có giá trị. Quy tắc: WHERE chạy trước GROUP BY, HAVING chạy sau.

Các hàm tổng hợp thường dùng

Khác biệt COUNT(*) và COUNT(cot) rất hay gây lệch số. Nếu cột có NULL, AVG(cot) chia cho số dòng có giá trị, không phải tổng số dòng. Muốn tính trung bình trên toàn bộ dòng, dùng SUM(cot) / COUNT(*) hoặc COALESCE.

Vì sao SQL ổn định hơn

• PivotTable phải làm mới khi dữ liệu đổi; SQL chạy lại là có số mới.
• Dữ liệu hàng triệu dòng vượt giới hạn Excel; SQL xử lý bình thường.
• Công thức PivotTable nằm trong file cá nhân; SQL lưu được trong Git, có review.
• Nhiều người có thể chạy cùng một truy vấn và ra cùng kết quả; mỗi file Excel là một phiên bản.

Thực hành

1. Chuyển một PivotTable theo city và SUM(doanh_thu) sang SQL.
2. Thêm category làm chiều thứ hai, viết cả hai cách (dài và rộng).
3. Lọc chỉ đơn completed bằng WHERE.
4. Giữ thành phố có doanh thu trên 10 triệu bằng HAVING.
5. So COUNT(*), COUNT(email), COUNT(DISTINCT city) trên bảng customers.

Lỗi thường gặp

Tóm tắt

• Rows → GROUP BY, Values → hàm tổng hợp, Filters → WHERE/HAVING.
• Mọi cột không tổng hợp phải có trong GROUP BY.
• Báo cáo chéo: nhóm hai chiều dạng dài hoặc FILTER dạng rộng.
• WHERE lọc dòng, HAVING lọc nhóm.
• COUNT(*) khác COUNT(cot) khi có NULL.