Menu
Học tập
Khóa học Lộ trình nghề nghiệp Roadmap BA → CPO BA → Product Manager Tài liệu & Sách
Cộng đồng
Cộng đồng Interview Questions
Công cụ
Phân tích CV Theo dõi ứng tuyển Hiring Pipeline Luyện phỏng vấn Tối ưu LinkedIn Đàm phán lương
Tài khoản
ESC

Nhập từ khóa để tìm kiếm

↑↓ Di chuyển
Enter Mở
ESC Đóng

CTE (Common Table Expressions): query dễ đọc hơn

SQL and Data Analysis cho BA Bài 16/60

Mở đầu — vì sao bài này quan trọng

Hãy tưởng tượng bạn là BA tại một sàn thương mại điện tử như Tiki. Sếp yêu cầu một báo cáo: "Cho anh xem những tháng mà doanh thu cao hơn doanh thu trung bình của cả năm". Nghe đơn giản, nhưng để viết câu query này, bạn cần làm hai việc: trước tiên tính doanh thu theo từng tháng, rồi sau đó so sánh với mức trung bình. Nếu chỉ biết subquery (như đã học ở Bài 15), bạn sẽ phải lồng một câu SELECT vào trong một câu SELECT khác, rồi lại lồng tiếp một lớp nữa để so sánh. Kết quả là một "ma trận ngoặc đơn" mà ba ngày sau chính bạn đọc lại cũng không hiểu mình đã viết gì.

Đây chính là lúc CTE — Common Table Expression — trở thành người bạn thân nhất của một BA. CTE cho phép bạn tách một query phức tạp thành những bước nhỏ, có tên rõ ràng, đọc từ trên xuống dưới y như đọc một đoạn văn. Thay vì lồng query vào nhau như búp bê Nga, bạn xếp chúng thành các "khối" nối tiếp.

Với người làm phân tích nghiệp vụ, kỹ năng viết query dễ đọc quan trọng không kém viết query chạy đúng. Báo cáo của bạn sẽ được người khác đọc lại, sửa lại, kiểm tra lại. Một câu SQL rối rắm là một quả bom hẹn giờ cho sai sót dữ liệu. Bài học này sẽ giúp bạn biến những logic phức tạp thành các query sạch sẽ, có cấu trúc mà cả đồng nghiệp non-tech cũng có thể lần theo được.

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

CTE là gì?

CTE là một "bảng tạm thời" được định nghĩa ngay ở đầu câu query bằng từ khóa WITH, và chỉ tồn tại trong phạm vi của đúng câu query đó. Sau khi câu query chạy xong, CTE biến mất — nó không được lưu vào database, không chiếm dung lượng vĩnh viễn, không ai khác nhìn thấy nó.

Cú pháp cơ bản như sau:

WITH ten_cte AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM ten_cte
WHERE ...;

Bạn có thể hình dung CTE như câu nói: "Cho tôi định nghĩa trước một thứ tên là ten_cte, nội dung của nó là kết quả của câu SELECT trong ngoặc. Sau đó, ở phần query chính bên dưới, tôi sẽ dùng ten_cte như thể nó là một bảng có sẵn."

Ví dụ đầu tiên với doanh thu theo tháng

Quay lại bài toán mở đầu. Giả sử Tiki có bảng orders với các cột order_id, order_date, total_amount, status. Ta muốn tính doanh thu mỗi tháng năm 2025:

WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS thang,
        SUM(total_amount) AS doanh_thu
    FROM orders
    WHERE status = 'completed'
      AND YEAR(order_date) = 2025
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    thang,
    doanh_thu
FROM monthly_revenue
ORDER BY thang;

CTE monthly_revenue ở đây đóng vai trò một bảng kết quả gồm hai cột: thang và doanh_thu. Phần query chính bên dưới đơn giản chỉ việc lấy từ bảng tạm đó ra.

Vì sao CTE dễ đọc hơn subquery?

So sánh trực tiếp. Cùng bài toán "tháng có doanh thu cao hơn trung bình", nếu viết bằng subquery lồng nhau:

SELECT thang, doanh_thu
FROM (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS thang,
           SUM(total_amount) AS doanh_thu
    FROM orders
    WHERE status = 'completed' AND YEAR(order_date) = 2025
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) AS t
WHERE doanh_thu > (
    SELECT AVG(doanh_thu_thang)
    FROM (
        SELECT SUM(total_amount) AS doanh_thu_thang
        FROM orders
        WHERE status = 'completed' AND YEAR(order_date) = 2025
        GROUP BY DATE_FORMAT(order_date, '%Y-%m')
    ) AS t2
);

Bạn thấy ngay vấn đề: cùng một logic "tính doanh thu theo tháng" bị viết lặp lại hai lần, query lồng ba lớp, và phải đọc từ trong ra ngoài. Bây giờ xem phiên bản CTE:

WITH monthly_revenue AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS thang,
           SUM(total_amount) AS doanh_thu
    FROM orders
    WHERE status = 'completed' AND YEAR(order_date) = 2025
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT thang, doanh_thu
FROM monthly_revenue
WHERE doanh_thu > (SELECT AVG(doanh_thu) FROM monthly_revenue);

Ngắn hơn, logic "doanh thu theo tháng" chỉ viết một lần, và đọc tuần tự từ trên xuống. Đây là lợi ích lớn nhất của CTE: không lặp lại logic (DRY — Don't Repeat Yourself) và đọc theo trình tự tự nhiên.

Nhiều CTE nối tiếp nhau

Sức mạnh thực sự của CTE bộc lộ khi bạn định nghĩa nhiều CTE trong cùng một query, mỗi cái dùng kết quả của cái trước. Bạn chỉ cần viết WITH một lần, rồi ngăn các CTE bằng dấu phẩy:

WITH doanh_thu_thang AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS thang,
           SUM(total_amount) AS doanh_thu
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
muc_trung_binh AS (
    SELECT AVG(doanh_thu) AS tb
    FROM doanh_thu_thang
)
SELECT d.thang, d.doanh_thu, m.tb
FROM doanh_thu_thang d
CROSS JOIN muc_trung_binh m
WHERE d.doanh_thu > m.tb;

Để ý cách muc_trung_binh dùng lại doanh_thu_thang ngay phía trên nó. Đây là kiểu "dây chuyền xử lý" (pipeline): mỗi CTE là một bước biến đổi dữ liệu, và bạn xếp các bước thành một chuỗi mạch lạc. Một BA giỏi sẽ tổ chức query phức tạp thành 3–5 CTE, mỗi cái làm đúng một việc và đặt tên rõ nghĩa.

> Lưu ý quan trọng: trong một CTE chỉ tham chiếu được tới các CTE đã định nghĩa trước nó. Bạn không thể dùng một CTE đứng sau (trừ trường hợp recursive CTE — chủ đề riêng của Bài 17).

Tình huống thực tế

Tình huống 1: Báo cáo top khách hàng tại Shopee

Bối cảnh: Chị Lan là BA ở bộ phận Customer Insights của một seller lớn trên Shopee. Chị cần báo cáo "Top 20% khách hàng đóng góp bao nhiêu phần trăm doanh thu" — câu hỏi kinh điển về quy luật 80/20. Dữ liệu nằm ở bảng orders (có customer_id, total_amount, status).

Logic cần nhiều bước: (1) tính tổng chi tiêu mỗi khách, (2) xếp hạng khách theo chi tiêu, (3) xác định nhóm top 20%, (4) tính tỷ trọng doanh thu của nhóm đó.

WITH chi_tieu_khach AS (
    SELECT customer_id,
           SUM(total_amount) AS tong_chi_tieu
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
xep_hang AS (
    SELECT customer_id,
           tong_chi_tieu,
           NTILE(5) OVER (ORDER BY tong_chi_tieu DESC) AS nhom_5
    FROM chi_tieu_khach
)
SELECT
    CASE WHEN nhom_5 = 1 THEN 'Top 20%' ELSE 'Còn lại 80%' END AS phan_nhom,
    COUNT(*) AS so_khach,
    SUM(tong_chi_tieu) AS doanh_thu_nhom
FROM xep_hang
GROUP BY CASE WHEN nhom_5 = 1 THEN 'Top 20%' ELSE 'Còn lại 80%' END;

Diễn giải: chi_tieu_khach gom doanh thu mỗi khách; xep_hang dùng hàm NTILE(5) chia khách thành 5 nhóm bằng nhau theo chi tiêu (nhóm 1 là cao nhất, tức top 20%); query chính so sánh hai nhóm. Kết quả thực tế của chị Lan: top 20% khách đóng góp 73% doanh thu — đúng tinh thần Pareto.

Bài học rút ra: nhờ tách thành CTE, khi sếp hỏi "đổi thành top 10% xem sao", chị Lan chỉ cần sửa NTILE(5) thành NTILE(10) và đổi điều kiện nhóm — không phải gỡ rối cả khối query lồng nhau.

Tình huống 2: Phân tích đơn hàng bất thường tại Be (gọi xe)

Bối cảnh: Anh Tuấn là BA của Be. Đội vận hành nghi ngờ có những tài xế nhận cuốc với giá trị cao bất thường so với mặt bằng khu vực. Bảng trips có trip_id, driver_id, city, fare, trip_date. Anh cần tìm những chuyến có giá cước cao hơn gấp đôi mức trung bình của thành phố đó.

WITH cuoc_tb_theo_tp AS (
    SELECT city,
           AVG(fare) AS gia_tb,
           COUNT(*) AS so_chuyen
    FROM trips
    WHERE trip_date >= '2025-06-01'
    GROUP BY city
)
SELECT
    t.trip_id,
    t.driver_id,
    t.city,
    t.fare,
    ROUND(c.gia_tb, 0) AS gia_tb_thanh_pho,
    ROUND(t.fare / c.gia_tb, 1) AS gap_bao_nhieu_lan
FROM trips t
JOIN cuoc_tb_theo_tp c ON t.city = c.city
WHERE t.trip_date >= '2025-06-01'
  AND t.fare > 2 * c.gia_tb
ORDER BY gap_bao_nhieu_lan DESC;

Diễn giải: CTE cuoc_tb_theo_tp tính giá cước trung bình từng thành phố. Query chính nối ngược lại bảng trips để so từng chuyến với mức trung bình thành phố tương ứng. Anh Tuấn phát hiện 47 chuyến ở TP.HCM có cước cao gấp 2,5–4 lần — hóa ra phần lớn là cuốc sân bay đường dài hợp lệ, nhưng 3 chuyến thì giá nhập sai do lỗi nhập liệu thủ công.

Bài học rút ra: CTE giúp tách rõ phần "tính chuẩn mực chung" (giá trung bình thành phố) khỏi phần "so sánh từng dòng". Cấu trúc này khiến đồng nghiệp vận hành (không rành SQL) vẫn đọc được query và xác nhận logic là đúng.

Tình huống 3: Báo cáo ví điện tử tại MoMo

Bối cảnh: Chị Hương làm BA mảng tăng trưởng tại một ví điện tử kiểu MoMo. Chị cần biết: trong số người dùng đăng ký tháng 1/2025, có bao nhiêu phần trăm thực hiện ít nhất một giao dịch trong vòng 30 ngày đầu? Bảng users có user_id, signup_date; bảng transactions có user_id, txn_date.

WITH nguoi_dung_thang_1 AS (
    SELECT user_id, signup_date
    FROM users
    WHERE signup_date BETWEEN '2025-01-01' AND '2025-01-31'
),
co_giao_dich AS (
    SELECT DISTINCT u.user_id
    FROM nguoi_dung_thang_1 u
    JOIN transactions t
      ON t.user_id = u.user_id
     AND t.txn_date BETWEEN u.signup_date AND DATE_ADD(u.signup_date, INTERVAL 30 DAY)
)
SELECT
    (SELECT COUNT(*) FROM nguoi_dung_thang_1) AS tong_dang_ky,
    (SELECT COUNT(*) FROM co_giao_dich) AS co_hoat_dong,
    ROUND(100.0  (SELECT COUNT() FROM co_giao_dich)
                / (SELECT COUNT(*) FROM nguoi_dung_thang_1), 1) AS ty_le_kich_hoat;

Diễn giải: CTE đầu lọc nhóm người đăng ký tháng 1; CTE thứ hai lọc ra ai có giao dịch trong 30 ngày. Query chính ráp lại thành tỷ lệ kích hoạt. Chị Hương ra con số 41,2% — và quan trọng hơn, query này dễ tái sử dụng cho các tháng khác chỉ bằng cách đổi khoảng ngày.

Bài học rút ra: khi bài toán có cấu trúc "định nghĩa nhóm A, rồi định nghĩa nhóm con B, rồi so sánh", CTE cho phép đặt tên đúng từng nhóm. Tên nguoi_dung_thang_1 và co_giao_dich tự thân đã giải thích nghiệp vụ, không cần thêm comment.

Hướng dẫn từng bước

Khi gặp một yêu cầu báo cáo phức tạp, hãy theo quy trình sau để dựng query bằng CTE:

  • Đọc yêu cầu và tách thành các bước nghiệp vụ. Viết ra giấy (bằng tiếng Việt) các bước cần làm. Ví dụ: "Bước 1: gom doanh thu theo tháng. Bước 2: tính trung bình. Bước 3: lọc tháng vượt trung bình." Mỗi bước thường sẽ thành một CTE.
  • Đặt tên CTE theo nghĩa nghiệp vụ. Đừng đặt t1, t2, tmp. Hãy đặt doanh_thu_thang, khach_vip, don_hang_loi. Tên tốt là tài liệu tốt.
  • Viết CTE đầu tiên và chạy thử nó độc lập. Mẹo cực hữu ích: tạm thời viết SELECT * FROM doanh_thu_thang ở phần query chính để kiểm tra riêng CTE đầu có ra đúng dữ liệu không, trước khi thêm các CTE sau.
  • Thêm dần các CTE tiếp theo bằng dấu phẩy. Mỗi lần thêm một CTE, lại chạy thử bằng cách SELECT * từ chính CTE vừa thêm. Cách "xây từng tầng" này giúp bạn định vị lỗi nhanh.
  • Viết query chính cuối cùng. Đây là nơi ráp các CTE lại — thường là JOIN hoặc so sánh giữa chúng.
  • Đọc lại toàn bộ từ trên xuống xem có đọc trôi như một đoạn văn không. Nếu thấy một CTE quá dài hoặc làm nhiều việc, hãy tách nó thành hai.

Lỗi thường gặp & mẹo

Lỗi 1 — Quên dấu phẩy giữa các CTE, hoặc thừa dấu phẩy trước query chính. Giữa hai CTE phải có dấu phẩy; nhưng không có dấu phẩy giữa CTE cuối cùng và câu SELECT chính. Đây là lỗi cú pháp phổ biến nhất với người mới.

Lỗi 2 — Tưởng CTE được lưu lại để dùng cho query sau. CTE chỉ sống trong đúng câu lệnh chứa nó. Câu query tiếp theo sẽ không thấy CTE đó nữa. Nếu cần dùng lại nhiều lần qua nhiều query, bạn cần VIEW (chủ đề của Bài 54), không phải CTE.

Lỗi 3 — Tham chiếu CTE đứng sau. CTE chỉ dùng được các CTE định nghĩa trước nó. Nếu cte_a muốn dùng cte_b, thì cte_b phải đứng trên cte_a.

Lỗi 4 — Lo lắng về hiệu năng một cách thái quá. Có quan niệm rằng CTE luôn chậm hơn subquery. Thực tế tùy database: PostgreSQL phiên bản cũ (trước 12) "vật chất hóa" CTE nên đôi khi chậm hơn, còn MySQL 8+ và PostgreSQL 12+ tối ưu khá tốt. Với phần lớn báo cáo BA, sự khác biệt là không đáng kể, và lợi ích về tính dễ đọc lớn hơn nhiều. Khi nào query thật sự chậm, hãy dùng EXPLAIN (Bài 25) để chẩn đoán thay vì đoán mò.

Mẹo 1 — Dùng CTE để "kể chuyện". Hãy nghĩ mỗi CTE như một câu trong câu chuyện phân tích: "Trước hết, lấy đơn hàng hợp lệ. Tiếp theo, gom theo khách. Sau đó, xếp hạng. Cuối cùng, lấy top." Cấu trúc kể chuyện này khiến report của bạn dễ bàn giao.

Mẹo 2 — CTE là công cụ debug tuyệt vời. Khi một query lớn cho kết quả sai, hãy SELECT * từ từng CTo một, từ trên xuống, để khoanh vùng đúng bước nào sinh ra dữ liệu lệch.

Mẹo 3 — Đừng lạm dụng. Nếu một query chỉ có một subquery đơn giản dùng một lần, viết thẳng subquery có khi gọn hơn. CTE phát huy giá trị khi logic phức tạp, dùng lại nhiều lần, hoặc cần đặt tên cho từng bước.

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

Dùng schema giả định của một sàn TMĐT Việt Nam: bảng orders(order_id, customer_id, order_date, total_amount, status) và customers(customer_id, full_name, city, signup_date).

  • Cơ bản: Viết một CTE tên doanh_thu_ngay tính tổng doanh thu mỗi ngày trong tháng 6/2025 (chỉ tính status = 'completed'), rồi ở query chính lọc ra những ngày có doanh thu trên 100 triệu đồng.
  • Hai CTE nối tiếp: Viết CTE thứ nhất gom tổng chi tiêu mỗi khách, CTE thứ hai tính chi tiêu trung bình toàn bộ khách. Query chính liệt kê những khách chi tiêu trên mức trung bình, kèm tên khách (JOIN với customers).
  • Nâng cao: Phân loại khách theo thành phố. Viết CTE tính doanh thu trung bình mỗi khách theo từng thành phố, rồi tìm những khách chi tiêu cao hơn mức trung bình của chính thành phố họ. Gợi ý: JOIN bảng chi tiêu khách với CTE trung bình-theo-thành-phố.
  • Thử thách debug: Lấy lời giải bài 3, cố tình tham chiếu CTE trung bình trước khi định nghĩa nó. Chạy thử, đọc thông báo lỗi, rồi sửa lại đúng thứ tự — để ghi nhớ quy tắc "CTE phải định nghĩa trước khi dùng".

Tóm tắt

CTE (Common Table Expression) là bảng tạm định nghĩa bằng WITH ở đầu query, chỉ sống trong phạm vi câu query đó. Lợi ích cốt lõi cho một BA là khả năng chia query phức tạp thành các bước có tên rõ ràng, đọc tuần tự từ trên xuống, thay vì lồng subquery rối rắm.

Những điểm cần nhớ: viết WITH một lần rồi ngăn các CTE bằng dấu phẩy; không đặt dấu phẩy trước query chính; một CTE chỉ dùng được các CTE đứng trước nó; CTE giúp tránh lặp lại logic (DRY) và là công cụ debug mạnh khi bạn SELECT * từng tầng. Đặt tên CTE theo nghĩa nghiệp vụ tiếng Việt để query tự giải thích chính nó.

Khi gặp báo cáo có cấu trúc nhiều bước — gom dữ liệu, tính chuẩn mực, rồi so sánh — hãy nghĩ ngay tới CTE. Đây là kỹ năng nền tảng giúp bạn tiến tới các chủ đề mạnh hơn như recursive CTE (Bài 17) để xử lý cây phân cấp, và window functions (Bài 18) để phân tích sâu hơn.

Trước Tiếp