Bài tập: danh mục trên trung bình

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

• Dùng CTE để tính doanh thu theo danh mục đúng một lần.
• So sánh kết quả gom nhóm với mức trung bình của chính các nhóm.
• Phân biệt "trung bình của các danh mục" với "trung bình của các dòng hàng".
• Giữ đúng tên cột và thứ tự mà bài chấm yêu cầu.
• Kiểm chứng bằng tổng doanh thu trước khi nộp.

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

Đề bài Danh mục trên mức doanh thu trung bình:

Tính doanh thu từng category, chỉ giữ danh mục có doanh thu lớn hơn doanh thu trung bình của các danh mục; sắp giảm dần.

Đề có hai điểm cần đọc kỹ:

Cấu trúc giống bài tập cuối khóa cơ bản, nhưng hạt khác: ở đó là khách, ở đây là danh mục. Điều không đổi là nguyên tắc: tính chỉ số trước, so sánh sau. Không thể viết HAVING SUM(...) > AVG(SUM(...)) trong một lớp — phải tách bằng CTE.

Đáp án:

WITH revenue AS (
  SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category
)
SELECT category, revenue
FROM revenue
WHERE revenue > (SELECT AVG(revenue) FROM revenue)
ORDER BY revenue DESC;

Kết quả hai dòng:

Bốn danh mục, tổng 31.230.000, trung bình 7.807.500. Thời trang 2.250.000 và Sách 1.980.000 nằm dưới ngưỡng. Chênh lệch rất lớn — Điện tử gần gấp đôi mức trung bình — nên kết quả chỉ có hai dòng là hợp lý.

Ba quyết định trong câu lệnh:

Một, COUNT/SUM chỉ trên order_items nối products. Không cần nối orders vì đề không yêu cầu lọc theo trạng thái. Nếu tự ý thêm WHERE o.status = 'completed', bạn đang trả lời một câu hỏi khác và bài chấm sẽ báo sai. Hãy làm đúng những gì đề nói, không hơn.

Hai, CTE được dùng hai lần. Một lần trong SELECT của câu chính, một lần trong truy vấn con AVG(revenue). Đây chính là lý do CTE phù hợp hơn bảng tạm: viết một lần, đọc hai nơi.

Ba, bí danh revenue là tên cột kết quả. Đề ghi "doanh thu" nhưng bài chấm so theo tên cột trong đáp án mẫu: category và revenue. Đặt doanh_thu sẽ bị báo sai dù con số đúng.

Một cạm bẫy tinh vi: "trung bình của các danh mục" là trung bình bốn con số 15.180.000; 11.820.000; 2.250.000; 1.980.000. Nếu ai đó viết:

SELECT AVG(quantity * unit_price) FROM order_items;

họ đang tính trung bình của 29 dòng hàng — một con số hoàn toàn khác và vô nghĩa cho câu hỏi này. Luôn xác định rõ đơn vị của phép trung bình: mỗi dòng đại diện cho cái gì?

Kiểm chứng nhanh bằng tay:

• 15.180.000 + 11.820.000 + 2.250.000 + 1.980.000 = 31.230.000.
• 31.230.000 / 4 = 7.807.500.
• Hai danh mục lớn hơn 7.807.500.

Ví dụ chạy được

Xem toàn bộ bảng trung gian trước khi lọc:

WITH revenue AS (
  SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category
)
SELECT category, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS phan_tram,
       ROUND(revenue - AVG(revenue) OVER (), 0) AS chenh_lech_trung_binh
FROM revenue
ORDER BY revenue DESC;

Bốn dòng, mỗi danh mục kèm tỷ trọng và chênh lệch so với trung bình 7.807.500:

Cột chenh_lech_trung_binh cho thấy ngay hai danh mục dương và hai danh mục âm — kết quả của câu lọc phải là hai dòng dương.

Bản sai dùng HAVING với truy vấn con trên bảng gốc:

SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
HAVING SUM(oi.quantity * oi.unit_price) > (SELECT AVG(quantity * unit_price) FROM order_items);

Câu này chạy được nhưng so sánh doanh thu danh mục với trung bình từng dòng hàng (khoảng 1,08 triệu). Kết quả sẽ là cả bốn danh mục, không phải hai — một lỗi logic không hề báo lỗi cú pháp.

Bản sai thứ hai, đặt điều kiện vào WHERE cùng lớp:

-- LỖI: không dùng được hàm tổng hợp trong WHERE
SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE SUM(oi.quantity * oi.unit_price) > 7807500
GROUP BY p.category;

PostgreSQL báo aggregate functions are not allowed in WHERE. Điều kiện trên kết quả gom nhóm thuộc HAVING, nhưng vì ngưỡng lại là trung bình của chính kết quả đó, CTE vẫn là cách sạch nhất.

Kiểm tra tổng trước khi nộp:

WITH revenue AS (
  SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category
)
SELECT COUNT(*) AS so_danh_muc, SUM(revenue) AS tong, ROUND(AVG(revenue), 0) AS trung_binh
FROM revenue;

Kết quả 4 | 31230000 | 7807500. Ba con số này là "chứng minh thư" của bài: nếu lệch, đừng nộp.

Thực hành

1. Nộp đáp án cho bài Danh mục trên mức doanh thu trung bình (categories-above-average-revenue). Giữ đúng tên cột category, revenue và ORDER BY revenue DESC.
2. Chạy riêng CTE revenue và đối chiếu bốn con số với bảng ở trên.
3. Thử thêm WHERE o.status = 'completed' (nối thêm orders) và giải thích vì sao kết quả khác. Đây là bài học về việc không tự thêm điều kiện ngoài đề.
4. Viết biến thể tính trung bình có trọng số theo số dòng hàng mỗi danh mục và so sánh ngưỡng. Cách tính nào hợp lý hơn cho báo cáo kinh doanh, theo bạn?
5. Đổi > thành >= và xác nhận kết quả không đổi — không danh mục nào đúng bằng 7.807.500.

Lỗi thường gặp

Lỗi đầu tiên là lỗi hay gặp nhất vì câu lệnh vẫn chạy và vẫn "có vẻ đúng". Cách phòng: viết ra giấy "trung bình của bốn danh mục" trước khi gõ SQL, rồi kiểm tra xem AVG có đang chạy trên bảng bốn dòng hay không.

Tóm tắt

• CTE revenue tính doanh thu mỗi danh mục, dùng lại cho cả SELECT và truy vấn con.
• Ngưỡng là trung bình của các danh mục — 7.807.500 — không phải trung bình dòng hàng.
• Giữ đúng tên cột category, revenue và sắp giảm dần.
• Không thêm điều kiện trạng thái khi đề không yêu cầu.
• Kiểm chứng bằng COUNT(*) = 4, SUM = 31.230.000, AVG = 7.807.500.

Chương tiếp theo mở ra bộ công cụ mạnh nhất của SQL phân tích: window functions — tính toán trên nhóm dòng mà vẫn giữ nguyên từng dòng chi tiết.