Menu

CTE trong PostgreSQL: truy vấn WITH và WITH RECURSIVE

Biểu thức bảng chung (CTE) đặt tên cho một truy vấn con trong mệnh đề WITH để truy vấn chính đọc nó như một bảng. WITH RECURSIVE cho phép nó tự tham chiếu để duyệt cây và đồ thị.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

Biểu thức bảng chung (common table expression, CTE) là một truy vấn con có tên trong mệnh đề WITH đứng trước câu lệnh chính: WITH name AS (SELECT ...) SELECT ... FROM name. Truy vấn chính đọc CTE như một bảng, nên các truy vấn con lồng nhau trở thành từng bước bạn đọc từ trên xuống dưới.

CTE totals cộng các đơn hàng của từng khách hàng, và truy vấn chính giữ lại những khách hàng trên 100: cy với 300 và ana với 180. CTE chỉ tồn tại trong câu lệnh chứa nó.

Nối nhiều CTE

Ngăn cách các CTE bằng dấu phẩy. Mỗi CTE có thể đọc những CTE được định nghĩa trước nó.

average đọc totals, còn truy vấn chính đọc cả hai. Tổng của các khách hàng là 180, 70 và 300, trung bình khoảng 183, nên chỉ cy vượt trên mức đó.

Duyệt cây với WITH RECURSIVE

WITH RECURSIVE cho phép một CTE tham chiếu tới chính nó. Nó có hai phần nối với nhau bằng UNION ALL:

  • truy vấn neo (anchor), trả về các hàng bắt đầu;
  • truy vấn đệ quy, join CTE với bảng để tìm cấp tiếp theo.

PostgreSQL chạy phần đệ quy trên các hàng mà bước trước tạo ra, và dừng khi một bước không trả về hàng nào.

Mỗi bước tìm những người báo cáo cho các hàng của bước trước. Mia kết thúc ở độ sâu 3 với đường dẫn Rosa > Omar > Ivo > Mia. Để liệt kê mọi người dưới quyền một quản lý thay vì cả cây, hãy đổi WHERE của truy vấn neo thành id = 2.

Để đi ngược lên, từ một nhân viên tới cấp cao nhất, hãy đổi điều kiện join thành s.id = c.manager_id.

Dừng một CTE đệ quy vô hạn

Một CTE đệ quy chỉ kết thúc khi một bước không trả về gì. Với dữ liệu có vòng (A báo cáo cho B, B báo cáo cho A), nó không bao giờ kết thúc. Hãy thêm giới hạn độ sâu như WHERE c.depth < 20, hoặc dùng mệnh đề CYCLE (PostgreSQL 14+), mệnh đề này đánh dấu hàng lặp lại đầu tiên và không đi tiếp theo nó nữa:

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM staff WHERE id = 5
  UNION ALL
  SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;

Sinh hàng bằng CTE đệ quy

Phần đệ quy không cần một bảng. Ví dụ này đếm từ 1 đến 5 bằng cách đưa giá trị của mỗi hàng quay lại:

Với dãy số hoặc dãy ngày đơn giản, generate_series ngắn gọn hơn.

Di chuyển hàng bằng CTE sửa dữ liệu

Một CTE có thể là INSERT, UPDATE hoặc DELETE với mệnh đề RETURNING, và câu lệnh chính đọc các hàng được trả về. Ví dụ này chuyển các đơn hàng cũ vào bảng lưu trữ chỉ trong một câu lệnh:

Đơn hàng 1 và 3 bị xóa và được chèn vào bảng lưu trữ cùng lúc, nên nếu có lỗi, cả hai bảng vẫn giữ nguyên. Mọi phần của câu lệnh thấy cùng một snapshot: truy vấn chính không thấy tác động của DELETE lên orders, chỉ thấy những gì RETURNING chuyển tiếp (xem RETURNING).

Điều khiển inline với MATERIALIZED

Từ PostgreSQL 12, một CTE không có tác dụng phụ mà truy vấn chính tham chiếu một lần sẽ được inline. Bộ lập kế hoạch tối ưu nó cùng với truy vấn bên ngoài, nên một WHERE viết bên ngoài CTE vẫn có thể dùng index. Một CTE được tham chiếu từ hai lần trở lên sẽ được tính một lần và kết quả được dùng lại. Bạn có thể ép một trong hai cách:

WITH recent AS MATERIALIZED (
  SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;

WITH recent AS NOT MATERIALIZED (...)

MATERIALIZED tính CTE một lần, riêng biệt, và không đưa WHERE bên ngoài vào trong. Hãy dùng nó khi kế hoạch inline hóa ra chậm hơn. NOT MATERIALIZED yêu cầu inline kể cả khi CTE được tham chiếu nhiều lần. CTE đệ quy và CTE sửa dữ liệu luôn được tính riêng.

Câu hỏi thường gặp

CTE có nhanh hơn truy vấn con trong PostgreSQL không?

Thường thì không bên nào nhanh hơn. Từ PostgreSQL 12, một CTE được tham chiếu một lần và không có tác dụng phụ sẽ được inline vào truy vấn chính, nên nó có cùng kế hoạch thực thi với truy vấn con tương đương. Trước phiên bản 12, mọi CTE đều được tính riêng, điều này có thể khiến index không được dùng.

Có dùng CTE trong UPDATE hoặc DELETE được không?

Được. WITH có thể đứng trước INSERT, UPDATE, DELETE và MERGE, và bản thân CTE có thể là một INSERT, UPDATE hoặc DELETE có RETURNING, câu lệnh chính sẽ đọc các hàng mà nó trả về.

Làm sao dừng vòng lặp vô hạn trong CTE đệ quy?

Cho phần đệ quy một điều kiện mà cuối cùng sẽ không trả về hàng nào, ví dụ giới hạn độ sâu (WHERE depth < 10). Với dữ liệu có thể vòng lại chính nó, mệnh đề CYCLE (PostgreSQL 14+) đánh dấu hàng bị lặp lại và dừng ở đó.

Một CTE có dùng được trong nhiều truy vấn không?

Không. CTE chỉ tồn tại trong câu lệnh chứa nó. Để dùng lại một truy vấn giữa nhiều câu lệnh, hãy tạo một view hoặc một bảng tạm.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu