Bài tập tổng hợp RFM

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

• Ghép ba chỉ số RFM trong một truy vấn có JOIN, GROUP BY và hàm tổng hợp.
• Vì sao COUNT(DISTINCT o.id) là bắt buộc khi đã nối order_items.
• Đặt bộ lọc status đúng chỗ.
• Kiểm chứng kết quả bằng tổng trước khi nộp.
• Cách đọc bảng RFM để tìm khách hàng quan trọng.

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

Đề bài RFM — Gộp ba chỉ số:

Trả về name, recency_days, frequency, monetary cho từng khách (bỏ đơn cancelled); sắp theo monetary giảm dần.

Ba chỉ số:

Mốc 2026-01-01 là ngày chốt dữ liệu, không phải CURRENT_DATE. Đây là điểm quan trọng: bài chấm so sánh với đáp án cố định, nên dùng CURRENT_DATE sẽ ra số khác mỗi ngày và không bao giờ khớp.

Đáp án:

SELECT c.name,
       DATE '2026-01-01' - MAX(o.order_date) AS recency_days,
       COUNT(DISTINCT o.id) AS frequency,
       SUM(oi.quantity * oi.unit_price) AS monetary
FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY monetary DESC;

Kết quả 10 dòng:

Bốn quyết định trong câu lệnh

Một, lọc status trong ON. Điều kiện o.status <> 'cancelled' nằm ở mệnh đề ON của JOIN orders. Với INNER JOIN, đặt ở ON hay WHERE cho cùng kết quả. Nhưng đặt ở ON là thói quen đúng: nếu sau này đổi sang LEFT JOIN để giữ cả khách chưa mua, điều kiện ở WHERE sẽ biến LEFT JOIN thành INNER JOIN và làm mất khách. Giữ điều kiện gắn với bảng được nối.

Hai, COUNT(DISTINCT o.id). Đây là điểm dễ sai nhất. Luồng dữ liệu:

customers 1 ─── orders N ─── order_items M

JOIN order_items nhân mỗi đơn thành nhiều dòng — mỗi sản phẩm một dòng. Với Bình Trần, 3 đơn có thể thành 6 dòng hàng. COUNT(*) sẽ trả 6 thay vì 3. COUNT(DISTINCT o.id) đếm số đơn duy nhất.

Hãy kiểm chứng bằng tổng:

SELECT SUM(frequency) AS tong_don
FROM ( ... câu đáp án ... ) t;

Kết quả phải bằng 15 — đúng số đơn không bị huỷ. Nếu ra 29 (bằng số dòng order_items), bạn đã dùng COUNT(*).

Ba, GROUP BY c.id, c.name. Gom theo khóa chính của khách. PostgreSQL cho phép chỉ gom theo c.id vì name phụ thuộc hàm vào khóa chính, nhưng ghi cả hai giúp câu lệnh rõ nghĩa và chạy được trên hệ khác. Không gom theo name một mình: hai khách trùng tên sẽ bị gộp thành một.

Bốn, ORDER BY monetary DESC. Bài đánh dấu orderSensitive, nên thứ tự dòng được so sánh. monetary ở đây không trùng giá trị nào trên bộ công khai, nên DESC là đủ xác định. Trên dữ liệu thật, hãy thêm khóa phụ (ORDER BY monetary DESC, name) để kết quả ổn định — nhưng với bài chấm này, làm theo đúng đề.

Kiểm chứng ba con số

Trước khi nộp, chạy truy vấn tổng hợp:

SELECT COUNT(DISTINCT c.id) AS khach,
       COUNT(DISTINCT o.id) AS don,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
JOIN order_items oi ON oi.order_id = o.id;

Kết quả kỳ vọng: 10 | 15 | 30270000.

Ba con số này xác nhận:

• 10 khách có ít nhất một đơn hợp lệ (trong 12 khách, 2 người chưa từng mua).
• 15 đơn không bị huỷ (tổng 16 đơn, 1 cancelled).
• 30.270.000 tổng doanh thu — bằng đúng tổng cột monetary của bảng kết quả.

Nếu tổng monetary trong kết quả khác 30.270.000, hãy kiểm tra theo thứ tự: bộ lọc trạng thái, điều kiện nối, phép nhân quantity * unit_price. Đừng bỏ qua đơn pending: đề chỉ loại cancelled, nên đơn chờ xử lý vẫn được tính.

Đọc bảng RFM

Nhìn bảng kết quả, ba nhóm nổi lên:

• Bình Trần chi nhiều nhất (6,75 triệu), mua 3 lần, nhưng 181 ngày chưa quay lại. Đây là khách giá trị cao đang có dấu hiệu nguội — ưu tiên chăm sóc.
• Chi Lê mới mua cách đây 51 ngày, chi 4,27 triệu, 2 đơn. Khách đang hoạt động tốt, ứng viên cho upsell.
• An Nguyễn chi 3,53 triệu nhưng đã 315 ngày không mua. Giá trị lịch sử cao, rủi ro rời bỏ cao.

Một bảng RFM chỉ có giá trị khi được phân nhóm. Bài rfm-scores dùng NTILE(5) để chấm điểm; bài này dừng ở việc tính đúng ba chỉ số thô — nền tảng cho mọi bước sau.

Thực hành

1. Nộp đáp án cho bài RFM — Gộp ba chỉ số (rfm-combined).
2. Đổi COUNT(DISTINCT o.id) thành COUNT(*) và xem cột frequency thay đổi thế nào.
3. Bỏ điều kiện status <> 'cancelled' và tính lại tổng doanh thu — chênh bao nhiêu?
4. Thêm cột so_mon đếm số dòng hàng và so sánh với frequency.
5. Sắp theo recency_days tăng dần và xác định ba khách "nóng" nhất.

Lỗi thường gặp

Tóm tắt

• RFM = recency, frequency, monetary; mốc chốt cố định 2026-01-01.
• COUNT(DISTINCT o.id) bắt buộc vì order_items nhân dòng.
• Lọc cancelled, gom theo c.id, sắp theo monetary DESC.
• Kiểm chứng bằng 10 | 15 | 30270000 trước khi nộp.
• Bảng thô là nền tảng; bước tiếp theo là chấm điểm và phân nhóm.