Nhiều CTE lồng nhau

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

• Khai báo nhiều CTE trong một câu lệnh và hiểu thứ tự tham chiếu.
• Xây pipeline phân tích theo từng bước có tên.
• Kết hợp CTE với window function để lọc theo hạng.
• Gỡ lỗi bằng cách chạy từng CTE riêng.
• Nhận biết khi nào pipeline quá dài nên tách thành view.

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

Một CTE tốt, nhiều CTE tốt hơn — miễn là mỗi cái có một nhiệm vụ. Câu lệnh dài nhất trong phân tích thường có dạng:

WITH buoc_1 AS ( ... ),
     buoc_2 AS ( ... FROM buoc_1 ... ),
     buoc_3 AS ( ... FROM buoc_2 ... )
SELECT ... FROM buoc_3;

Quy tắc thứ tự: CTE chỉ thấy những CTE khai báo trước nó. buoc_2 đọc được buoc_1; buoc_1 không thấy buoc_2. Đây là lý do pipeline luôn chảy một chiều — giống các bước trong một quy trình, không phải một mớ truy vấn gọi nhau tuỳ ý.

Vì sao chia nhỏ? Vì mỗi bước là một phép kiểm tra được. Khi kết quả cuối sai, bạn chạy riêng từng CTE để tìm bước hỏng:

WITH base AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items GROUP BY order_id
)
SELECT * FROM base ORDER BY total DESC LIMIT 5;

Nếu base đúng mà kết quả cuối sai, lỗi nằm ở bước sau. Nếu base sai, bạn không cần đọc các bước còn lại. Cách gỡ lỗi này nhanh hơn nhiều so với việc nhìn một khối SELECT lồng năm tầng ngoặc.

Một mẫu pipeline hay gặp trong phân tích:

Điểm cần chú ý: window function không dùng được trong WHERE cùng lớp, nên bước ranked buộc phải là CTE (hoặc bảng tạm) trước khi lọc WHERE r <= 5. Đây là lý do tự nhiên nhất khiến pipeline có bốn bước thay vì ba.

Về đặt tên: dùng danh từ mô tả nội dung của bước, không phải số thứ tự. order_totals, customer_spend, top_orders dễ đọc hơn cte1, cte2, cte3. Khi câu lệnh dài 40 dòng, tên tốt là thứ duy nhất giúp người đọc lần theo logic.

Đừng lạm dụng. Nếu một CTE chỉ được dùng một lần và chỉ có ba dòng, nó có thể là một bảng tạm trong FROM — hoặc gộp luôn vào câu chính. Ngưỡng hợp lý: CTE khi bước đó được dùng lại, cần đặt tên để dễ đọc, hoặc cần tách khỏi giới hạn mệnh đề. Ngoài ba lý do đó, thêm CTE chỉ làm câu lệnh dài hơn.

Ví dụ chạy được

Top 5 đơn hàng lớn nhất, qua ba bước:

WITH base AS (
  SELECT o.id, o.customer_id,
         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.customer_id
), ranked AS (
  SELECT *, RANK() OVER (ORDER BY total DESC) AS r
  FROM base
)
SELECT id, customer_id, total, r
FROM ranked
WHERE r <= 5
ORDER BY r;

Năm dòng: đơn 13 (4.030.000, hạng 1); đơn 7 (3.960.000, hạng 2); đơn 6 (2.950.000, hạng 3); đơn 16 (2.580.000, hạng 4); đơn 9 (2.150.000, hạng 5). Không có đồng hạng nên RANK và ROW_NUMBER cho cùng kết quả — chương sau sẽ nói rõ khi nào chúng khác nhau.

Pipeline theo khách: tổng chi tiêu, số đơn, rồi xếp hạng:

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
), customer_totals AS (
  SELECT o.customer_id,
         COUNT(*) AS orders,
         SUM(t.total) AS spend
  FROM orders o
  JOIN order_totals t ON t.order_id = o.id
  GROUP BY o.customer_id
), ranked AS (
  SELECT c.name, ct.orders, ct.spend,
         RANK() OVER (ORDER BY ct.spend DESC) AS r
  FROM customer_totals ct
  JOIN customers c ON c.id = ct.customer_id
)
SELECT name, orders, spend, r
FROM ranked
WHERE r <= 3;

Ba dòng: Bình Trần (3 đơn, 6.750.000, hạng 1); Giang Đặng (2 đơn, 5.490.000, hạng 2); Chi Lê (2 đơn, 4.270.000, hạng 3).

Điều đáng chú ý: order_totals và customer_totals tách đúng theo hạt. Nếu gộp làm một — nối orders với order_items rồi gom theo khách — bạn vẫn ra tổng đúng, nhưng COUNT(*) sẽ đếm dòng hàng chứ không phải đơn, và phải nhớ COUNT(DISTINCT o.id). Tách theo bước khiến mỗi phép đếm rõ hạt của nó.

Doanh thu danh mục kèm hạng và tỷ trọng, bốn CTE:

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

Bốn dòng: Điện tử 15.180.000 (48,6%, hạng 1); Gia dụng 11.820.000 (37,8%, hạng 2); Thời trang 2.250.000 (7,2%, hạng 3); Sách 1.980.000 (6,3%, hạng 4).

Kiểm chứng từng bước — chạy riêng category_revenue và cộng lại:

WITH category_revenue AS (
  SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category
)
SELECT COUNT(*) AS so_danh_muc, SUM(revenue) AS tong
FROM category_revenue;

Kết quả 4 | 31230000. Nếu tổng khác 31.230.000, lỗi nằm ngay ở bước đầu và bạn không cần đọc ba bước còn lại.

Thực hành

1. Viết pipeline base → ranked cho top 5 đơn. Đáp số: 4.030.000; 3.960.000; 2.950.000; 2.580.000; 2.150.000.
2. Thêm bước customer_totals và trả về tên khách thay vì customer_id. Ba khách đầu: Bình Trần, Giang Đặng, Chi Lê.
3. Chạy riêng từng CTE với SELECT * ... LIMIT 5 để xem dữ liệu ở mỗi bước. Ghi lại số dòng của từng bước: order_totals 16, customer_totals 11.
4. Viết pipeline bốn bước cho doanh thu danh mục và xác nhận tổng 31.230.000.
5. Cố tình đặt WHERE r <= 3 vào cùng lớp SELECT ... RANK() OVER (...) và đọc thông báo lỗi. Ghi lại để nhớ vì sao cần CTE.

Lỗi thường gặp

Lỗi "ít dòng hơn mong đợi" thường đến từ bước nối khách: JOIN loại khách chưa mua. Nếu câu hỏi là "mọi khách", dùng LEFT JOIN và COALESCE — như bài tập cuối chương trước đã chỉ ra.

Tóm tắt

• Nhiều CTE nối tiếp tạo pipeline đọc từ trên xuống; CTE chỉ thấy các CTE trước nó.
• Mỗi bước một nhiệm vụ, một hạt rõ ràng — dễ kiểm tra và dễ gỡ lỗi.
• Window function phải nằm trong CTE trước khi lọc theo hạng.
• Chạy riêng từng CTE để khoanh vùng lỗi.
• Đặt tên theo nội dung, và chỉ tách bước khi thật sự cần.