PostgreSQL - WITH Clause: A Friendly Guide for Beginners

Xin chào các bạn đam mê cơ sở dữ liệu! Hôm nay, chúng ta sẽ cùng nhau bắt đầu một hành trình thú vị vào thế giới của PostgreSQL, cụ thể là khám phá clause WITH mạnh mẽ. Đừng lo lắng nếu bạn mới bắt đầu học lập trình; tôi sẽ là người hướng dẫn thân thiện của bạn, giải thích mọi thứ từng bước. Hãy lấy một tách cà phê, và chúng ta cùng nhau nhảy vào!

What is the WITH Clause?

WITH clause, còn được gọi là Common Table Expressions (CTEs), giống như một bộ công cụ ma thuật trong PostgreSQL cho phép chúng ta viết các câu lệnh phụ trợ trong các truy vấn lớn hơn. Hãy tưởng tượng nó như việc tạo ra các bộ kết quả tạm thời có tên mà bạn có thể tham chiếu trong một câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc MERGE.

Why Use the WITH Clause?

1. Cải thiện khả năng đọc
2. Đơn giản hóa các truy vấn phức tạp
3. Cho phép các truy vấn đệ quy

Hãy bắt đầu với một ví dụ đơn giản để làm quen:

WITH employee_salaries AS (
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
)
SELECT department, avg_salary
FROM employee_salaries
WHERE avg_salary > 50000;

Trong ví dụ này, chúng ta đang tạo một bộ kết quả tạm thời叫做 employee_salaries để tính mức lương trung bình cho mỗi phòng ban. Sau đó, chúng ta sử dụng bộ kết quả này để tìm các phòng ban có mức lương trung bình trên $50,000.

Basic Syntax and Usage

Cú pháp cơ bản của một clause WITH trông như thế này:

WITH cte_name AS (
CTE_query_definition
)
SELECT * FROM cte_name;

Ở đây, cte_name là tên bạn đặt cho Common Table Expression của mình, và CTE_query_definition là truy vấn định nghĩa bộ kết quả.

Hãy xem một ví dụ khác:

WITH high_value_orders AS (
SELECT customer_id, SUM(order_total) as total_value
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 1000
)
SELECT c.customer_name, h.total_value
FROM customers c
JOIN high_value_orders h ON c.customer_id = h.customer_id;

Trong truy vấn này, chúng ta trước tiên xác định các khách hàng có đơn hàng giá trị cao (tổng giá trị trên $1000) và sau đó kết hợp thông tin này với bảng khách hàng để lấy tên của họ.

Multiple CTEs

Một trong những điều tuyệt vời về clause WITH là bạn có thể định nghĩa nhiều CTE trong một truy vấn duy nhất. Nó giống như có nhiều trợ lý cho truy vấn chính của bạn!

WITH
top_products AS (
SELECT product_id, SUM(quantity) as total_sold
FROM order_items
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 5
),
product_revenue AS (
SELECT product_id, SUM(quantity * price) as revenue
FROM order_items
GROUP BY product_id
)
SELECT p.product_name, t.total_sold, r.revenue
FROM products p
JOIN top_products t ON p.product_id = t.product_id
JOIN product_revenue r ON p.product_id = r.product_id;

Truy vấn này trước tiên xác định top 5 sản phẩm bán chạy nhất, tính doanh thu cho tất cả các sản phẩm, và sau đó kết hợp thông tin này với tên sản phẩm.

Recursive WITH

Bây giờ, hãy bước vào lãnh thổ phức tạp hơn: CTE đệ quy. Những điều này đặc biệt hữu ích khi làm việc với dữ liệu phân cấp hoặc cấu trúc cây.

Cú pháp cho một CTE đệ quy trông như thế này:

WITH RECURSIVE cte_name AS (
non_recursive_term
UNION [ALL]
recursive_term
)
SELECT * FROM cte_name;

Hãy xem một ví dụ kinh điển: tạo một dãy số.

WITH RECURSIVE number_sequence AS (
SELECT 1 as n
UNION ALL
SELECT n + 1
FROM number_sequence
WHERE n < 10
)
SELECT * FROM number_sequence;

Truy vấn này tạo ra một dãy số từ 1 đến 10. Term không đệ quy bắt đầu từ 1, và term đệ quy thêm 1 vào số trước đó cho đến khi đạt đến 10.

A More Practical Example: Organizational Hierarchy

Hãy tưởng tượng chúng ta có một bảng employees với các cột employee_id, name, và manager_id. Chúng ta có thể sử dụng một CTE đệ quy để hiển thị toàn bộ cấu trúc tổ chức:

WITH RECURSIVE org_hierarchy AS (
SELECT employee_id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, oh.level + 1
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT employee_id, name, level
FROM org_hierarchy
ORDER BY level, employee_id;

Truy vấn này bắt đầu từ quản lý cấp cao nhất (nơi manager_id là NULL) và đệ quy để tìm tất cả nhân viên dưới mỗi quản lý, gán cấp độ khi di chuyển.

Best Practices and Tips

1. Naming: Chọn tên rõ ràng, mô tả cho các CTE của bạn.
2. Complexity: Chia nhỏ các truy vấn phức tạp thành các CTE nhỏ hơn, dễ quản lý.
3. Performance: Mặc dù CTE có thể cải thiện khả năng đọc, nhưng hãy cẩn thận để tránh sử dụng quá mức có thể ảnh hưởng đến hiệu suất.
4. Recursion: Cẩn thận với CTE đệ quy để tránh các vòng lặp vô hạn.

Common Methods Used with WITH Clause

Dưới đây là bảng tóm tắt một số phương pháp phổ biến được sử dụng với clause WITH:

Phương pháp	Mô tả	Ví dụ
SELECT	Truy xuất dữ liệu từ CTE	SELECT * FROM cte_name
JOIN	Kết hợp CTE với các bảng khác	SELECT * FROM table JOIN cte_name ON ...
UNION	Kết hợp kết quả của nhiều CTE	WITH cte1 AS (...), cte2 AS (...) SELECT * FROM cte1 UNION SELECT * FROM cte2
INSERT	Chèn dữ liệu sử dụng CTE	WITH cte AS (...) INSERT INTO table SELECT * FROM cte
UPDATE	Cập nhật dữ liệu sử dụng CTE	WITH cte AS (...) UPDATE table SET ... FROM cte WHERE ...
DELETE	Xóa dữ liệu sử dụng CTE	WITH cte AS (...) DELETE FROM table USING cte WHERE ...

Nhớ rằng, thực hành là cách tốt nhất để thành thạo! Đừng ngần ngại thử nghiệm với các khái niệm này trong môi trường PostgreSQL của riêng bạn. Trước khi bạn nhận ra, bạn sẽ viết các truy vấn phức tạp với sự tinh tế của một chuyên gia cơ sở dữ liệu!

Chúc các bạn thành công và dữ liệu của bạn luôn được cấu trúc tốt và truy vấn tối ưu!

Credits: Image by storyset