Bài tập: chi tiêu trên trung bình

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

• Chia một câu hỏi khó thành hai bước bằng CTE.
• Đặt điều kiện lọc trong ON thay vì WHERE để không làm mất khách chưa mua.
• Tính trung bình trên toàn bộ khách, kể cả người chi tiêu 0.
• Dùng COALESCE để khách chưa mua không làm hỏng phép tính.
• Kiểm chứng kết quả trước khi nộp bài.

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

Đề bài Khách chi tiêu trên trung bình:

Tính tổng chi tiêu mỗi khách (bỏ đơn cancelled), trả về khách có chi tiêu lớn hơn mức trung bình của tất cả khách; sắp giảm dần.

Đề có ba yêu cầu ẩn mà người mới hay bỏ qua:

Câu hỏi này không thể trả lời trong một lớp SELECT: bạn cần biết chi tiêu từng khách trước, rồi mới so với trung bình của chính các giá trị đó. CTE (WITH) cho phép đặt tên cho bước một và dùng lại nó hai lần — một lần để lấy dòng, một lần để tính trung bình:

WITH spends AS (
  SELECT c.id, c.name,
         COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS spend
  FROM customers c
  LEFT JOIN orders o
    ON o.customer_id = c.id AND o.status <> 'cancelled'
  LEFT JOIN order_items oi ON oi.order_id = o.id
  GROUP BY c.id, c.name
)
SELECT name, spend
FROM spends
WHERE spend > (SELECT AVG(spend) FROM spends)
ORDER BY spend DESC;

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

Một, LEFT JOIN chứ không JOIN. Đề nói "tất cả khách", nên Hà Bùi (chưa có đơn) và Inh Đỗ (chỉ có đơn huỷ) vẫn phải nằm trong phép tính trung bình với chi tiêu 0. INNER JOIN sẽ loại họ, mẫu số còn 10 thay vì 12 — trung bình tăng từ 2.522.500 lên 3.027.000, và ngưỡng so sánh bị dịch.

Hai, điều kiện o.status <> 'cancelled' nằm trong ON, không trong WHERE. Đây là điểm dễ sai nhất của bài. Với LEFT JOIN, điều kiện đặt trong ON chỉ quyết định có ghép được dòng nào hay không; khách không ghép được vẫn giữ một dòng với cột bảng phải là NULL. Đặt cùng điều kiện trong WHERE thì dòng NULL bị loại ngay sau đó — LEFT JOIN biến thành INNER JOIN mà bạn không hề đổi từ khoá. Cụ thể:

-- SAI: Hà Bùi và Inh Đỗ bị loại khỏi mẫu số
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'cancelled'

Ba, COALESCE(..., 0). Khách chưa mua có SUM là NULL, không phải 0. AVG bỏ qua NULL, nên chỉ cần thiếu COALESCE là mẫu số âm thầm co lại — cùng hậu quả với lỗi thứ hai, nhưng khó thấy hơn vì câu lệnh vẫn chạy trơn tru.

Một chi tiết về thứ tự thực thi: CTE spends được tính một lần, và AVG(spend) trong truy vấn con đọc đúng kết quả đã gom nhóm. Nếu viết SELECT AVG(SUM(...)) trực tiếp ở lớp ngoài sẽ lỗi — không thể lồng hàm tổng hợp. CTE chính là cách "hạ cấp" kết quả tổng hợp thành dữ liệu thường để tính tiếp.

Ví dụ chạy được

Bước 1 — xem chi tiêu từng khách, chưa lọc gì. Chạy phần CTE rồi SELECT * FROM spends ORDER BY spend DESC, name;:

Mười hai dòng — đúng bằng số khách. Hai dòng cuối chính là lý do phải dùng LEFT JOIN và COALESCE: nếu chúng biến mất, phép trung bình đã sai ngay từ bước này.

Bước 2 — kiểm tra mức trung bình:

WITH spends AS (
  SELECT c.id, c.name,
         COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS spend
  FROM customers c
  LEFT JOIN orders o
    ON o.customer_id = c.id AND o.status <> 'cancelled'
  LEFT JOIN order_items oi ON oi.order_id = o.id
  GROUP BY c.id, c.name
)
SELECT COUNT(*) AS so_khach,
       SUM(spend) AS tong,
       AVG(spend) AS trung_binh
FROM spends;

Kết quả: 12 khách, tổng 30.270.000, trung bình 2.522.500. Hai phép kiểm chứng nhanh: tổng phải bằng tổng doanh thu trừ đơn huỷ (31.230.000 − 960.000 = 30.270.000); và 30.270.000 chia 12 đúng bằng 2.522.500.

Bước 3 — lọc và sắp xếp, chính là đáp án:

WITH spends AS (
  SELECT c.id, c.name,
         COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS spend
  FROM customers c
  LEFT JOIN orders o
    ON o.customer_id = c.id AND o.status <> 'cancelled'
  LEFT JOIN order_items oi ON oi.order_id = o.id
  GROUP BY c.id, c.name
)
SELECT name, spend
FROM spends
WHERE spend > (SELECT AVG(spend) FROM spends)
ORDER BY spend DESC;

Năm dòng: Bình Trần 6.750.000; Giang Đặng 5.490.000; Chi Lê 4.270.000; Minh Dương 4.030.000; An Nguyễn 3.530.000. An Nguyễn chỉ hơn trung bình 1.007.500 — sát ngưỡng nhất, nên mọi thay đổi ở mẫu số đều có thể đẩy An ra khỏi danh sách.

Bản sai đặt điều kiện trong WHERE:

WITH spends AS (
  SELECT c.id, c.name,
         COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS spend
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  LEFT JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY c.id, c.name
)
SELECT name, spend
FROM spends
WHERE spend > (SELECT AVG(spend) FROM spends)
ORDER BY spend DESC;

Trên bộ dữ liệu công khai, câu này vẫn trả đúng năm cái tên — điều khiến lỗi khó bị phát hiện. Nhưng spends chỉ còn 10 dòng với trung bình 3.027.000, nên ngưỡng đã khác. Bộ dữ liệu ẩn có thêm khách và đơn, và sự trùng hợp này không còn. Đừng đánh giá câu lệnh bằng danh sách tên; hãy kiểm tra COUNT(*) và AVG của CTE.

Bản sai thiếu COALESCE:

WITH spends AS (
  SELECT c.id, c.name,
         SUM(oi.quantity * oi.unit_price) AS spend
  FROM customers c
  LEFT JOIN orders o
    ON o.customer_id = c.id AND o.status <> 'cancelled'
  LEFT JOIN order_items oi ON oi.order_id = o.id
  GROUP BY c.id, c.name
)
SELECT name, spend
FROM spends
WHERE spend > (SELECT AVG(spend) FROM spends)
ORDER BY spend DESC;

AVG bỏ qua hai giá trị NULL, mẫu số còn 10, trung bình thành 3.027.000. Lại một lần kết quả trông đúng trên dữ liệu công khai nhưng logic sai.

Vì sao phải gom theo c.id, c.name? Hai khách trùng tên sẽ bị gộp nếu chỉ gom theo tên, và tổng chi tiêu của họ cộng vào nhau — một khách "ảo" có thể vọt lên đầu bảng. GROUP BY c.id, c.name giữ đúng một dòng cho mỗi khách.

Thực hành

1. Nộp đáp án cho bài Khách chi tiêu trên trung bình (customers-above-average-spend). Giữ đúng tên cột name và spend, và nhớ ORDER BY spend DESC.
2. Chạy CTE spends riêng và đếm số dòng. Phải là 12, không phải 10 — đây là phép kiểm tra nhanh nhất cho lỗi ON/WHERE.
3. Đổi > thành >= và giải thích vì sao kết quả không đổi trên dữ liệu này, rồi thử với một ngưỡng khác (ví dụ spend >= 2.150.000) để thấy dấu bằng có tác dụng khi có khách đúng bằng ngưỡng.
4. Viết biến thể tính trung bình chỉ trên khách đã mua (WHERE spend > 0 trong truy vấn con). So sánh ngưỡng mới 3.027.000 với ngưỡng đúng 2.522.500 và giải thích khi nào cách tính đó hợp lý về nghiệp vụ.

Lỗi thường gặp

Nhóm lỗi đầu tiên nguy hiểm vì câu lệnh không báo lỗi. Cách phòng duy nhất là kiểm chứng bằng số: CTE phải có đúng số khách, tổng phải khớp doanh thu, trung bình phải bằng tổng chia số khách.

Tóm tắt

• CTE spends biến kết quả gom nhóm thành dữ liệu dùng lại được cho cả SELECT lẫn truy vấn con.
• Điều kiện loại đơn huỷ đặt trong ON; đặt trong WHERE sẽ làm mất khách 0 đồng.
• COALESCE(..., 0) giữ khách chưa mua trong mẫu số.
• Đề nói "tất cả khách" → trung bình trên 12 khách = 2.522.500.
• Kiểm chứng bằng COUNT(*), SUM(spend) và AVG(spend) trước khi tin vào danh sách kết quả.

Bạn vừa hoàn thành chương cuối của khóa SQL cơ bản. Bạn đã đi từ SELECT đầu tiên đến lọc, sắp xếp, gom nhóm, nối bảng, phân nhánh bằng CASE và cuối cùng là tổng hợp nhiều bước bằng CTE. Bước tiếp theo là khóa SQL phân tích dữ liệu, nơi những mảnh này được ghép thành báo cáo thật: window functions, cohort và xếp hạng.