PostgreSQL - WITH 子句：初学者的友好指南

您好，有抱負的數據庫愛好者！今天，我們將踏上一段令人興奮的旅程，進入 PostgreSQL 的世界，特別是探討強大的 WITH 子句。別擔心您對編程是新手；我將成為您的友好導遊，一步一步地解釋一切。所以，來一杯咖啡，我們一起來看看！

WITH 子句是什麼？

WITH 子句，也稱為公共表達式（Common Table Expressions，CTEs），在 PostgreSQL 中就像一個神奇的工具箱，讓我們能在較大的查詢中編寫輔助語句。把它看作是在 SELECT、INSERT、UPDATE、DELETE 或 MERGE 語句中引用的臨時命名結果集。

為什麼使用 WITH 子句？

1. 提高可讀性
2. 簡化複雜查詢
3. 允許遞歸查詢

讓我們從一個簡單的例子開始，讓我們的腳濕一下水：

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;

在這個例子中，我們創建了一個臨時結果集 employee_salaries，計算每個部門的平均工資。然後，我們使用這個結果集來找到平均工資超過 50,000 美元的部門。

基本語法和用法

WITH 子句的基本語法如下：

WITH cte_name AS (
CTE_query_definition
)
SELECT * FROM cte_name;

在這裡，cte_name 是您給您的公共表達式的名稱，CTE_query_definition 是定義結果集的查詢。

讓我們再看一個例子：

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;

在這個查詢中，我們首先識別出總訂單金額超過 1,000 美元的客戶，然後將這些信息與客戶表連接以獲取他們的名字。

多個 CTE

WITH 子句的一個很酷的功能是您可以在單個查詢中定義多個 CTE。這就像為您的主要查詢提供多個助手！

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;

這個查詢首先識別出銷量最好的前 5 件產品，計算所有產品的收入，然後將這些信息與產品名稱結合。

遞歸 WITH

現在，讓我們進入更先進的領域：遞歸 CTE。這些特別適合處理層次結構或樹狀結構數據。

遞歸 CTE 的語法如下：

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

讓我們看一個經典的例子：生成數字序列。

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

這個查詢生成從 1 到 10 的數字序列。非遞歸項從 1 開始，遞歸項將前一個數字加 1，直到我們達到 10。

一個更實際的例子：組織結構

想像我們有一個 employees 表，其中包含 employee_id、name 和 manager_id 列。我們可以使用遞歸 CTE 來顯示整個組織結構：

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;

這個查詢從頂級經理（manager_id 為 NULL）開始，遞歸地找到每個經理下的所有員工，並在過程中分配級別。

最佳實踐和提示

1. 命名：為您的 CTE 選擇清晰、描述性的名稱。
2. 複雜性：將複雜查詢分解為更小、更易於管理的 CTE。
3. 性能：雖然 CTE 可以提高可讀性，但要注意過度使用可能會影響性能。
4. 遞歸：使用遞歸 CTE 時要謹慎，以避免無窮循環。

WITH 子句常用的方法

以下是一個總結 WITH 子句常用的方法的表格：

方法	描述	示例
SELECT	從 CTE 检索數據	SELECT * FROM cte_name
JOIN	將 CTE 與其他表結合	SELECT * FROM table JOIN cte_name ON ...
UNION	結合多個 CTE 的結果	WITH cte1 AS (...), cte2 AS (...) SELECT * FROM cte1 UNION SELECT * FROM cte2
INSERT	使用 CTE 插入數據	WITH cte AS (...) INSERT INTO table SELECT * FROM cte
UPDATE	使用 CTE 更新數據	WITH cte AS (...) UPDATE table SET ... FROM cte WHERE ...
DELETE	使用 CTE 刪除數據	WITH cte AS (...) DELETE FROM table USING cte WHERE ...

記住，熟能生巧！不要害怕在您自己的 PostgreSQL 環境中嘗試這些概念。在您知道之前，您將能夠以數據庫大師的優雅寫出複雜的查詢！

快樂查詢，願您的數據總是結構良好，您的查詢總是優化！

Credits: Image by storyset