CTE với WITH

Bạn sẽ học được

• Viết CTE bằng WITH và đặt tên cho bước trung gian.
• Dùng lại một CTE nhiều lần trong cùng câu lệnh.
• Kết hợp CTE với JOIN, WHERE và cửa sổ tính toán.
• Hiểu khi nào CTE được "trộn" vào câu chính và khi nào được tính riêng.
• Biết giới hạn: CTE không thay thế được mọi truy vấn con.

Khái niệm cốt lõi

CTE (Common Table Expression) là bảng tạm có tên, khai báo trước câu SELECT chính:

WITH totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
)
SELECT *
FROM totals
WHERE total > 1000000;

Đọc như một câu chuyện: "Gọi totals là tổng tiền mỗi đơn. Từ đó, lấy các đơn trên một triệu." Kết quả 13 dòng.

So với bảng tạm trong FROM, CTE có ba lợi ích rõ rệt:

Khả năng dùng lại là điểm khác biệt thực chất. Ví dụ tính tỷ trọng doanh thu từng đơn trên tổng:

WITH totals AS (
  SELECT o.id, o.order_date,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.order_date
), grand AS (
  SELECT SUM(total) AS all_total FROM totals
)
SELECT t.id, t.total,
       ROUND(100.0 * t.total / g.all_total, 1) AS phan_tram
FROM totals t
CROSS JOIN grand g
ORDER BY t.total DESC
LIMIT 3;

Ba dòng đầu: đơn 13 chiếm 12,9%; đơn 7 chiếm 12,7%; đơn 6 chiếm 9,4% — trên tổng 31.230.000. CTE totals được dùng hai lần: một lần trong grand, một lần ở câu chính. Nếu viết bằng bảng tạm, bạn sẽ phải lặp lại toàn bộ phép gom nhóm.

Cú pháp cần nhớ:

• WITH ten AS ( ... ) — có thể có nhiều CTE, ngăn bằng dấu phẩy.
• CTE sau được tham chiếu CTE trước; CTE trước không thấy CTE sau.
• Câu chính bắt buộc theo sau danh sách CTE.
• CTE chỉ tồn tại trong một câu lệnh. Muốn dùng lại giữa các lần chạy, đó là view hoặc bảng thật.

Một công dụng rất thực tế: vượt qua giới hạn của WHERE. Bạn không thể lọc theo kết quả của một window function trong cùng lớp SELECT vì WHERE chạy trước khi cửa sổ được tính. CTE tách thành hai bước:

WITH ranked AS (
  SELECT id, customer_id,
         SUM(quantity * unit_price) OVER (PARTITION BY order_id) AS total,
         ROW_NUMBER() OVER (ORDER BY order_id) AS rn
  FROM order_items
)
SELECT * FROM ranked WHERE rn <= 3;

Đây là mẫu bạn sẽ dùng liên tục ở chương window functions: tính trong CTE, lọc ở ngoài.

Về hiệu năng, PostgreSQL 12 trở lên mặc định trộn (inline) CTE không đệ quy vào câu chính nếu nó chỉ được dùng một lần — nghĩa là CTE không tự động là "tường chắn tối ưu hoá" như các phiên bản cũ. Nếu bạn cần buộc tính riêng (ví dụ để tránh tính lại một biểu thức đắt), thêm MATERIALIZED:

WITH totals AS MATERIALIZED (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items GROUP BY order_id
)
SELECT * FROM totals WHERE total > 1000000;

Ngược lại, NOT MATERIALIZED cho phép trộn khi bạn muốn optimizer tự do. Đừng thêm hai từ khoá này theo phản xạ — chỉ dùng sau khi đo bằng EXPLAIN ANALYZE.

CTE cũng hỗ trợ đệ quy bằng WITH RECURSIVE, hữu ích cho cây phân cấp hoặc dãy số:

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

Năm dòng 1 đến 5. Với dữ liệu bán lẻ hiện tại chưa cần đệ quy, nhưng biết nó tồn tại giúp bạn nhận ra khi nào bài toán có cấu trúc cây.

Ví dụ chạy được

Doanh thu theo danh mục kèm tỷ trọng:

WITH revenue AS (
  SELECT p.category, SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category
), total AS (
  SELECT SUM(doanh_thu) AS tong FROM revenue
)
SELECT r.category, r.doanh_thu,
       ROUND(100.0 * r.doanh_thu / t.tong, 1) AS phan_tram
FROM revenue r
CROSS JOIN total t
ORDER BY r.doanh_thu DESC;

Bốn dòng: Điện tử 15.180.000 (48,6%); Gia dụng 11.820.000 (37,8%); Thời trang 2.250.000 (7,2%); Sách 1.980.000 (6,3%). Hai CTE, mỗi cái một nhiệm vụ, câu chính chỉ còn việc trình bày.

Khách chi tiêu cao nhất, chỉ tính đơn không huỷ:

WITH spends AS (
  SELECT c.id, c.name,
         SUM(oi.quantity * oi.unit_price) AS spend
  FROM customers c
  JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY c.id, c.name
)
SELECT name, spend
FROM spends
ORDER BY spend DESC
LIMIT 3;

Ba dòng: Bình Trần 6.750.000; Giang Đặng 5.490.000; Chi Lê 4.270.000. Điều kiện o.status <> 'cancelled' đặt trong ON của JOIN — ở đây dùng JOIN thường nên đặt trong ON hay WHERE đều cho cùng kết quả; đặt trong ON giữ thói quen nhất quán với LEFT JOIN.

Kiểm tra lại tổng của CTE:

WITH totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items GROUP BY order_id
)
SELECT COUNT(*) AS so_don, SUM(total) AS tong
FROM totals;

Kết quả 16 | 31230000. Phép đối chiếu này nên chạy sau mỗi CTE tổng hợp — nó phát hiện cả mất dòng lẫn nhân dòng trước khi bạn tin vào các bước sau.

Một CTE có thể được nối như bảng thật:

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items GROUP BY order_id
)
SELECT o.id, o.status, t.total
FROM orders o
JOIN order_totals t ON t.order_id = o.id
WHERE o.status = 'completed'
ORDER BY t.total DESC
LIMIT 3;

Ba đơn hoàn tất lớn nhất: đơn 13 (4.030.000), đơn 7 (3.960.000), đơn 6 (2.950.000). Ba đơn vắng mặt đúng như mong đợi: đơn 5 và 14 đang pending, đơn 10 đã cancelled. CTE không làm thay đổi dữ liệu, chỉ đặt tên cho một bước tính.

Thực hành

1. Viết CTE totals tính tổng mỗi đơn, rồi đếm số đơn trên 1.000.000. Đáp số 13.
2. Thêm CTE thứ hai tính tổng toàn bộ và hiện tỷ trọng của năm đơn lớn nhất. Tổng tỷ trọng phải nhỏ hơn 100.
3. Viết lại câu "khách chi tiêu cao nhất" bằng CTE, giữ nguyên ba dòng kết quả.
4. Dùng WITH RECURSIVE sinh dãy số 1 đến 10, rồi đổi điều kiện dừng để hiểu vai trò của WHERE n < ....
5. Chạy EXPLAIN ANALYZE cho một CTE có và không có MATERIALIZED và so sánh thời gian.

Lỗi thường gặp

Lỗi "relation does not exist" thường xảy ra khi bạn quen đọc từ dưới lên: CTE phải được khai báo trước khi được dùng, giống biến phải khai báo trước khi đọc.

Tóm tắt

• WITH ten AS (...) đặt tên cho bước trung gian, giúp câu lệnh đọc như một quy trình.
• CTE dùng lại được nhiều lần trong cùng câu lệnh — lợi thế lớn so với bảng tạm.
• Dùng CTE để tính window function rồi lọc ở lớp ngoài.
• PostgreSQL 12+ trộn CTE không đệ quy mặc định; MATERIALIZED chỉ dùng khi đã đo.
• WITH RECURSIVE xử lý cấu trúc cây và dãy số, cần điều kiện dừng rõ ràng.