SQL cho phân tích dữ liệu · Hướng dẫn · 30 tháng 10, 2026 · 15 phút đọc

CTE trong SQL (mệnh đề WITH): viết truy vấn thành từng bước dễ đọc

CTE (common table expression, biểu thức bảng chung) là một kết quả tạm thời có tên, chỉ tồn tại trong một truy vấn. Bạn định nghĩa nó bằng WITH name AS (SELECT ...) rồi dùng name trong truy vấn chính như thể đó là một bảng. Bên dưới không có gì mới mà truy vấn con không làm được. Mục đích là dễ đọc: thay vì lồng truy vấn sâu ba cấp, bạn viết chúng từ trên xuống dưới, mỗi CTE một bước, và đặt cho mỗi bước một cái tên nói rõ nó chứa gì.

WITH step_one AS (
  SELECT ...
),
step_two AS (
  SELECT ... FROM step_one ...
)
SELECT ... FROM step_two;

Các ví dụ chạy trên cơ sở dữ liệu hãng hàng không của khóa SQL trên Nomto: 33.121 chuyến bay giữa 104 sân bay trong mùa hè 2026, cùng đơn đặt chỗ, vé, ghế và một chương trình khách hàng thân thiết. Cú pháp là PostgreSQL, và mọi kết quả đều có được bằng cách chạy truy vấn thật.

CTE đầu tiên của bạn

Thành viên chương trình khách hàng thân thiết thường có bao nhiêu vé? Câu hỏi này cần hai cấp tổng hợp: số vé theo từng thành viên, rồi số thành viên theo từng mức số vé. Với CTE, mỗi cấp có một khối riêng:

WITH per_member AS (
  SELECT member_id, COUNT(*) AS tickets
  FROM ticket_members
  GROUP BY member_id
)
SELECT tickets, COUNT(*) AS members
FROM per_member
GROUP BY tickets
ORDER BY tickets
LIMIT 8;

per_member chứa một dòng cho mỗi thành viên có vé. Truy vấn chính đọc nó như một bảng. Đúng logic này cũng viết được bằng bảng dẫn xuất (FROM (SELECT ...) AS per_member), và với một bước thì như vậy cũng ổn. Khác biệt chỉ lộ ra khi có nhiều bước.

(Chỗ số liệu tăng vọt ở mức bảy và tám vé, sau khi các con số đang giảm dần, là đặc điểm của dữ liệu chứ không phải lỗi truy vấn. Nó đáng được xem kỹ trước khi ai đó dựng biểu đồ dựa trên nó, và một truy vấn hai bước như thế này thường chính là cách bạn phát hiện những điều như vậy.)

Nhiều CTE: mỗi bước một ý

Hệ số lấp đầy trung bình (số hành khách chia cho số ghế) của các chuyến đã hạ cánh, theo từng mẫu máy bay, là bao nhiêu? Cần ba thành phần: số ghế mỗi mẫu, số hành khách mỗi chuyến, và bản thân các chuyến bay. Mỗi thành phần thành một CTE, và CTE sau có thể dùng CTE trước:

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats
  FROM seats
  GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax
  FROM ticket_flights
  GROUP BY flight_id
),
flight_load AS (
  SELECT f.flight_id,
         f.aircraft_code,
         COALESCE(p.pax, 0) AS pax,
         s.seats,
         COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
  LEFT JOIN passengers p ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
)
SELECT aircraft_code,
       COUNT(*) AS flights,
       MAX(seats) AS seats,
       ROUND(AVG(pax), 1) AS avg_pax,
       ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC;

Mỗi CTE làm một việc, và câu SELECT cuối chỉ tóm tắt. Tổng hợp trước số ghế và số hành khách trước khi JOIN cũng là điều giữ cho các con số đúng: JOIN flights với bảng seats và ticket_flights chưa tổng hợp sẽ nhân dòng lên và thổi phồng mọi số đếm.

Đây là cùng truy vấn đó viết bằng các bảng dẫn xuất lồng nhau (rút gọn còn ba dòng đầu):

SELECT aircraft_code, COUNT(*) AS flights, ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM (
  SELECT f.aircraft_code, COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN (SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code) s
    ON s.aircraft_code = f.aircraft_code
  LEFT JOIN (SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id) p
    ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
) AS flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC
LIMIT 3;

Cùng một câu trả lời. Nhưng bạn phải đọc từ trong ra ngoài, và các bước không có tên. Khi review code, nói “kiểm tra passengers” dễ hơn nhiều so với “kiểm tra truy vấn con thứ hai bên trong truy vấn con thứ nhất”.

Gỡ lỗi chuỗi CTE từng bước

Lợi thế thật sự của các bước có tên: bạn có thể xem bất kỳ bước nào bằng cách thay câu SELECT cuối. Thay nó bằng SELECT * FROM flight_load LIMIT 20, hoặc bằng một phép đếm, và bạn thấy chính xác bước đó tạo ra gì.

Thói quen đó bắt được những lỗi như sau. Đổi LEFT JOIN passengers thành JOIN thường, và hệ số lấp đầy tăng vọt:

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id
)
SELECT f.aircraft_code,
       COUNT(*) AS flights,
       ROUND(100 * AVG(p.pax * 1.0 / s.seats), 1) AS avg_load_pct
FROM flights f
JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
JOIN passengers p ON p.flight_id = f.flight_id
WHERE f.status = 'Arrived'
GROUP BY f.aircraft_code
ORDER BY avg_load_pct DESC;

Hệ số lấp đầy của chiếc Cessna tăng từ 14% lên 50%, vì 3.328 chuyến bay không có khách của nó đã âm thầm bị loại. Kiểm tra nhanh bước passengers cho thấy lý do: trong 16.707 chuyến đã hạ cánh, chỉ 11.438 chuyến có vé. INNER JOIN chỉ giữ lại những chuyến đó. Bản thân không phiên bản nào là câu trả lời “duy nhất đúng”, nhưng bạn cần biết mình đang trả lời câu hỏi nào, và các bước có tên biến việc kiểm tra đó thành một dòng lệnh.

Dùng một CTE nhiều lần

Một CTE có thể được tham chiếu nhiều lần trong truy vấn chính, điều mà bảng dẫn xuất không làm được nếu không sao chép lại. Cách dùng kinh điển: một giá trị và tỷ trọng của nó trên tổng.

WITH route_revenue AS (
  SELECT f.departure_airport || '-' || f.arrival_airport AS route,
         SUM(tf.amount) AS revenue
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE f.status = 'Arrived'
  GROUP BY route
)
SELECT route,
       ROUND(revenue) AS revenue,
       ROUND(100 * revenue / (SELECT SUM(revenue) FROM route_revenue), 2) AS pct_of_total
FROM route_revenue
ORDER BY revenue DESC
LIMIT 5;

route_revenue được dùng hai lần: làm bảng chính và bên trong truy vấn con vô hướng tính tổng. Trên toàn bộ 451 tuyến, các chuyến đã hạ cánh mang về 114,3 triệu đô la, và tuyến đứng đầu, Domodedovo đi Khabarovsk, chiếm gần 4% trong số đó.

CTE cũng có thể chứa một bản tóm tắt một dòng để JOIN với mọi dòng. Những ngày đặt chỗ nào đông nhất so với một ngày trung bình?

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  GROUP BY day
),
stats AS (
  SELECT AVG(bookings) AS avg_bookings FROM daily
)
SELECT d.day, d.bookings, ROUND(d.bookings / s.avg_bookings, 2) AS vs_avg
FROM daily d
CROSS JOIN stats s
ORDER BY d.bookings DESC
LIMIT 5;

stats đọc từ daily, còn truy vấn chính đọc từ cả hai. (Giá trị trung bình, 4.692,6 đơn mỗi ngày, được tính trên 56 ngày từ 17/6 đến 11/8; ngày cuối chỉ có một phần trong dữ liệu, nên kéo giá trị trung bình xuống một chút. Với kiểu so sánh này, bạn thường nên bỏ những ngày chưa đủ dữ liệu.)

Đặt tên cột cho CTE

Bạn có thể liệt kê tên cột ngay sau tên CTE. Chúng sẽ thay thế tên cột mà truy vấn bên trong đặt:

WITH model_seats (code, seat_count) AS (
  SELECT aircraft_code, COUNT(*)
  FROM seats
  GROUP BY aircraft_code
)
SELECT code, seat_count
FROM model_seats
ORDER BY seat_count DESC
LIMIT 3;

Đa số mọi người đặt bí danh ngay trong SELECT (COUNT(*) AS seat_count). Danh sách cột là lựa chọn gọn gàng khi truy vấn bên trong có những biểu thức chưa đặt tên, và nó cho thấy “giao diện” của CTE ngay ở dòng đầu tiên.

CTE đệ quy

WITH RECURSIVE cho phép một CTE tham chiếu đến chính nó. Nó gồm hai phần nối với nhau bằng UNION hoặc UNION ALL: phần neo (anchor) tạo ra các dòng khởi đầu, và phần đệ quy tạo ra dòng mới từ các dòng của vòng trước. Nó dừng lại khi một vòng không tạo ra gì mới.

Ví dụ nhỏ nhất là sinh dãy số:

WITH RECURSIVE n AS (
  SELECT 1 AS i
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;

WHERE i < 5 là điều kiện dừng. Không có nó, truy vấn sẽ chạy mãi. (Với dãy số và dãy ngày, generate_series đơn giản hơn; xem bài về date spine.)

Đệ quy phát huy tác dụng với đồ thị và cấu trúc phân cấp. Cần bao nhiêu chuyến bay để đi từ Yuzhno-Sakhalinsk (UUS) đến các sân bay khác trong mạng lưới?

WITH RECURSIVE routes AS MATERIALIZED (
  SELECT DISTINCT departure_airport AS src, arrival_airport AS dst
  FROM flights
),
reach AS (
  SELECT 'UUS'::VARCHAR AS airport, 0 AS hops
  UNION
  SELECT r.dst, reach.hops + 1
  FROM reach
  JOIN routes r ON r.src = reach.airport
  WHERE reach.hops < 4
),
best AS (
  SELECT airport, MIN(hops) AS hops
  FROM reach
  GROUP BY airport
)
SELECT hops, COUNT(*) AS airports
FROM best
GROUP BY hops
ORDER BY hops;

Từ UUS có bốn điểm đến bay thẳng, thêm 76 sân bay cần một lần nối chuyến, và 23 sân bay còn lại cần ba chuyến bay (hai lần nối chuyến). Không nơi nào cần đến chuyến thứ tư: phép tìm kiếm được phép đi sâu bốn bước nhưng không tìm thấy gì mới sau bước thứ ba, và 1 + 4 + 76 + 23 là đủ cả 104 sân bay, nên trong lịch bay này mọi sân bay đều đến được từ UUS.

Bốn chi tiết giúp truy vấn này an toàn và ổn định:

• WITH RECURSIVE chỉ viết một lần, ở đầu, dù chỉ có reach là đệ quy. Các CTE thông thường (routes, best) có thể nằm chung trong cùng một WITH.
• UNION (không phải UNION ALL) loại bỏ các dòng (airport, hops) trùng nhau, giúp số dòng không bùng nổ khi nhiều đường đi cùng dẫn tới một sân bay.
• routes AS MATERIALIZED buộc danh sách tuyến bay được tính một lần trước khi đệ quy bắt đầu. Trên DuckDB (động cơ của Sân tập), nếu bỏ từ khóa này, cùng truy vấn đó có lúc dừng sớm và chỉ trả về hai dòng đầu; với MATERIALIZED, kết quả luôn ổn định.
• hops < 4 là giới hạn độ sâu cứng. Trên một đồ thị có chu trình (và các tuyến bay đều có chiều đi lẫn chiều về), đệ quy không giới hạn có thể chạy mãi.

CTE, truy vấn con, bảng tạm hay view?

Hãy dùng CTE cho các bước bên trong một phân tích. Nếu cùng một kết quả trung gian phục vụ nhiều truy vấn riêng rẽ, bảng tạm giúp khỏi phải tính lại. Nếu cả nhóm cần dùng logic đó, nó nên nằm trong một view hoặc một model trong pipeline dữ liệu của bạn.

CTE làm truy vấn nhanh hơn hay chậm hơn?

Mặc định thì không nhanh hơn cũng không chậm hơn. Trong PostgreSQL 12 trở lên, một CTE không đệ quy được tham chiếu một lần sẽ được nhúng thẳng vào truy vấn chính, y như truy vấn con, và bộ tối ưu hóa nhìn xuyên qua nó. Một CTE được tham chiếu nhiều lần thường được tính một lần rồi dùng lại.

Bạn có thể thay đổi lựa chọn đó. WITH big AS MATERIALIZED (...) buộc PostgreSQL tính CTE một lần và giữ kết quả, hữu ích khi CTE tốn kém và bộ tối ưu hóa cứ tính lại nó. NOT MATERIALIZED buộc nhúng thẳng, để các bộ lọc từ truy vấn chính được đẩy vào bên trong. DuckDB cũng chấp nhận các từ khóa MATERIALIZED và NOT MATERIALIZED.

Lời khuyên cũ “CTE là hàng rào tối ưu hóa” xuất phát từ PostgreSQL 11 trở về trước, khi CTE luôn được vật chất hóa. Nếu bạn đọc thấy điều đó trong một bài blog cũ, đó là lý do.

Lưu ý về tính tương thích

• PostgreSQL, DuckDB, MySQL 8+, SQLite, BigQuery, Snowflake đều hỗ trợ WITH và WITH RECURSIVE.
• SQL Server hỗ trợ CTE và đệ quy nhưng không dùng từ RECURSIVE: bạn chỉ viết WITH. Nó cũng cần dấu chấm phẩy trước WITH nếu câu lệnh trước đó chưa kết thúc bằng dấu chấm phẩy, đó là lý do bạn thấy ;WITH trong code SQL Server.
• CTE sửa đổi dữ liệu (WITH deleted AS (DELETE ... RETURNING *) SELECT ...) là tính năng của PostgreSQL. DuckDB và phần lớn các hệ quản trị khác chỉ cho phép SELECT bên trong CTE.
• Phép ép kiểu 'UUS'::VARCHAR trong phần neo cố định kiểu dữ liệu của cột cho cả hai phần của đệ quy. PostgreSQL đòi hỏi kiểu dữ liệu giữa phần neo và phần đệ quy phải khớp nhau; CAST('UUS' AS VARCHAR) là cách viết tương thích mọi nơi.

Các lỗi thường gặp

1. Dấu phẩy sau CTE cuối cùng. Các CTE ngăn cách nhau bằng dấu phẩy, nhưng giữa CTE cuối và câu SELECT chính không có dấu phẩy. ), SELECT ... là lỗi cú pháp.
2. Viết WITH hai lần. Một câu lệnh chỉ có một WITH; các CTE tiếp theo đứng sau dấu phẩy: WITH a AS (...), b AS (...).
3. Mong CTE còn tồn tại sau câu lệnh. Sau dấu chấm phẩy, cái tên đó biến mất. Nếu truy vấn tiếp theo cần nó, hãy lặp lại CTE hoặc tạo bảng tạm.
4. Dựa vào ORDER BY bên trong CTE. Nó không cố định thứ tự của kết quả cuối cùng. Hãy sắp xếp trong truy vấn chính.
5. CTE đệ quy không có điều kiện dừng. Luôn giới hạn độ sâu hoặc đảm bảo rằng đến một lúc nào đó mỗi vòng không trả về dòng nào. UNION thay vì UNION ALL có ích khi các đường đi quay lại cùng những dòng cũ.
6. Một chuỗi dài không ai kiểm tra. Bảy CTE chỉ dễ đọc khi từng CTE đều đúng. Hãy xem số dòng của mỗi bước khi bạn xây dựng chuỗi, đặc biệt là sau các phép JOIN.

Nếu bạn muốn xem các dạng truy vấn con mà CTE thay thế, hãy đọc bài truy vấn con trong SQL. Nhiều chuỗi CTE phân tích kết thúc bằng một hàm cửa sổ; bài hàm cửa sổ trong SQL và bài về tỷ lệ giữ chân theo cohort có những chuỗi thực tế dài hơn.

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

CTE trong SQL là gì?

Là biểu thức bảng chung (common table expression): một kết quả tạm thời có tên, được định nghĩa bằng WITH name AS (SELECT ...) và được dùng bởi truy vấn ngay sau đó. Nó chỉ tồn tại trong một câu lệnh đó.

CTE và truy vấn con khác nhau thế nào?

Về logic thì không khác: một CTE dùng một lần hoạt động như một bảng dẫn xuất. Khác biệt nằm ở thực tế sử dụng. CTE có tên, đứng ở đầu nên truy vấn đọc được theo thứ tự, có thể được tham chiếu nhiều lần và có thể đệ quy.

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

Có. Viết WITH một lần và ngăn cách các CTE bằng dấu phẩy. Mỗi CTE có thể dùng những CTE được định nghĩa trước nó.

CTE có chậm hơn truy vấn con không?

Không, trong PostgreSQL và DuckDB hiện nay: một CTE dùng một lần được nhúng thẳng như truy vấn con. Trong PostgreSQL 11 trở về trước, CTE luôn được vật chất hóa, điều này có thể chặn một số tối ưu hóa. Hãy dùng MATERIALIZED hoặc NOT MATERIALIZED nếu bạn cần kiểm soát.

CTE đệ quy dùng để làm gì?

Cho cấu trúc phân cấp và đồ thị: sơ đồ tổ chức, cây danh mục, đường bay giữa các sân bay, chuỗi sự kiện. Nó lặp đi lặp lại một truy vấn trên chính kết quả trước đó của mình cho đến khi không còn dòng mới nào xuất hiện.

Ngôn ngữ khác: English, Español, Português (Brasil), 简体中文