Bài 31: CTE: đặt tên cho một bước truy vấn

Thầy Nam cần danh sách học viên đạt điểm cao nhất từ mức yêu cầu trở lên. Báo cáo phải tính kết quả riêng của từng bạn trước, rồi mới lọc và ghép tên. Khi mọi bước nằm trong nhiều lớp ngoặc, thầy khó chỉ ra chỗ cần sửa. Đặt tên cho kết quả của từng bước giúp cả người viết lẫn người đọc theo dõi công việc.

Đặt tên cho một danh sách để dùng ngay trong câu lệnh

Biểu thức bảng chung (common table expression, viết tắt CTE) đặt tên cho một kết quả truy vấn trong phạm vi một câu lệnh. WITH mở phần khai báo, tên theo sau được dùng như tên nguồn dữ liệu. CTE chưa tạo một bảng được lưu lâu dài.

Trước khi tổng hợp điểm, hãy thử một bước chỉ lọc học viên. Bước có tên grade_seven giữ các bạn khối 7, rồi truy vấn ngoài chọn hai cột cần gửi giáo viên:

WITH grade_seven AS (
  SELECT id, name
  FROM students
  WHERE grade = 7
)
SELECT id, name
FROM grade_seven
ORDER BY id;

Bình và Lan xuất hiện vì hai dòng của họ thỏa điều kiện trong CTE. Truy vấn ngoài đọc grade_seven, không phải tự lọc lại cả bảng. Dấu ngoặc đóng kết thúc phần định nghĩa, còn dấu chấm phẩy cuối cùng mới kết thúc cả câu lệnh.

Cách đặt tên nên nói được danh sách chứa gì. Tên grade_seven nhắc rằng mỗi dòng vẫn là một học viên của khối đã chọn. Tên chung chung như data1 buộc người đọc mở lại phần định nghĩa mới hiểu.

Tổng hợp trước rồi ghép với danh sách học viên

Với báo cáo điểm, bước đầu cần tạo một dòng cho mỗi người đã được chấm. Sau đó mới ghép kết quả với danh sách học viên. Đây là cách chia theo ý nghĩa công việc, không phải chia mỗi từ khóa thành một CTE.

Đọc bảng điểm đầu vào để thấy một học viên có thể xuất hiện nhiều lần:

Ta đặt tên best_scores cho bước lấy điểm cao nhất mỗi người. Truy vấn ngoài ghép tên rồi chọn các bạn đạt ít nhất 90 điểm:

WITH best_scores AS (
  SELECT student_id, MAX(score) AS best
  FROM scores
  GROUP BY student_id
)
SELECT s.id, s.name, b.best
FROM students AS s
JOIN best_scores AS b ON b.student_id = s.id
WHERE b.best >= 90
ORDER BY s.id;

An có điểm cao nhất 90, Chi có 100, còn Huy có 90. An được chấm hai lần nhưng chỉ xuất hiện một dòng vì bước tổng hợp đã gom theo mã người. Lan chưa có điểm nên không có dòng trong best_scores để ghép.

Nếu báo cáo cần đủ mọi học viên, hãy thay câu hỏi và cách ghép. CTE vẫn có thể chỉ chứa những người đã có điểm. Chính LEFT JOIN ở bước sau giữ thêm người chưa có dòng tương ứng:

WITH score_counts AS (
  SELECT student_id, COUNT(*) AS attempts, MAX(score) AS best
  FROM scores
  GROUP BY student_id
)
SELECT s.id, s.name, COALESCE(t.attempts, 0) AS attempts,
  t.best
FROM students AS s
LEFT JOIN score_counts AS t ON t.student_id = s.id
ORDER BY s.id;

Lan có attempts = 0 nhưng best = NULL. Không có lần chấm nào thì đếm được không lần, còn chưa có điểm để xác định điểm cao nhất. Đổi cả điểm thiếu thành 0 sẽ làm lẫn người chưa làm với người đã làm và nhận điểm không.

Tên bước không cố định số dòng của các bước về sau. Nếu ghép thêm các lần đăng ký lớp, người có nhiều đăng ký lại có thể xuất hiện nhiều dòng. Sau mỗi phép nối, bạn vẫn cần kiểm một dòng kết quả đang đại diện cho điều gì.

Nối nhiều bước: CTE sau đọc CTE trước

Thầy Nam cần lấy điểm cao nhất, giữ người đạt ít nhất 90, rồi ghép tên. Nếu dùng truy vấn con, ba việc nằm trong các lớp ngoặc sau:

SELECT s.id, s.name, p.best
FROM students AS s
JOIN (
  SELECT student_id, best
  FROM (
    SELECT student_id, MAX(score) AS best
    FROM scores
    GROUP BY student_id
  ) AS best_scores
  WHERE best >= 90
) AS p ON p.student_id = s.id
ORDER BY s.id;

Thay vì lần theo ngoặc từ trong ra ngoài, hãy đọc hai bước có tên từ trên xuống. Dấu phẩy giữa hai khai báo cho phép bước passed đọc kết quả của best_scores:

WITH best_scores AS (
  SELECT student_id, MAX(score) AS best
  FROM scores
  GROUP BY student_id
),
passed AS (
  SELECT student_id, best
  FROM best_scores
  WHERE best >= 90
)
SELECT s.id, s.name, p.best
FROM passed AS p
JOIN students AS s ON s.id = p.student_id
ORDER BY s.id;

Kết quả vẫn là An 90, Chi 100 và Huy 90. best_scores gom các lần chấm của mỗi người trước khi passed lọc theo điểm cao nhất. Truy vấn ngoài chỉ còn việc ghép tên và sắp thứ tự.

Hãy khai báo bước tạo dữ liệu trước bước sử dụng dữ liệu ấy. Trong PostgreSQL với WITH thường, một CTE chỉ đọc được CTE đã khai báo trước nó. SQLite cho phép tham chiếu tới khai báo đứng sau, nhưng thứ tự trên vẫn giúp người đọc theo dõi từng bước.

Một tên CTE còn có thể được dùng hai lần trong cùng câu lệnh. Giả sử cần ghép cặp các bạn khối 7 để làm việc nhóm, hãy đọc cùng danh sách với hai bí danh:

WITH grade_seven AS (
  SELECT id, name
  FROM students
  WHERE grade = 7
)
SELECT a.name AS first_name, b.name AS second_name
FROM grade_seven AS a
JOIN grade_seven AS b ON a.id < b.id
ORDER BY a.id, b.id;

Kết quả chỉ có cặp Bình và Lan. Hai bí danh đọc cùng kết quả CTE, còn điều kiện a.id < b.id tránh ghép một người với chính mình và tránh cặp đảo chiều.

CTE kết thúc cùng câu lệnh chứa nó

Bạn có thể dùng tên CTE nhiều chỗ trong cùng câu lệnh. Tuy nhiên, câu lệnh độc lập tiếp theo không biết tên ấy. Ví dụ dưới đây kết thúc phép đọc đầu rồi thử đọc lại tên vừa khai báo:

WITH short_list AS (
  SELECT id
  FROM students
  WHERE grade = 7
)
SELECT id
FROM short_list;
SELECT id
FROM short_list;

SQLite báo không có bảng short_list ở lần đọc thứ hai. Câu đầu đã hợp lệ, nhưng phạm vi của tên kết thúc tại dấu chấm phẩy. Nếu cần dùng lại trong một câu lệnh khác, bạn khai báo lại bước đó.

CTE giúp trình bày cách tính, nhưng không bảo đảm tính nhanh hơn. Hệ quản trị có thể lựa chọn cách xử lý khác nhau cho cùng một cấu trúc truy vấn. Muốn kết luận về tốc độ, cần đo với dữ liệu và kế hoạch thực thi phù hợp.

• Dùng tên phản ánh điều kiện hoặc phép tổng hợp đã làm.
• Chọn các cột cần dùng, tránh đưa cả bảng vào mà không có mục đích.
• Kiểm kết quả từng bước trước khi thêm phép nối tiếp theo.

Giới hạn này cũng giúp phân biệt CTE với đối tượng được lưu trong cơ sở dữ liệu. Bài 34 sẽ giới thiệu cách lưu định nghĩa truy vấn để dùng lại qua nhiều câu lệnh. Hiện tại, bạn chỉ cần nhớ tên CTE phục vụ câu lệnh đang viết.

CTE đệ quy dùng kết quả trước để sinh bước tiếp

Đệ quy (recursion) là cách tạo bước mới từ kết quả của bước trước. Với CTE đệ quy, WITH RECURSIVE khai báo một phần khởi đầu và một phần sinh tiếp. Ví dụ nhỏ nhất hữu ích là tạo dãy số để đánh thứ tự các buổi học.

Trong ví dụ này, SELECT 1 tạo dòng đầu. Phần sau tăng số vừa có thêm một đơn vị, nhưng chỉ khi số đang xét nhỏ hơn 4:

WITH RECURSIVE lesson_numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1
  FROM lesson_numbers
  WHERE n < 4
)
SELECT n
FROM lesson_numbers
ORDER BY n;

Kết quả gồm các số từ 1 đến 4. lesson_numbers(n) đặt tên cột là n. Ở đây UNION ALL ghép dòng khởi đầu với các dòng sinh thêm, giữ mọi lần xuất hiện. Bài sau sẽ so sánh đầy đủ cách ghép dòng này với các phép tập hợp khác.

Điều kiện xét trên số đang có, trước khi cộng thêm. Vì vậy n < 4 vẫn tạo ra số 4. Nếu thay giới hạn bằng 1, dòng khởi đầu vẫn trả số 1, còn điều kiện n < 1 không sinh thêm dòng.

Điều kiện dừng phải khớp với giá trị được sinh

Nếu đổi điều kiện thành n <= 4, dòng có giá trị 4 vẫn được phép sinh tiếp. Truy vấn dưới đây cho thấy một lỗi vượt giới hạn thường gặp:

WITH RECURSIVE lesson_numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1
  FROM lesson_numbers
  WHERE n <= 4
)
SELECT n
FROM lesson_numbers
ORDER BY n;

Kết quả chứa cả 5, nên không đáp ứng yêu cầu chỉ tới 4. Điều kiện so sánh không sai cú pháp, nhưng đặt sai ranh giới của việc sinh số. Điều kiện n + 1 <= 4 tương đương n < 4: số kế tiếp không được vượt quá 4.

Lần ngược các chủ đề cần học trước

CTE đệ quy còn giúp lần theo quan hệ cha–con giữa các dòng. Trong bảng topics, cột prerequisite_id chỉ mã chủ đề cần học ngay trước chủ đề hiện tại. Giá trị NULL nghĩa là chủ đề đó không yêu cầu chủ đề nào trước nó.

Mục này dùng chuỗi kiến thức của khóa SQL và thêm nhánh WHERE cùng bắt đầu từ SELECT cơ bản. Bảng riêng được tạo như sau để bạn đọc được mỗi mối liên kết:

Bắt đầu từ CTE với độ sâu (depth) bằng 0. Mỗi bước lấy prerequisite_id của dòng vừa tìm để đọc chủ đề cần học trước nó:

WITH RECURSIVE learning_path AS (
  SELECT id, name, prerequisite_id, 0 AS depth
  FROM topics
  WHERE id = 5
  UNION ALL
  SELECT t.id, t.name, t.prerequisite_id, p.depth + 1
  FROM learning_path AS p
  JOIN topics AS t ON t.id = p.prerequisite_id
  WHERE p.depth < 10
)
SELECT id, name, depth
FROM learning_path
ORDER BY depth;

CTE có độ sâu 0, rồi lần lượt tới LEFT JOIN, JOIN, GROUP BY và SELECT cơ bản ở độ sâu 4. Khi tới dòng có prerequisite_id = NULL, phép nối không tìm thấy dòng kế tiếp. Nhánh WHERE không xuất hiện vì nó không nằm trên đường học trước của CTE.

Lỗi thường gặp

Tóm tắt

• CTE đặt tên cho một kết quả để dùng trong câu lệnh hiện tại.
• Chia theo công việc giúp nhìn rõ mỗi bước lọc, tổng hợp và ghép.
• CTE không thay quy tắc giữ dòng của phép nối tiếp theo.
• Đệ quy cần phần khởi đầu, phần sinh tiếp và điều kiện kết thúc phù hợp.
• Dễ đọc hơn chưa chứng minh chạy nhanh hơn.

Kiểm tra nhanh

Đọc thêm

• SQLite: CTE thường và CTE đệ quy.
• PostgreSQL: truy vấn có tên với WITH.