CTE (Common Table Expression) trong SQL: Cách viết truy vấn nhiều tầng sạch sẽ và dễ bảo trì

Hướng dẫn sử dụng CTE (mệnh đề WITH) trong SQL: cách phân rã truy vấn lồng phức tạp thành các bảng tạm logic dễ đọc, dễ kiểm thử.

1. Subquery phức tạp và nỗi đau của Data Analyst

Khi làm việc với các hệ thống cơ sở dữ liệu lớn, các Data Analyst thường xuyên phải viết những câu lệnh truy vấn (Query) lồng nhau nhiều tầng (Nested Subqueries).

Việc này dẫn đến nhiều hệ lụy:

• Câu lệnh trở nên quá dài và khó đọc.

• Khó kiểm thử (debugging) từng phần khi xảy ra lỗi dữ liệu.

• Giảm hiệu suất bảo trì khi cấu trúc bảng thay đổi.

Đó là lúc CTE (Common Table Expression) hay mệnh đề WITH trở thành “cứu tinh”.

2. CTE trong SQL là gì?

CTE là một tập kết quả tạm thời (Temporary Result Set) được định nghĩa ngay trong phạm vi thực thi của một câu lệnh SQL duy nhất (SELECT, INSERT, UPDATE, hoặc DELETE).

Thay vì lồng các subquery vào nhau, CTE cho phép bạn tách nhỏ bài toán thành các bước logic tuần tự, giống như việc bạn đặt tên cho từng bước xử lý trung gian.

3. Cú pháp cơ bản của CTE

Cú pháp của một CTE bắt đầu bằng từ khóa WITH, theo sau là tên của CTE và câu lệnh truy vấn bên trong dấu ngoặc đơn:

WITH Ten_CTE AS (
    -- Câu lệnh SQL truy vấn dữ liệu trung gian
    SELECT Column1, Column2
    FROM TableName
    WHERE Condition
)
-- Truy vấn chính gọi lại CTE vừa tạo
SELECT *
FROM Ten_CTE
WHERE Column1 > 100;

4. Ví dụ thực tế: Tối ưu truy vấn với CTE

Giả sử bạn cần tính doanh thu trung bình của các cửa hàng có tổng doanh thu vượt mức trung bình toàn hệ thống. Nếu dùng Subquery truyền thống, câu lệnh sẽ rất rối rắm. Với CTE, các bước được phân rã rất sạch sẽ:

-- Bước 1: Tạo CTE tính doanh thu của từng cửa hàng
WITH DoanhThuCuaHang AS (
    SELECT 
        Store_ID,
        SUM(Revenue) AS Total_Revenue
    FROM Sales
    GROUP BY Store_ID
),
-- Bước 2: Tạo CTE thứ 2 tính mức doanh thu trung bình chung
DoanhThuTrungBinh AS (
    SELECT AVG(Total_Revenue) AS Avg_Revenue
    FROM DoanhThuCuaHang
)
-- Bước 3: Truy vấn chính kết hợp các CTE
SELECT c.Store_ID, c.Total_Revenue
FROM DoanhThuCuaHang c
JOIN DoanhThuTrungBinh a
ON c.Total_Revenue > a.Avg_Revenue;

5. Ưu điểm vượt trội của CTE

• Mã nguồn sạch và dễ đọc: Chia nhỏ logic phức tạp thành các khối độc lập, dễ dàng giải thích cho đồng nghiệp.

• Dễ dàng kiểm thử (Debugging): Bạn có thể chạy riêng từng khối CTE bên trong câu lệnh để kiểm tra dữ liệu trước khi ghép nối.

• Hỗ trợ đệ quy (Recursive CTE): Giải quyết cực kỳ hiệu quả các bài toán phân cấp dữ liệu như sơ đồ tổ chức công ty, quản lý danh mục cha – con.

6. Tổng kết

Sử dụng thành thạo CTE trong SQL là bước tiến quan trọng giúp bạn nâng cao kỹ năng xử lý dữ liệu, viết code sạch và tối ưu hóa hiệu suất hệ thống. Đây là kỹ năng cốt lõi mà mọi Data Analyst chuyên nghiệp cần nắm vững.

✍️ Tác giả: Phan Thị Như Phước – Data Analyst tại MCNA Technology School

📞 Hotline: 0939.866.825 (Mr. Minh Khang)

🌐 Website: mcna.vn

📍 Hà Nội: 30 Trung Liệt, Đống Đa | Liền kề 44B TT2 Văn Quán, Hà Đông

📍 TP.HCM: 50B Phan Tây Hồ, Cầu Kiệu