Sử dụng CTE để làm cho truy vấn phức tạp dễ đọc hơn

Hãy tưởng tượng bạn cần viết một truy vấn SQL to đùng, thực hiện nhiều thao tác liên quan với nhau. Bạn có thể nhét hàng đống subquery lồng nhau, nhưng kết quả sẽ thành spaghetti-code. Một mê cung SQL thực sự, mà chính bạn cũng dễ lạc luôn.

CTE chính là phao cứu sinh của bạn! CTE cho phép chia nhỏ truy vấn phức tạp thành các phần logic, mỗi phần là một section có tên riêng. Nhờ vậy, truy vấn của bạn sẽ dễ hiểu và dễ bảo trì hơn nhiều.

So sánh: Subquery vs CTE

Nhìn sơ qua thì cả hai cách đều làm giống nhau — lọc điểm theo khóa học và tính điểm trung bình cho từng sinh viên. Nhưng nhìn kỹ đi: với subquery thì logic bị "giấu" trong ngoặc, còn với CTE thì nó được tách ra ngoài và đặt tên rõ ràng là filtered_grades. Giờ tưởng tượng có 10 bước trung gian như vậy chứ không chỉ 2 thì sao!

Subquery:

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE:

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

Tìm 10 điểm khác biệt đi. Rõ ràng, CTE đọc sướng hơn hẳn!

Chia nhỏ truy vấn phức tạp thành từng bước với CTE

CTE cho phép bạn xây dựng truy vấn từng bước, để ở mỗi bước kết quả đều dễ hiểu nhất có thể. Ví dụ, nếu bạn muốn lấy danh sách sinh viên kèm điểm trung bình theo từng khóa học và thêm thông tin giáo viên, hãy chia nhỏ bài toán thành nhiều phần.

Ví dụ:

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

Đọc có rõ không? Dù bạn quay lại truy vấn này sau một tháng, cấu trúc của nó vẫn cực kỳ dễ hiểu.

Dùng nhiều CTE cho báo cáo lớn

Cùng xem ví dụ về một báo cáo phức tạp hơn. Giả sử bạn có database cho trường đại học, và muốn tạo báo cáo về những sinh viên xuất sắc nhất, các khóa học của họ và giáo viên phụ trách. Kế hoạch như sau:

1. Đầu tiên tìm sinh viên có điểm trung bình cao (trên 90).
2. Sau đó ghép họ với các khóa học.
3. Cuối cùng, thêm thông tin giáo viên.

Truy vấn với nhiều CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

Điểm mạnh của truy vấn này là mỗi phần của bài toán đều được tách thành một block logic riêng biệt. Muốn biết sinh viên nào xuất sắc? Nhìn vào CTE high_achievers. Muốn xem họ học khóa nào? Đó là student_courses. Cần giáo viên? Tất cả trong teachers. Cách này giúp việc bảo trì và chỉnh sửa code dễ hơn rất nhiều.

Chia nhỏ các phép tính phức tạp thành từng bước

Đôi khi truy vấn của bạn có nhiều phép tính hoặc lọc phức tạp. Thay vì cố nhồi nhét tất cả vào một truy vấn dài ngoằng, hãy chia nhỏ nó thành nhiều CTE.

Ví dụ:

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

Ở đây, đầu tiên mình gom thống kê theo khóa học trong course_stats, sau đó lọc ra các khóa học phổ biến trong popular_courses, rồi mới join với bảng courses. Cách này giúp tách các bước trung gian, làm cho truy vấn dễ hiểu hơn nhiều.

Khi nào CTE là không thể thiếu?

Dưới đây là vài trường hợp CTE cực kỳ hữu ích:

1. Phân tích và báo cáo. Ví dụ, tính toán các chỉ số phức tạp với lọc theo nhóm.
2. Làm việc với cấu trúc phân cấp. Dùng CTE đệ quy để xây dựng cây danh mục hoặc sơ đồ tổ chức.
3. Tái sử dụng dữ liệu. Khi một tập dữ liệu được dùng ở nhiều bước khác nhau trong truy vấn.

Các lỗi thường gặp khi dùng CTE

Tất nhiên, giống như bất kỳ công cụ mạnh nào, CTE cũng có những cái bẫy riêng.

Materialization dữ liệu quá mức. Trong PostgreSQL, CTE mặc định sẽ "materialize", tức là kết quả được tính toán và lưu tạm thời. Nếu dữ liệu quá lớn, điều này có thể làm truy vấn chậm đi. Để tránh, hãy dùng index và chỉ chọn những cột thực sự cần thiết.

Join sai. Đôi khi truy vấn phức tạp với nhiều CTE sẽ khó tối ưu hóa. Luôn kiểm tra truy vấn với EXPLAIN hoặc EXPLAIN ANALYZE.

Lạm dụng CTE. Nếu CTE của bạn quá dài và rối rắm, có thể bạn nên tách truy vấn thành nhiều thao tác riêng biệt.