Hướng Dẫn Sử Dụng CTE Trong SQL Để Tổ Chức Truy Vấn Nhiều Bước

Khi xử lý các yêu cầu trích xuất dữ liệu kinh doanh phức tạp, hầu như mọi Data Analyst đều từng rơi vào cái bẫy của truy vấn lồng nhau (Nested Subqueries). Khi một câu lệnh SQL có tới 3-4 tầng ngoặc đơn lồng nhau, việc đọc hiểu luồng dữ liệu, gỡ lỗi (debug) hay bàn giao mã nguồn cho đồng nghiệp trở thành một thách thức lớn. CTE (Common Table Expression) xuất hiện như một giải pháp chuẩn mực giúp chuẩn hóa tư duy truy vấn: biến mã nguồn từ dạng “xoắn ốc lồng ghép” thành luồng dữ liệu tuyến tính từ trên xuống dưới.

Bài viết này cung cấp một hướng dẫn thực chiến toàn diện: từ bản chất thực thi của CTE trong Database Engine, phân tích so sánh với Subquery và Bảng tạm, cho đến cách tổ chức nhiều CTE (Multiple CTEs) để giải quyết bài toán đo lường doanh thu đơn hàng thứ hai (Repeat Purchase Revenue).

Theo tài liệu kỹ thuật chính thức từ Microsoft Learn (T-SQL Reference), CTE (Common Table Expression) là một tập kết quả tạm thời được đặt tên (temporary named result set), được phát sinh từ một câu truy vấn đơn giản và được định nghĩa trong phạm vi thực thi của một câu lệnh duy nhất như SELECT, INSERT, UPDATE, DELETE hoặc MERGE.

Ba đặc tính cốt lõi tạo nên sức mạnh của CTE:

• Phạm vi thực thi khép kín (Scope): CTE chỉ khả dụng ngay trong câu lệnh theo sau nó. Ngay khi câu lệnh chính kết thúc, kết quả tạm thời này sẽ tự động được giải phóng khỏi ngữ cảnh thực thi.
• Tái sử dụng nội bộ (Internal Reusability): Trong phạm vi của câu lệnh chính, bạn có thể gọi tên CTE nhiều lần (ví dụ: tự JOIN với chính nó) mà không cần phải viết lại toàn bộ khối mã nguồn.
• Khả năng đệ quy (Recursive CTE): CTE có thể tự tham chiếu lại chính nó, hỗ trợ giải quyết các bài toán dữ liệu dạng phân cấp (cây danh mục sản phẩm, cơ cấu tổ chức quản lý) mà các câu lệnh SQL thông thường khó xử lý.

Một câu hỏi thường gặp khi đi phỏng vấn vị trí Data Analyst là: “Khi nào nên dùng CTE, khi nào nên dùng Subquery và khi nào phải dùng Bảng tạm?”. Dưới đây là bảng đối chiếu kỹ thuật chuẩn mực:

Một CTE cơ bản luôn mở đầu bằng từ khóa WITH, theo sau là tên đặt cho CTE và câu lệnh truy vấn nằm trong cặp dấu ngoặc đơn (...):

-- Dấu chấm phẩy ngăn chặn lỗi batch của T-SQL
;WITH cte_active_customers AS (
    SELECT customer_id, customer_name, city
    FROM customers
    WHERE status = 'Active'
)
-- Truy vấn chính gọi CTE ngay bên dưới
SELECT city, COUNT(customer_id) AS total_customers
FROM cte_active_customers
GROUP BY city;

Đây là kỹ thuật mạnh mẽ nhất của CTE: bạn có thể định nghĩa liên tiếp nhiều khối xử lý. Quy tắc quan trọng cần nhớ: chỉ sử dụng duy nhất một từ khóa WITH ở đầu, các CTE tiếp theo phân tách nhau bằng dấu phẩy ,. Đặc biệt, CTE đứng sau hoàn toàn có thể tham chiếu trực tiếp đến dữ liệu của các CTE đã định nghĩa phía trước:

;WITH cte_step_one AS (
    SELECT ...
),
cte_step_two AS (
    -- Tham chiếu đến kết quả của cte_step_one
    SELECT ...
    FROM cte_step_one
)
SELECT * 
FROM cte_step_two;

Trong ngành Bán lẻ và Thương mại điện tử (E-commerce), đơn hàng thứ 2 là cột mốc sống còn (inflection point). Đây là thời điểm phân định một người dùng là “khách mua thử một lần rồi rời bỏ” hay đã chuyển hóa thành “khách hàng quay lại (Repeat Customer)”.

Giả sử chúng ta có 2 bảng dữ liệu nguồn với cấu trúc mẫu như sau:

Bảng orders (Lịch sử giao dịch):

Bảng customers (Hồ sơ khách hàng):

Toàn bộ câu lệnh SQL được chia thành 2 CTE nối tiếp nhau trước khi tổng hợp ở truy vấn chính:

-- BƯỚC 1: Xếp hạng thứ tự đơn hàng của từng khách hàng
;WITH cte_ranked_orders AS (
    SELECT 
        order_id,
        customer_id,
        order_date,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date ASC
        ) AS order_sequence
    FROM orders
    WHERE order_status = 'Completed'
),

-- BƯỚC 2: Trích xuất chính xác các đơn hàng ở lần mua thứ hai
cte_second_orders AS (
    SELECT 
        customer_id,
        order_id,
        total_amount AS second_order_value
    FROM cte_ranked_orders
    WHERE order_sequence = 2
)

-- BƯỚC 3: Ghép nối bảng khách hàng để tổng hợp báo cáo theo thành phố
SELECT 
    c.city AS customer_city,
    COUNT(s.customer_id) AS repeat_customer_count,
    ROUND(AVG(s.second_order_value), 0) AS avg_second_order_revenue
FROM cte_second_orders s
INNER JOIN customers c ON s.customer_id = c.customer_id
GROUP BY c.city
ORDER BY avg_second_order_revenue DESC;

Cơ chế xử lý diễn ra như thế nào qua từng khối lệnh?

• Khối cte_ranked_orders: Hàm Window Function ROW_NUMBER() chia cụm dữ liệu theo từng customer_id (nhờ PARTITION BY) và sắp xếp ngày mua tăng dần. Kết quả sinh ra cột order_sequence (1 cho đơn đầu, 2 cho đơn tiếp theo…). Đồng thời, điều kiện WHERE order_status = 'Completed' loại trừ ngay các đơn hàng bị hủy trước khi tính thứ hạng.
• Khối cte_second_orders: Kế thừa tập dữ liệu đã đánh số từ bước 1 và chỉ lọc đúng điều kiện order_sequence = 2. Lúc này, chỉ những khách hàng thực sự quay lại mua lần 2 mới được giữ lại.
• Khối SELECT cuối cùng: Thực hiện INNER JOIN với bảng danh mục customers để lấy chiều thông tin địa lý (city), áp dụng hàm tổng hợp COUNT() đếm số lượng khách mua lại và AVG() tính doanh thu trung bình.

Kết quả báo cáo đầu ra (Final Query Result):

• Microsoft Learn: WITH common_table_expression (Transact-SQL)
  – Tài liệu kỹ thuật chuẩn mực từ Microsoft về cú pháp, nguyên tắc hoạt động và hướng dẫn sử dụng CTE trong SQL Server.
• Mode Analytics: Use Common Table Expressions (CTE) to Keep Your SQL Clean
  – Hướng dẫn thực hành chuẩn mực từ Mode Analytics về tư duy giữ cấu trúc mã SQL sạch sẽ, dễ bảo trì cho Data Team.