CASE WHEN đưa quy tắc kinh doanh vào SQL, từ phân loại khách hàng đến chuẩn hóa trạng thái. Bài viết xây mô hình phân nhóm khách hàng dựa trên lần mua gần nhất, số đơn và tổng chi tiêu, đồng thời chỉ ra vì sao thứ tự điều kiện quyết định kết quả.

Mục lục

1. Bài toán thực tế
2. Sản phẩm hoàn thành
3. Kiến thức cần dùng
4. Chuẩn bị môi trường
5. Dữ liệu thực hành
6. Xây dựng từng bước
7. Chương trình hoàn chỉnh
8. Kiểm tra kết quả
9. Lỗi thường gặp
10. Đưa vào thực tế
11. Bài tập thực hành
12. Project mở rộng
13. Tổng kết
14. Tài liệu tham khảo

1. Bài toán thực tế

Marketing muốn chia khách hàng thành mới, đang hoạt động, có nguy cơ rời bỏ và giá trị cao. Bảng customers không chứa sẵn nhãn; dữ liệu cần được suy ra từ lịch sử đơn hàng completed.

Nếu điều kiện “đang hoạt động” được đặt trước “giá trị cao”, một khách vừa mua gần đây và chi tiêu lớn có thể không bao giờ vào nhóm ưu tiên. Quy tắc phải có thứ tự và mỗi khách chỉ nhận một nhãn chính.

2. Sản phẩm hoàn thành

Sản phẩm là view customer_segments với một dòng mỗi customer_id. View có last_order_date, completed_orders, lifetime_value, days_since_last_order và segment.

Bảng cấu hình ngưỡng được giới thiệu ở phần production để tránh chôn các con số trong nhiều truy vấn.

• Viết CASE searched và CASE simple.
• Hiểu CASE trả kết quả ở nhánh đúng đầu tiên.
• Kết hợp FILTER, COALESCE và phép tính ngày.
• Xử lý khách chưa mua mà không mất khỏi kết quả.
• Kiểm tra nhóm loại trừ nhau và bao phủ toàn bộ khách.

3. Kiến thức cần dùng

CASE WHEN được đánh giá từ trên xuống và dừng ở điều kiện đúng đầu tiên. Điều kiện hẹp hoặc ưu tiên cao phải đặt trước điều kiện rộng. ELSE nên được viết rõ để tránh NULL khó giải thích.

COALESCE lấy giá trị không NULL đầu tiên. Trong bài, khách không có đơn completed nhận completed_orders và lifetime_value bằng 0; last_order_date vẫn NULL vì chưa có ngày hợp lệ.

Aggregate FILTER cho phép tính nhiều chỉ số với điều kiện riêng mà không cần lặp nhiều subquery. LEFT JOIN giữ khách chưa có đơn.

4. Chuẩn bị môi trường

Chốt ngày đánh giá để kết quả tái lập. Không dùng CURRENT_DATE trong ví dụ nghiệm thu vì chạy vào ngày khác sẽ đổi nhãn. Biến as_of_date được đặt trong CTE parameters.

Chỉ đơn completed được tính vào giá trị khách hàng. Đơn cancelled và refunded cần quy tắc riêng tùy doanh nghiệp.

WITH parameters AS (
  SELECT DATE '2026-09-30' AS as_of_date
)
SELECT * FROM parameters;

5. Dữ liệu thực hành

customer_metrics có grain một dòng mỗi khách. last_order_date là ngày completed gần nhất; completed_orders đếm distinct order_id; lifetime_value cộng total_amount completed.

Các ngưỡng minh họa: giá trị cao từ 50 triệu, khách mới có đúng một đơn trong 30 ngày, đang hoạt động mua trong 30 ngày, nguy cơ rời bỏ từ 90 ngày không mua.

CREATE TABLE segment_rules (
  rule_version varchar(20) PRIMARY KEY,
  high_value_threshold numeric(14,2) NOT NULL,
  active_days integer NOT NULL,
  churn_risk_days integer NOT NULL,
  effective_from date NOT NULL
);

6. Xây dựng từng bước

Bước 1: Tổng hợp chỉ số khách hàng

LEFT JOIN từ customers để không làm mất khách chưa mua. FILTER giới hạn đơn completed cho từng aggregate.

SELECT c.customer_id, c.customer_name,
       MAX(o.order_date::date) FILTER (WHERE o.status='completed') AS last_order_date,
       COUNT(DISTINCT o.order_id) FILTER (WHERE o.status='completed') AS completed_orders,
       COALESCE(SUM(o.total_amount) FILTER (WHERE o.status='completed'),0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON o.customer_id=c.customer_id
GROUP BY c.customer_id, c.customer_name;

Kết quả mong đợi: Một dòng mỗi khách, kể cả khách chưa mua.

Bước 2: Tính số ngày kể từ lần mua cuối

Phép trừ hai giá trị date trả số ngày. Khách chưa mua giữ NULL để CASE nhận diện riêng.

WITH m AS (
  SELECT c.customer_id, MAX(o.order_date::date) FILTER (WHERE o.status='completed') last_order_date
  FROM customers c LEFT JOIN orders o ON o.customer_id=c.customer_id
  GROUP BY c.customer_id
)
SELECT customer_id, last_order_date,
       DATE '2026-09-30' - last_order_date AS days_since_last_order
FROM m;

Kết quả mong đợi: Số ngày không hoạt động tại ngày 30/9/2026.

Bước 3: Viết CASE theo thứ tự ưu tiên

Chưa mua được kiểm tra trước vì ngày NULL. Giá trị cao đứng trước active để khách VIP không bị gắn nhãn thường.

CASE
  WHEN completed_orders = 0 THEN 'Chưa mua hàng'
  WHEN lifetime_value >= 50000000 THEN 'Giá trị cao'
  WHEN completed_orders = 1 AND days_since_last_order <= 30 THEN 'Khách hàng mới'
  WHEN days_since_last_order <= 30 THEN 'Đang hoạt động'
  WHEN days_since_last_order >= 90 THEN 'Có nguy cơ rời bỏ'
  ELSE 'Cần nuôi dưỡng'
END AS segment

Kết quả mong đợi: Mỗi khách nhận đúng một segment chính.

Bước 4: Dùng CASE cho cột hiển thị

CASE cũng phù hợp để chuyển trạng thái kỹ thuật thành nhãn báo cáo, nhưng mapping dùng ở nhiều nơi nên đưa vào bảng tham chiếu.

SELECT order_id, status,
  CASE status
    WHEN 'pending' THEN 'Đang xử lý'
    WHEN 'completed' THEN 'Hoàn tất'
    WHEN 'cancelled' THEN 'Đã hủy'
    ELSE 'Khác'
  END AS status_label
FROM orders;

Kết quả mong đợi: Trạng thái có nhãn tiếng Việt dễ đọc.

Bước 5: Đếm phân bố nhóm

Sau khi phân nhóm, kiểm tra số khách theo segment và tỷ trọng. Một nhóm bằng 0 có thể do thứ tự CASE sai.

SELECT segment, COUNT(*) AS customers,
       ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER (),2) AS share_pct
FROM customer_segments
GROUP BY segment
ORDER BY customers DESC;

Kết quả mong đợi: Phân bố khách hàng cộng lại bằng 100%.

Viết bảng quyết định trước CASE

Tạo bảng gồm điều kiện, ưu tiên, nhãn và ví dụ. Mỗi hàng là một nhánh CASE. Bảng quyết định giúp marketing xác nhận quy tắc mà không cần đọc SQL và làm lộ khoảng trống giữa 30 và 90 ngày.

Thử mọi tổ hợp quan trọng: khách chưa mua nhưng profile mới, khách VIP không mua 120 ngày, khách có một đơn hôm qua và khách mua đúng ngưỡng 50 triệu. Ví dụ phản biện thường phát hiện ưu tiên chưa phù hợp.

Tách phân loại khỏi hành động

Segment “có nguy cơ rời bỏ” là kết quả phân tích; gửi voucher là hành động. Nếu gắn hành động trong CASE, thay đổi chiến dịch sẽ buộc sửa lớp dữ liệu. Giữ segment ổn định và ánh xạ sang campaign ở bảng riêng.

Một khách có thể thỏa nhiều tín hiệu. Nhãn chính hữu ích cho báo cáo tổng quan, nhưng hệ thống kích hoạt nên giữ thêm các cờ is_high_value và is_churn_risk để không mất thông tin.

Theo dõi thay đổi quy tắc

Khi ngưỡng VIP từ 50 triệu đổi thành 70 triệu, dashboard lịch sử có được tính lại không? Snapshot theo rule cũ và recompute theo rule mới trả lời hai câu hỏi khác nhau. Cần ghi rule_version và evaluated_at.

Đừng cập nhật âm thầm toàn bộ lịch sử. Tài liệu thay đổi phải nêu số khách chuyển nhóm và ảnh hưởng đến chiến dịch đang chạy.

Tạo dữ liệu kiểm thử cho từng nhánh

Một CASE sáu nhánh cần ít nhất sáu khách đại diện và thêm trường hợp đúng tại mỗi ngưỡng. Dữ liệu kiểm thử nên nhỏ đến mức người đọc tính tay được last_order_date, completed_orders và lifetime_value.

Dùng VALUES để tạo test table ngay trong truy vấn, không phụ thuộc database hiện tại. Cách này phù hợp unit test logic phân nhóm.

WITH sample(customer_id,completed_orders,lifetime_value,days_since_last_order) AS (VALUES
 (1,0,0::numeric,NULL::integer),
 (2,5,60000000,10),
 (3,1,500000,5),
 (4,4,8000000,20),
 (5,3,9000000,100),
 (6,2,3000000,60)
)
SELECT *,CASE
 WHEN completed_orders=0 THEN 'Chưa mua hàng'
 WHEN lifetime_value>=50000000 THEN 'Giá trị cao'
 WHEN completed_orders=1 AND days_since_last_order<=30 THEN 'Khách hàng mới'
 WHEN days_since_last_order<=30 THEN 'Đang hoạt động'
 WHEN days_since_last_order>=90 THEN 'Có nguy cơ rời bỏ'
 ELSE 'Cần nuôi dưỡng' END segment
FROM sample;

CASE trong SUM và COUNT

CASE có thể tạo aggregate có điều kiện trên các database chưa dùng FILTER hoặc khi cần biến đổi giá trị. SUM(CASE WHEN condition THEN amount ELSE 0 END) cộng tiền đúng nhóm; COUNT(CASE WHEN condition THEN 1 END) dựa vào việc COUNT bỏ NULL.

Không dùng ELSE 0 bên trong COUNT vì 0 vẫn là giá trị được đếm. Đây là lỗi nhỏ nhưng làm chỉ số bằng tổng số dòng.

SELECT
 SUM(CASE WHEN status='completed' THEN total_amount ELSE 0 END) completed_revenue,
 COUNT(CASE WHEN status='cancelled' THEN 1 END) cancelled_orders,
 COUNT(CASE WHEN status='cancelled' THEN 1 ELSE 0 END) wrong_cancelled_count
FROM orders;

Phân nhóm có thứ tự hay độc lập

Segment chính là mutually exclusive: một khách chỉ có một nhãn vì CASE dừng ở nhánh đầu. Tín hiệu độc lập cần nhiều cột boolean. Một khách VIP đã 100 ngày không mua nên đồng thời high value và churn risk để chiến dịch giữ chân không bỏ sót.

Thiết kế output có primary_segment cho báo cáo và các cờ cho activation. Điều này giữ bảng dễ tổng hợp mà không làm mất đặc điểm chồng lấn.

SELECT customer_id,
 CASE WHEN lifetime_value>=50000000 THEN 'Giá trị cao'
      WHEN days_since_last_order>=90 THEN 'Có nguy cơ rời bỏ'
      ELSE 'Thông thường' END primary_segment,
 lifetime_value>=50000000 AS is_high_value,
 days_since_last_order>=90 AS is_churn_risk
FROM customer_metrics;

Kiểm tra độ ổn định khi đổi ngày đánh giá

Chạy view với as_of_date cuối ba tháng liên tiếp và đếm khách chuyển nhóm. Nếu phần lớn khách nhảy bất thường, có thể dữ liệu ngày hoặc rule sai. Phân nhóm theo thời gian là state machine đơn giản và cần quan sát transition.

Snapshot giúp trả lời “khách thuộc nhóm nào tại thời điểm gửi chiến dịch”, trong khi view hiện tại trả lời “nếu đánh giá hôm nay”. Cả hai đều cần nhưng không thay thế nhau.

SELECT previous.segment previous_segment,current.segment current_segment,COUNT(*) customers
FROM customer_segment_snapshot previous
JOIN customer_segment_snapshot current USING(customer_id)
WHERE previous.snapshot_date=DATE '2026-08-31'
  AND current.snapshot_date=DATE '2026-09-30'
GROUP BY previous.segment,current.segment
ORDER BY customers DESC;

7. Chương trình hoàn chỉnh

View dùng ba CTE: parameters cố định ngày đánh giá, metrics tổng hợp hành vi và classified áp dụng CASE. Cách tách này cho phép kiểm tra số liệu trước khi gắn nhãn.

Không nhúng thông điệp marketing hoặc hành động liên hệ vào cùng CASE. Segment là dữ liệu; chiến dịch sử dụng segment thuộc lớp ứng dụng.

CREATE OR REPLACE VIEW customer_segments AS
WITH parameters AS (SELECT DATE '2026-09-30' AS as_of_date),
metrics AS (
 SELECT c.customer_id,c.customer_name,
   MAX(o.order_date::date) FILTER (WHERE o.status='completed') last_order_date,
   COUNT(DISTINCT o.order_id) FILTER (WHERE o.status='completed') completed_orders,
   COALESCE(SUM(o.total_amount) FILTER (WHERE o.status='completed'),0) lifetime_value
 FROM customers c LEFT JOIN orders o ON o.customer_id=c.customer_id
 GROUP BY c.customer_id,c.customer_name
)
SELECT m.*, p.as_of_date-m.last_order_date AS days_since_last_order,
 CASE WHEN completed_orders=0 THEN 'Chưa mua hàng'
      WHEN lifetime_value>=50000000 THEN 'Giá trị cao'
      WHEN completed_orders=1 AND p.as_of_date-m.last_order_date<=30 THEN 'Khách hàng mới'
      WHEN p.as_of_date-m.last_order_date<=30 THEN 'Đang hoạt động'
      WHEN p.as_of_date-m.last_order_date>=90 THEN 'Có nguy cơ rời bỏ'
      ELSE 'Cần nuôi dưỡng' END AS segment
FROM metrics m CROSS JOIN parameters p;

8. Kiểm tra kết quả

Số dòng view phải bằng số khách và customer_id phải duy nhất. Tổng khách theo segment cũng phải bằng số dòng view.

Tạo các khách đúng tại ngưỡng 30, 90 ngày và 50 triệu để xác nhận toán tử <=, >=. Kiểm tra một khách vừa giá trị cao vừa active nhận nhóm ưu tiên nào.

SELECT COUNT(*) total_rows, COUNT(DISTINCT customer_id) unique_customers
FROM customer_segments;

SELECT segment, COUNT(*) FROM customer_segments GROUP BY segment;

SELECT * FROM customer_segments
WHERE segment IS NULL;

9. Lỗi thường gặp

• Điều kiện rộng đặt trước: Nhánh sau không bao giờ được chạy.
• Không có ELSE: Các trường hợp chưa nghĩ tới trở thành NULL.
• CURRENT_DATE làm kết quả thay đổi: Dùng as_of_date khi cần tái lập.
• INNER JOIN làm mất khách chưa mua: Bắt đầu bằng customers và LEFT JOIN.
• COALESCE ngày mua thành ngày giả: Giữ NULL để phân biệt chưa từng mua.

10. Đưa vào thực tế

Ngưỡng segment thay đổi theo chiến lược. Đưa chúng vào segment_rules có effective_from và rule_version; output lưu rule_version để giải thích nhãn lịch sử.

Một khách có thể cần nhiều nhãn song song như VIP, churn risk và preferred category. Khi đó không ép tất cả vào một CASE loại trừ nhau; xây các cờ boolean hoặc bảng customer_tags.

11. Bài tập thực hành

1. Đổi ưu tiên để churn risk đứng trước high value và so sánh.
2. Thêm nhóm khách quay lại sau 180 ngày.
3. Tạo ba cờ is_high_value, is_active, is_churn_risk.
4. Đưa ngưỡng vào segment_rules và CROSS JOIN rule hiệu lực.
5. Viết unit test cho sáu trường hợp biên.

12. Project mở rộng

Xây bảng lịch sử customer_segment_snapshot theo ngày để theo dõi khách chuyển nhóm. Khóa gồm snapshot_date và customer_id; không cập nhật ngược bản ghi cũ khi rule thay đổi.

13. Tổng kết

CASE WHEN là công cụ biểu diễn quyết định, vì vậy chất lượng phụ thuộc vào quy tắc và thứ tự chứ không chỉ cú pháp. Một CASE tốt phải bao phủ đầy đủ, không mơ hồ và kiểm thử được ở ngưỡng.

Bài tiếp theo dùng CTE để chia truy vấn nhiều bước thành các lớp có tên, giúp logic như customer segmentation dễ đọc và bảo trì hơn.

14. Tài liệu tham khảo

• Conditional Expressions
• Aggregate FILTER
• Date Functions

TechData.AI - Leading the Future.
Tham khảo các khoá học theo link: https://techdata.ai/techdata-ai-course/
Hoàng Minh.