Giải thích về CTE (Common Table Expressions) trong PostgreSQL.

Question

Vương Vĩnh · Accepted Answer

Common Table Expressions (CTEs) là một tính năng mạnh mẽ trong PostgreSQL (và nhiều hệ quản trị cơ sở dữ liệu khác) cho phép bạn định nghĩa các truy vấn tạm thời có thể được tham chiếu trong các câu lệnh SQL. CTE giúp cải thiện tính đọc và tổ chức của mã SQL, đặc biệt khi làm việc với các truy vấn phức tạp.
Cú pháp CTE
Cú pháp cơ bản của một CTE trong PostgreSQL như sau:
WITH cte_name AS (
    -- Truy vấn SQL
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;
Các điểm nổi bật về CTE
Tăng tính đọc và bảo trì:
CTE giúp bạn chia nhỏ truy vấn phức tạp thành các phần nhỏ hơn, dễ hiểu hơn.
Sử dụng nhiều lần:
CTE có thể được tham chiếu nhiều lần trong câu lệnh SQL, giúp giảm thiểu việc viết lại mã.
Hỗ trợ đệ quy:
CTE có thể được định nghĩa đệ quy, cho phép bạn thực hiện các truy vấn phức tạp hơn, như tìm kiếm trong cây hay đồ thị.
Ví dụ CTE
Dưới đây là một ví dụ đơn giản về CTE trong PostgreSQL:
WITH sales_data AS (
    SELECT salesperson_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
)
SELECT sd.salesperson_id, sd.total_sales
FROM sales_data sd
WHERE sd.total_sales > 10000;
CTE Đệ quy
Dưới đây là một ví dụ về CTE đệ quy, thường được sử dụng để truy vấn dữ liệu cây hoặc đồ thị:
WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL  -- Bắt đầu từ người quản lý cấp cao nhất
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy;
Kết luận
CTEs là công cụ hữu ích trong PostgreSQL giúp tối ưu hóa việc viết truy vấn và tổ chức mã. Chúng cung cấp sự linh hoạt và khả năng mở rộng cho các tác vụ truy vấn phức tạp, đồng thời giúp mã dễ đọc và bảo trì hơn.