Khi nào sử dụng WITH để tạo CTE trong PostgreSQL?

Question

Nguyễn Nhật Hoàng · Accepted Answer

Trong PostgreSQL, Common Table Expressions (CTE) được tạo ra bằng cách sử dụng từ khóa WITH. CTE là một phần hữu ích trong việc viết truy vấn phức tạp và có thể mang lại nhiều lợi ích. Dưới đây là những tình huống mà bạn nên sử dụng WITH để tạo CTE:
1. Khi Viết Các Truy Vấn Phức Tạp
Nếu bạn có một truy vấn phức tạp với nhiều bước tính toán hoặc các truy vấn con, việc sử dụng CTE sẽ giúp mã truy vấn trở nên dễ đọc hơn. Bạn có thể tách biệt các phần khác nhau của truy vấn thành các khối riêng biệt.
WITH sales_data AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT p.product_name, sd.total_sales
FROM products p
JOIN sales_data sd ON p.product_id = sd.product_id;
2. Khi Cần Sử Dụng Kết Quả Nhiều Lần
Nếu bạn cần sử dụng cùng một tập hợp dữ liệu nhiều lần trong một truy vấn, sử dụng CTE có thể giúp giảm thiểu việc lặp lại mã và cải thiện hiệu suất.
WITH recent_orders AS (
    SELECT customer_id, order_date
    FROM orders
    WHERE order_date > NOW() - INTERVAL '30 days'
)
SELECT COUNT(*) FROM recent_orders;
SELECT AVG(order_date) FROM recent_orders;
3. Khi Xử Lý Đệ Quy
CTE hỗ trợ đệ quy, cho phép bạn thực hiện các truy vấn đệ quy trên các cấu trúc dữ liệu phân cấp (như cây hoặc đồ thị). Đây là một trong những ứng dụng mạnh mẽ của CTE.
WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart;
4. Khi Cần Tính Toán Tạm Thời
CTE có thể được sử dụng để tính toán các giá trị tạm thời mà bạn không muốn tạo bảng tạm trong cơ sở dữ liệu. Điều này giúp cải thiện hiệu suất mà không cần tạo ra các đối tượng lâu dài trong cơ sở dữ liệu.
5. Khi Tối Ưu Hóa Hiệu Suất
Trong một số trường hợp, sử dụng CTE có thể giúp tối ưu hóa hiệu suất của truy vấn, đặc biệt là khi các truy vấn con có thể được tối ưu hóa một cách độc lập trước khi kết hợp với các truy vấn khác.
6. Khi Cần Tăng Tính Đọc Dễ
Sử dụng CTE giúp cải thiện tính đọc dễ của mã SQL, làm cho nó dễ hiểu hơn cho những người khác đọc hoặc bảo trì mã sau này.
Kết Luận
Sử dụng WITH để tạo CTE trong PostgreSQL là một công cụ mạnh mẽ giúp bạn viết các truy vấn phức tạp một cách rõ ràng và hiệu quả hơn. Bạn nên cân nhắc sử dụng CTE khi làm việc với các truy vấn phức tạp, đệ quy hoặc khi bạn cần sử dụng lại các tập hợp dữ liệu trong một truy vấn.