BẢNG TẠM POSTGRES (PostgreSQL common table expression)

BẢNG TẠM POSTGRES (PostgreSQL common table expression)

1. 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.