Mở đầu — vì sao bài này quan trọng
Hãy tưởng tượng một buổi sáng thứ Hai, bạn mở dashboard doanh thu và thấy con số hôm qua là 1,2 tỷ đồng. Nhiều hay ít? Bình thường hay bất thường? Nếu bạn chỉ nhìn một con số đơn lẻ, bạn không thể trả lời được. Có thể 1,2 tỷ là một ngày tệ hại (nếu trung bình bạn làm 2 tỷ/ngày), cũng có thể là một ngày bùng nổ (nếu trung bình chỉ 800 triệu). Anomaly detection — phát hiện bất thường — chính là kỹ năng giúp BA biến những con số trần trụi thành câu hỏi đúng: "Tại sao hôm qua lại lệch khỏi bình thường đến vậy?"
Với BA, đây không phải bài tập học thuật. Bất thường thường là tín hiệu sớm của vấn đề kinh doanh: doanh thu rớt vì một bước thanh toán bị lỗi, traffic tăng đột biến vì bot quét hàng, tỷ lệ hoàn tiền (refund) leo thang vì một lô sản phẩm lỗi, hay một cụm đơn hàng gian lận (fraud) đang được tạo bằng thẻ trộm. Người phát hiện ra những điều này sớm vài giờ có thể cứu công ty hàng trăm triệu. Và phần lớn thời gian, BA không cần một mô hình machine learning phức tạp — chỉ cần vài câu SQL viết khéo là đã bắt được 80% các bất thường quan trọng.
Trong bài này, chúng ta tập trung hoàn toàn vào cách phát hiện bất thường bằng SQL thuần: từ ngưỡng cố định đơn giản, đến phương pháp thống kê Z-score, rồi đến IQR và so sánh theo chu kỳ. Đây là những kỹ thuật bạn dùng được ngay trên Metabase, Looker hay bất kỳ công cụ query nào, không cần Python.
Khái niệm cốt lõi
Bất thường (anomaly hay outlier) là một điểm dữ liệu lệch đáng kể so với phần còn lại. Nhưng "đáng kể" là bao nhiêu? Đó chính là phần khó. Một con số chỉ bất thường khi đặt trong bối cảnh (context) — so với lịch sử của chính nó, so với cùng kỳ, hoặc so với các nhóm tương tự.
Ba loại bất thường BA hay gặp
Point anomaly — một điểm đơn lẻ vượt ngưỡng: ví dụ một đơn hàng trị giá 500 triệu trong khi đơn trung bình chỉ 500 nghìn. Contextual anomaly — bình thường trong ngữ cảnh này nhưng bất thường trong ngữ cảnh khác: 100 đơn lúc 3 giờ sáng là bất thường, nhưng 100 đơn lúc 8 giờ tối là bình thường. Collective anomaly — từng điểm có vẻ ổn nhưng cả cụm thì sai: 50 đơn nhỏ liên tiếp từ cùng một thiết bị trong 2 phút.
Phương pháp 1 — Ngưỡng cố định (static threshold)
Đơn giản nhất: định nghĩa trước một giới hạn. "Báo động nếu refund rate trong ngày vượt 5%." Dễ hiểu, dễ giải thích cho sếp, nhưng cứng nhắc — ngưỡng tốt cho mùa thấp điểm sẽ sai bét vào mùa cao điểm. Dùng khi bạn có một quy tắc nghiệp vụ rõ ràng.
Phương pháp 2 — Z-score (độ lệch chuẩn)
Đây là "vũ khí chủ lực" của BA. Ý tưởng: tính giá trị trung bình (AVG) và độ lệch chuẩn (STDDEV) của chuỗi dữ liệu, rồi đo xem mỗi điểm cách trung bình bao nhiêu lần độ lệch chuẩn:
Z = (giá_trị - trung_bình) / độ_lệch_chuẩn
Theo quy tắc thống kê, với dữ liệu phân phối chuẩn, khoảng 99,7% giá trị nằm trong khoảng Z từ -3 đến +3. Vậy nên |Z| > 3 (hoặc thận trọng hơn là > 2) thường được coi là bất thường. Z-score tự điều chỉnh theo dữ liệu, không cần bạn đoán ngưỡng tuyệt đối.
WITH daily AS (
SELECT
order_date,
SUM(amount) AS revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY order_date
),
stats AS (
SELECT
AVG(revenue) AS mean_rev,
STDDEV(revenue) AS std_rev
FROM daily
)
SELECT
d.order_date,
d.revenue,
ROUND((d.revenue - s.mean_rev) / NULLIF(s.std_rev, 0), 2) AS z_score
FROM daily d
CROSS JOIN stats s
WHERE ABS((d.revenue - s.mean_rev) / NULLIF(s.std_rev, 0)) > 2
ORDER BY ABS((d.revenue - s.mean_rev) / NULLIF(s.std_rev, 0)) DESC;
Chú ý chi tiết quan trọng: NULLIF(s.std_rev, 0) để tránh lỗi chia cho 0 khi mọi giá trị giống hệt nhau. CROSS JOIN stats chỉ nối một dòng thống kê vào mọi dòng ngày — một mẹo gọn để dùng số tổng hợp trong câu so sánh từng dòng.
Phương pháp 3 — IQR (Interquartile Range)
Z-score có nhược điểm: chính các outlier lại làm phồng STDDEV, khiến ngưỡng bị kéo dãn và bỏ sót bất thường. IQR dùng trung vị và phân vị nên "lì đòn" với outlier hơn. Ta tính phân vị 25% (Q1) và 75% (Q3), khoảng giữa là IQR = Q3 − Q1, rồi coi mọi điểm nằm ngoài [Q1 − 1.5×IQR, Q3 + 1.5×IQR] là bất thường.
WITH daily AS (
SELECT order_date, SUM(amount) AS revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY order_date
),
quartiles AS (
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) AS q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue) AS q3
FROM daily
)
SELECT
d.order_date, d.revenue
FROM daily d
CROSS JOIN quartiles q
WHERE d.revenue < q.q1 - 1.5 * (q.q3 - q.q1)
OR d.revenue > q.q3 + 1.5 * (q.q3 - q.q1);
(PERCENTILE_CONT có sẵn trong PostgreSQL; MySQL 8 cần dùng cách khác hoặc xấp xỉ bằng window function.)
Phương pháp 4 — So sánh theo chu kỳ (rolling / week-over-week)
Doanh thu có tính mùa vụ: cuối tuần khác ngày thường, lễ Tết khác ngày bình thường. So với trung bình toàn cục là sai. Thay vào đó, ta so từng ngày với trung bình trượt của vài tuần gần nhất, hoặc với chính ngày đó tuần trước (LAG 7 ngày). Đây là cách thực tế nhất cho dữ liệu kinh doanh thật.
Tình huống thực tế
Ví dụ 1 — Tiki: refund rate tăng âm thầm
Một BA tại sàn thương mại điện tử (mô phỏng kiểu Tiki) theo dõi tỷ lệ hoàn hàng theo ngành hàng. Refund rate toàn sàn dao động quanh 3%, nên không ai báo động. Nhưng khi BA tách theo category và chạy Z-score trên từng ngành, ngành "Đồ điện tử gia dụng" bật lên Z = 3,4: refund rate nhảy từ 2,8% lên 9,1% trong 4 ngày.
WITH cat_daily AS (
SELECT category, order_date,
COUNT() FILTER (WHERE is_refunded) 1.0 / COUNT(*) AS refund_rate
FROM orders
GROUP BY category, order_date
),
cat_stats AS (
SELECT category, AVG(refund_rate) m, STDDEV(refund_rate) s
FROM cat_daily GROUP BY category
)
SELECT c.category, c.order_date, ROUND(c.refund_rate, 3) AS rate,
ROUND((c.refund_rate - cs.m) / NULLIF(cs.s, 0), 2) AS z
FROM cat_daily c
JOIN cat_stats cs USING (category)
WHERE ABS((c.refund_rate - cs.m) / NULLIF(cs.s, 0)) > 2.5
ORDER BY z DESC;
Điều tra ra: một lô nồi chiên không dầu bị lỗi mạch, khách trả hàng hàng loạt. Bài học: bất thường thường bị che giấu khi gộp dữ liệu. Hãy GROUP BY theo từng phân khúc (category, region, kênh) rồi mới phát hiện — bất thường ở một nhóm nhỏ dễ bị "pha loãng" trong tổng thể.
Ví dụ 2 — Be/Grab: traffic spike lúc 3 giờ sáng
Một BA ở nền tảng gọi xe (mô phỏng Be) thấy số lượt tạo chuyến (ride requests) lúc 2–4 giờ sáng tại quận Bình Thạnh bỗng tăng gấp 8 lần bình thường. Đây là contextual anomaly: 800 lượt/giờ là bình thường lúc cao điểm chiều, nhưng cực kỳ bất thường lúc rạng sáng. BA dùng so sánh theo khung giờ:
WITH hourly AS (
SELECT district, EXTRACT(HOUR FROM created_at) AS hr,
DATE(created_at) AS d, COUNT(*) AS requests
FROM ride_requests
GROUP BY district, hr, DATE(created_at)
),
baseline AS (
SELECT district, hr, AVG(requests) m, STDDEV(requests) s
FROM hourly
WHERE d < CURRENT_DATE - 1 -- baseline từ quá khứ
GROUP BY district, hr
)
SELECT h.district, h.d, h.hr, h.requests,
ROUND((h.requests - b.m) / NULLIF(b.s, 0), 1) AS z
FROM hourly h
JOIN baseline b USING (district, hr)
WHERE h.d = CURRENT_DATE - 1
AND (h.requests - b.m) / NULLIF(b.s, 0) > 3;
Hóa ra là một nhóm tài khoản tạo chuyến giả để cày khuyến mãi tài xế. Bài học: baseline phải so cùng ngữ cảnh — cùng khung giờ, cùng khu vực. Đừng so 3 giờ sáng với trung bình cả ngày, nếu không bạn sẽ báo động sai liên tục (false positive) và đội vận hành sẽ phớt lờ cảnh báo của bạn.
Ví dụ 3 — MoMo: cụm giao dịch nghi gian lận
Một BA ở ví điện tử (mô phỏng MoMo) cần phát hiện hành vi nạp–rút bất thường. Thay vì nhìn từng giao dịch, họ phát hiện collective anomaly: số giao dịch của một user trong cửa sổ trượt 10 phút vượt xa mức bình thường của chính user đó. Dùng COUNT cộng với ngưỡng động theo từng người giúp tránh báo động nhầm những user vốn giao dịch nhiều.
WITH user_stats AS (
SELECT user_id, AVG(daily_txn) m, STDDEV(daily_txn) s
FROM (SELECT user_id, DATE(created_at) d, COUNT(*) daily_txn
FROM transactions GROUP BY user_id, DATE(created_at)) t
GROUP BY user_id
),
today AS (
SELECT user_id, COUNT(*) AS txn_today
FROM transactions
WHERE DATE(created_at) = CURRENT_DATE
GROUP BY user_id
)
SELECT t.user_id, t.txn_today, ROUND(us.m, 1) AS usual,
ROUND((t.txn_today - us.m) / NULLIF(us.s, 0), 1) AS z
FROM today t
JOIN user_stats us USING (user_id)
WHERE us.s > 0
AND (t.txn_today - us.m) / NULLIF(us.s, 0) > 4
ORDER BY z DESC;
Bài học: ngưỡng động "theo từng đối tượng" (per-user, per-merchant) mạnh hơn ngưỡng chung rất nhiều cho bài toán fraud. Một user vốn giao dịch 50 lần/ngày mà hôm nay 60 lần thì bình thường; một user vốn 2 lần/ngày mà hôm nay 40 lần thì rất đáng ngờ — dù 40 < 60.
Hướng dẫn từng bước
Đây là quy trình bạn nên theo mỗi khi xây một query phát hiện bất thường:
Bước 1 — Xác định metric và đơn vị thời gian. Bạn đang theo dõi cái gì? (doanh thu, số đơn, refund rate, số giao dịch). Theo ngày, giờ, hay tuần? Viết câu GROUP BY để tổng hợp thô trước, dùng một CTE tên daily hoặc hourly.
Bước 2 — Chọn chiều phân tách (dimension). Phân tích toàn cục dễ bỏ sót. Tự hỏi: bất thường có thể núp ở đâu? Theo category, region, channel, hay user? Thêm chúng vào GROUP BY.
Bước 3 — Tính baseline. Quyết định "bình thường" là gì: trung bình + độ lệch chuẩn (Z-score), phân vị (IQR), hay cùng kỳ tuần trước. Quan trọng: baseline nên tính từ dữ liệu quá khứ, không bao gồm ngày đang kiểm tra, để tránh việc chính bất thường làm sai lệch baseline.
Bước 4 — Tính điểm lệch và đặt ngưỡng. Tính Z-score hoặc khoảng IQR. Bắt đầu với ngưỡng thận trọng (|Z| > 2), xem ra bao nhiêu kết quả. Quá nhiều thì nâng lên 2,5 hoặc 3; quá ít (bỏ sót) thì hạ xuống.
Bước 5 — Lọc, sắp xếp, và thêm bối cảnh. ORDER BY theo độ lớn của Z để cái bất thường nhất hiện lên đầu. Kèm theo giá trị thực và giá trị baseline để người đọc tự đánh giá. Một cảnh báo "Z = 3,4; thực tế 9,1% vs bình thường 2,8%" hữu ích hơn nhiều so với chỉ một dòng "có bất thường".
Bước 6 — Đóng gói thành alert hoặc dashboard. Đưa query vào Metabase, đặt lịch chạy hằng ngày, gửi cảnh báo qua email/Slack khi có dòng trả về. BA giỏi không chỉ phát hiện một lần — họ biến nó thành hệ thống giám sát tự động.
Lỗi thường gặp & mẹo
Chia cho 0. Khi mọi giá trị giống nhau, STDDEV = 0 và phép chia Z-score nổ lỗi. Luôn bọc mẫu số bằng NULLIF(std, 0).
Để bất thường làm hỏng baseline. Nếu bạn tính trung bình trên cả ngày đang kiểm tra, một outlier khổng lồ sẽ tự kéo trung bình về phía nó và che giấu chính mình. Hãy loại ngày hiện tại ra khỏi tập tính baseline (như ví dụ Be ở trên).
Bỏ qua tính mùa vụ. So sánh ngày thường với cuối tuần, hoặc ngày thường với mùa sale 11/11, sẽ tạo ra hàng loạt false positive. Nếu dữ liệu có chu kỳ rõ, hãy so cùng khung giờ / cùng thứ trong tuần thay vì trung bình phẳng.
Gộp dữ liệu quá thô. Đây là lỗi phổ biến nhất. Bất thường ở một category nhỏ thường bị "pha loãng" khi nhìn tổng. Luôn thử tách theo dimension trước khi kết luận "mọi thứ bình thường".
Cỡ mẫu quá nhỏ. STDDEV trên 5–7 điểm dữ liệu rất không ổn định. Cố gắng có ít nhất 20–30 điểm lịch sử trước khi tin vào Z-score.
Mẹo robust Z-score. Khi dữ liệu nhiều outlier, thay AVG/STDDEV bằng trung vị và MAD (median absolute deviation) để baseline không bị outlier kéo lệch. Đơn giản hơn thì dùng thẳng IQR.
Mẹo phân biệt false positive. Trước khi báo sếp, hãy tự hỏi: hôm đó có sự kiện gì không? (sale, lễ, campaign, downtime). Một bất thường có lời giải thích nghiệp vụ thì không phải lỗi — nhưng vẫn đáng ghi nhận.
Bài tập thực hành
Giả sử bạn có bảng orders(order_id, order_date, customer_id, region, amount, is_refunded).
Bài 1. Viết query dùng Z-score tìm những ngày trong 60 ngày gần nhất có tổng doanh thu bất thường (|Z| > 2). Trả về ngày, doanh thu, và z_score, sắp xếp theo độ lớn của Z.
Bài 2. Mở rộng bài 1: tính baseline riêng cho từng region, rồi tìm các cặp (region, ngày) có doanh thu bất thường với |Z| > 2,5. Vì sao kết quả này có thể khác hoàn toàn so với bài 1?
Bài 3. Dùng IQR (Q1, Q3) thay cho Z-score để tìm các ngày có số đơn hàng ngoài khoảng [Q1 − 1.5×IQR, Q3 + 1.5×IQR]. So sánh kết quả với phương pháp Z-score — phương pháp nào nhạy hơn với dữ liệu của bạn?
Bài 4 (nâng cao). Dùng LAG(..., 7) để so doanh thu mỗi ngày với chính ngày đó của tuần trước, tính phần trăm thay đổi, và đánh dấu những ngày tăng/giảm hơn 40% so với cùng thứ tuần trước. Đây là cách xử lý mùa vụ theo tuần.
Gợi ý cho bài 4: dùng window function LAG(revenue, 7) OVER (ORDER BY order_date) trên một CTE đã GROUP BY theo ngày, rồi lọc ABS(revenue - prev) / prev > 0.4.
Tóm tắt
Anomaly detection bằng SQL là một trong những kỹ năng "đáng tiền" nhất của BA, vì nó biến dữ liệu thành cảnh báo sớm cho các vấn đề kinh doanh thật. Những điểm cốt lõi cần nhớ:
- Bất thường luôn được định nghĩa trong bối cảnh — so với lịch sử, cùng kỳ, hoặc nhóm tương tự, chứ không phải một con số tuyệt đối.
- Z-score (
AVG,STDDEV) là công cụ chủ lực, dễ triển khai; IQR (phân vị) bền hơn khi nhiều outlier; so sánh theo chu kỳ (LAG7 ngày) xử lý tốt mùa vụ. - Luôn bọc mẫu số bằng
NULLIF(std, 0), tính baseline từ quá khứ (loại ngày hiện tại), và tách theo dimension để không bỏ sót bất thường bị pha loãng. - Ngưỡng động theo từng đối tượng (per-user, per-region) mạnh hơn ngưỡng chung, đặc biệt cho fraud.
- Mục tiêu cuối không chỉ là phát hiện một lần, mà là đóng gói thành alert tự động chạy hằng ngày — và luôn kèm bối cảnh để cảnh báo của bạn được tin và được hành động.