CTE: truy vấn nhiều bước với VỚI

Truy vấn thực có xu hướng tăng: ba JOIN, GROUP BY, truy vấn phụ, một truy vấn phụ khác… và chúng trở nên không thể đọc được. CTE ("Bảng chung Biểu thức", mệnh đề WITH) cho phép bạn đặt tên cho một bước trung gian và sử dụng nó như thể nó là một bảng trong truy vấn chính.

Cú pháp cơ bản

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

CTE hợp lệ chỉ dành cho truy vấn theo sau nó: nó không tồn tại việc hành quyết.

Ví dụ

Tìm 3 khách hàng đã chi tiêu nhiều nhất (tổng số đơn hàng đã giao):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

Đọc bằng tiếng Anh: "đầu tiên hãy tính chi tiêu cho mỗi khách hàng; sau đó kết hợp với khách hàng và chiếm top 3". Nếu không có CTE, bạn sẽ cần một nội tuyến truy vấn phụ trong FROM (hợp pháp nhưng ồn ào hơn).

Nhiều CTE theo tầng

Bạn có thể xác định nhiều CTE được phân tách bằng dấu phẩy và mỗi CTE có thể tham chiếu đến những cái trước:

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average vì average luôn có một hàng: Tích Descartes chỉ thêm total_average vào mỗi hàng của kết quả.

CTE và truy vấn phụ: khi nào nên sử dụng cái nào

CTE tỏa sáng khi:

• cùng một truy vấn phụ được sử dụng hai lần trở lên trong truy vấn chính;
• bạn muốn làm cho cấu trúc rõ ràng trong các bước được đặt tên (tự ghi lại);
• chúng là điều kiện tiên quyết cho CTE đệ quy (WITH RECURSIVE, bài học tiếp theo trong mô-đun này).

Truy vấn con nội tuyến sẽ ngắn gọn hơn đối với một bước chỉ được sử dụng một lần.

Hãy thử nó

Xem lại bài tập