BẢNG TẠM POSTGRES (PostgreSQL common table expression)
BẢNG TẠM POSTGRES (PostgreSQL common table expression)
- Giới thiệu
Biểu thức bảng chung (common table expression) cho phép ta tạo một tập kết quả tạm thời với query.
No giúp ta tạo các truy vấn phức tạp một cách dễ hiểu và có tổ chức hơn. Nó được sử dụng để định nghĩa một bảng tạm thời trong phạm vi của một truy vấn SELECT, INSERT, UPDATE, hoặc DELETE.
2. Tại sao cần CTE?
- Tăng tính dễ đọc: Giúp tổ chức truy vấn phức tạp thành các phần dễ hiểu hơn.
- Tái sử dụng kết quả trung gian: Bạn có thể sử dụng các kết quả từ CTE trong cùng một truy vấn mà không phải viết lại chúng.
- Giảm phụ thuộc vào các bảng tạm thời: Thay vì phải tạo bảng tạm thời, CTE giúp định nghĩa bảng "ảo" trực tiếp trong truy vấn.
3. Cấu trúc của một CTE
WITH cte_name (column1, column2, ...) AS (
-- CTE query
SELECT ...
)
-- Main query using the CTE
SELECT ...
FROM cte_name;
- WITH: Khai báo rằng đang bắt đầu định nghĩa một CTE.
- cte_name: Tên của CTE, có thể sử dụng nó giống như tên một bảng.
- Bên trong () là truy vấn con (subquery) sẽ trả về kết quả dùng trong truy vấn chính.
- Column List (optional): danh sách list các column, nếu không được liệt kê thì các column sẽ ăn theo SELECT statement ở bên trong CTE
4. Cte example
WITH action_films AS (
SELECT
f.title,
f.length
FROM
film f
INNER JOIN film_category fc USING (film_id)
INNER JOIN category c USING(category_id)
WHERE
c.name = 'Action'
)
SELECT \* FROM action_films;
Kết quả:
Ví dụ kết một bảng thường với một CTE:
WITH cte_rental AS (
SELECT
staff_id,
COUNT(rental_id) rental_count
FROM
rental
GROUP BY
staff_id
)
SELECT
s.staff_id,
first_name,
last_name,
rental_count
FROM
staff s
INNER JOIN cte_rental USING (staff_id);
Câu query trên sẽ lấy ra liệt kê số film được mượn ứng với mỗi staff
Tiếp đến, ta join bảng staff với CTE trên để lấy ra thông tin chi tiết của mỗi staff:
Ví dụ dùng query với nhiều CTE cùng lúc:
WITH film_stats AS (
-- CTE 1: Calculate film statistics
SELECT
AVG(rental_rate) AS avg_rental_rate,
MAX(length) AS max_length,
MIN(length) AS min_length
FROM film
),
customer_stats AS (
-- CTE 2: Calculate customer statistics
SELECT
COUNT(DISTINCT customer_id) AS total_customers,
SUM(amount) AS total_payments
FROM payment
)
-- Main query using the CTEs
SELECT
ROUND((SELECT avg_rental_rate FROM film_stats), 2) AS avg_film_rental_rate,
(SELECT max_length FROM film_stats) AS max_film_length,
(SELECT min_length FROM film_stats) AS min_film_length,
(SELECT total_customers FROM customer_stats) AS total_customers,
(SELECT total_payments FROM customer_stats) AS total_payments;
Câu query này sẽ như sau: lấy ra thống kê về film bỏ vào cte film_stats (đánh giá phim trung bình, độ dài phim dài nhất, độ dài phim ngắn nhất) và lấy ra thống kê về khách hàng bỏ vào cte customer_stats (tổng lượng customer, tổng doanh thu) => sau đó sẽ gom chung vào 1 query select để lấy ra cùng lúc thống kê film và customer:
Vậy nếu ta viết query tương tự như trên bằng query thuần thì sao?
EXPLAIN (ANALYZE, BUFFERS ) SELECT
ROUND(film_stats.avg_rental_rate, 2) AS avg_film_rental_rate,
film_stats.max_length,
film_stats.min_length,
customer_stats.total_customers,
customer_stats.total_payments
FROM
(SELECT
AVG(rental_rate) AS avg_rental_rate,
MAX(length) AS max_length,
MIN(length) AS min_length
FROM film) AS film_stats,
(SELECT
COUNT(DISTINCT customer_id) AS total_customers,
SUM(amount) AS total_payments
FROM payment) AS customer_stats;
Performence thì thế nào:
Có thể thấy performence giữa chúng gần như tương đương với dùng cte:
5. CTE Recursive
Giống như đệ quy trong lập trình bình thường, ta có thể sử dụng CTE Recursive để tạo một query đệ quy với từ khóa WITH RECURSIVE
WITH RECURSIVE cte_name (column1, column2, ...)
AS(
-- anchor member
SELECT select_list FROM table1 WHERE condition
UNION \[ALL\]
-- recursive term
SELECT select_list FROM cte_name WHERE recursive_condition
)
SELECT * FROM cte_name;
Ta sẽ tạo một bảng employee:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
full_name VARCHAR NOT NULL,
manager_id INT
);
Ta sẽ viết câu query đệ quy lấy ra list tất cả các employee là cấp dưới của employee có id = 2 (tức là B thuộc quản lý của A, C thuộc quản lý của B => B, C đều là cấp dưới của A)
WITH RECURSIVE subordinates AS (
SELECT
employee_id,
manager_id,
full_name
FROM
employees
WHERE
employee_id = 2
UNION
SELECT
e.employee_id,
e.manager_id,
e.full_name
FROM
employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;
6. Ưu Điểm của CTE
- Tính rõ ràng và dễ bảo trì: CTE giúp chia nhỏ các truy vấn phức tạp thành từng phần rõ ràng, dễ hiểu.
- Có thể tái sử dụng: Bạn có thể sử dụng CTE nhiều lần trong cùng một truy vấn, tiết kiệm tài nguyên và tăng hiệu suất.
- Hỗ trợ đệ quy: CTE đệ quy rất hữu ích cho các bài toán cây phân cấp hoặc các cấu trúc đệ quy khác.
7. Nhược Điểm của CTE
- Hiệu suất: CTE trong một số trường hợp có thể không tối ưu bằng việc dùng các truy vấn con (subquery) hoặc bảng tạm nếu dữ liệu lớn và phức tạp.
- Không thể sử dụng lại giữa các truy vấn: CTE chỉ tồn tại trong phạm vi một truy vấn, vì vậy không thể dùng lại trong các truy vấn khác nếu không khai báo lại.
8. So Sánh CTE với Các Phương Pháp Khác
- Subquery: CTE dễ đọc và bảo trì hơn so với subquery, đặc biệt khi cần chia nhỏ truy vấn phức tạp.
- Bảng tạm (Temporary Tables): CTE không yêu cầu tạo bảng thực tế trong cơ sở dữ liệu, điều này giúp tránh các bước tạo và xóa bảng tạm, nhưng bảng tạm có thể dùng cho nhiều truy vấn khác nhau, còn CTE chỉ dùng cho một truy vấn duy nhất.
9. Kết Luận
CTE trong PostgreSQL là một công cụ mạnh mẽ để giúp truy vấn dữ liệu phức tạp trở nên rõ ràng và dễ bảo trì hơn. Nó đặc biệt hữu ích cho việc tổ chức các truy vấn phức tạp và thực hiện các bài toán đệ quy. Tuy nhiên, việc sử dụng CTE cũng cần cân nhắc về hiệu suất và quy mô dữ liệu để tối ưu cho từng trường hợp.
10. Minh Họa Cuối Cùng
Để minh họa rõ hơn, chúng ta có thể xem CTE giống như một tập hợp các công thức tính toán trong bảng tính. Bạn định nghĩa các bước nhỏ (CTE) để dễ kiểm soát, sau đó dùng chúng để tính kết quả cuối cùng, làm cho mọi thứ trở nên đơn giản hơn so với viết toàn bộ phép tính trong một bước dài và khó đọc.