CTE đơn giản để chuẩn bị dữ liệu: ví dụ và case thực tế

CTE đơn giản để chuẩn bị dữ liệu: ví dụ và case thực tế

Có vẻ như bạn đã nắm được cơ bản về CTE và có khi còn viết WITH như phản xạ rồi ấy nhỉ. Hôm nay tụi mình cùng lặn sâu hơn chút — xem thử cách dùng CTE để chuẩn bị dữ liệu trong tình huống thực tế. Hãy tưởng tượng bạn sắp tạo báo cáo hoặc viết một truy vấn SQL phức tạp: đầu tiên phải tách nhỏ nguyên liệu ra đã — rồi mới nấu món “súp” phân tích ngon lành được.

CTE ở đây là công cụ xịn cho các bước trung gian: lọc, đếm, tổng hợp, tính trung bình — nói chung là mọi thứ cần thiết để chuẩn bị dữ liệu cho hợp lý. Bạn có thể chia truy vấn phức tạp thành các block logic dễ hiểu, mỗi block làm một việc: chọn dòng cần thiết, tính trung bình hoặc chuẩn bị dữ liệu cho bước cuối. Như vậy code dễ đọc hơn, tránh lặp lại và khỏi phải tạo bảng tạm nếu không cần.

Cách làm với CTE đặc biệt hữu ích khi bạn chuẩn bị dữ liệu cho báo cáo, xây dựng bộ lọc phức tạp hoặc muốn “làm sạch” dữ liệu trước khi xử lý tiếp. Theo kiểu này, CTE không chỉ là mẹo kỹ thuật mà còn là chiến lược — xây logic từng bước, kiểm soát mọi thứ rõ ràng.

Sẵn sàng chưa? Giờ mình qua ví dụ luôn nhé.

Lọc dữ liệu với CTE

CTE là cách cực hay để “lôi” dữ liệu cần thiết từ bảng tổng, rồi sau đó chỉ làm việc với phần bạn thực sự quan tâm thôi. Thay vì viết truy vấn lồng cồng kềnh, bạn lọc dữ liệu trước, đặt tên cho bước đó — rồi dùng kết quả như bảng bình thường.

Giả sử tụi mình có bảng students, lưu điểm của sinh viên:

Bảng students

Giả dụ bạn muốn chọn tất cả ai có điểm trên 85. Dùng CTE thì siêu rõ ràng luôn:

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

Kết quả:

Cái hay ở đây là gì?

Bạn lọc trước các dòng cần thiết và đặt tên cho bước này — excellent_students. Giờ bạn có thể dùng kết quả này tiếp: ví dụ join với bảng khác, lọc thêm lần nữa hoặc tính điểm trung bình. Mọi thứ dễ đọc, đơn giản và không rối, nhất là khi truy vấn dài.

Tổng hợp dữ liệu với CTE

Giờ chuyển sang trường hợp cần đếm số dòng hoặc tính trung bình. Ví dụ, tụi mình có bảng enrollments, lưu thông tin sinh viên đăng ký môn học nào.

Bảng enrollments

Tụi mình muốn biết mỗi môn có bao nhiêu sinh viên đăng ký.

Ví dụ truy vấn:

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

Kết quả:

Điểm cần chú ý:

• Tụi mình group theo course_id và đếm số sinh viên cho từng môn.
• Bảng course_enrollments giờ chứa thông tin này, có thể dùng tiếp cho phân tích sau.

Chuẩn bị dữ liệu cho báo cáo

Nếu bạn cần tạo báo cáo chi tiết dựa trên nhiều bước xử lý dữ liệu, CTE là cứu tinh luôn. Nó cho phép chia logic thành các block dễ hiểu mà không phải tạo bảng tạm thừa thãi. Giả sử bạn có bảng grades chứa điểm và bảng students chứa thông tin sinh viên. Cần lập báo cáo chỉ gồm sinh viên có điểm trung bình trên 80.

Bảng grades

Bảng students

Thay vì viết truy vấn lồng cồng kềnh, bạn có thể làm từng bước rõ ràng:

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

Bước đầu (avg_grades) tụi mình tính điểm trung bình cho từng sinh viên và lọc luôn ai có kết quả tốt — trên 80. Bước hai (students_with_grades) join dữ liệu này với bảng students để lấy tên họ. Kết quả SELECT cuối cùng trả về bảng gọn gàng, có thể chèn thẳng vào báo cáo — mọi thứ đã được tính, lọc và trình bày đẹp rồi.

Kết quả:

Chính kiểu làm này khiến CTE cực tiện: bạn tập trung vào logic và cấu trúc, khỏi phải lo chuyện phụ như tạo/xóa bảng tạm.

Tính metric phức tạp

Đôi khi bạn phải kết hợp nhiều dữ liệu trong một truy vấn. Ví dụ, cần tính cho mỗi môn học:

1. Số lượng sinh viên.
2. Điểm trung bình của môn.

Ví dụ truy vấn:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

Lỗi nên tránh

Làm việc với CTE dễ bị rối và mắc vài lỗi phổ biến.

Lỗi đầu tiên — materialization dư thừa. Nếu tạo quá nhiều CTE, PostgreSQL có thể lưu kết quả thành bảng tạm, dù chỉ dùng một lần. Kết quả là truy vấn chạy chậm hơn mong đợi.

Lỗi thứ hai — áp dụng filter sai chỗ. Nếu filter đặt sai thứ tự hoặc ở các bước khác nhau, kết quả cuối có thể không như ý. Ví dụ, bạn có thể loại mất dữ liệu quan trọng quá sớm.

Vì vậy, CTE nên dùng khi dữ liệu cần qua nhiều bước biến đổi liên tiếp — lúc đó công cụ này phát huy hết lợi thế, giúp code sạch, dễ hiểu và hiệu quả.