# 我所理解的 PostgreSQL 04: Temporary Table 是什麼? 跟 CTE 和子查詢差在哪?

{%hackmd @moment89/tXJIcGqmSI6_fJQp2hnJUA %}

## 什麼是 Temporary Table (Temp Table)?

### 📌特性

* Session 範圍有效
Temp Table 會在建立它的 Session 結束時自動清除。
* 跨 Transaction 使用
在一個 Session 中，多個 Transaction 可以存取同一個 Temp Table。
* 資料通常先儲存在記憶體中，適合短期、大量資料的處理。
* 與 Table 功能類似
可以建立 Primary Key、Unique Constraint、Index 等，幫助提升資料處理期間查詢效能。
(通常只在 Temp Table 要處理的資料量大的時候使用較具效益，畢竟建立 index 也是成本。)

### 📌常見應用情境

* Function、Procedure 的中繼資料儲存，除了提供後續資料處理步驟使用，也可以做為 Debug 用途使用。
* 複雜串查或資料集處理，當需進行多步驟、彙整、排序等操作時，可將中間結果暫存至 Temp Table，加快查詢效能並簡化 SQL。
* 一般操作資料庫下 SQL 也可以利用 Temp Table 幫助開發測試。

## Temp Table vs CTE (Common Table Expressions) vs Sub Query

SQL 寫法中，經常使用到 CTE 和 Sub Query，是不是感覺三者用途很像，都是拿來暫存某段 SQL 的查詢結果?
Sub Query 和 CTE 確實很類似，在多數情境下這兩者的查詢計畫會一模一樣，但 Temp Table 則和前兩者有較大的差異，不過這三者是完全可以一起使用的 (實務上經常都用)。

| | Sub Query | CTE | Temp Table |
|--|--|--|--|
| 可重複使用 | 不可重複使用，查詢完成即消失 | 單一查詢內可重複使用，查詢完成即消失 | 同一 Session 內不同 Transaction 可重複使用，Session 結束即消失 |
| 查詢效能 | 小資料量處理快速 | 小資料量處理快速[^CTE] | 適合大資料量處理[^TempTable] |
| 支援 index | 不支援 | 不支援 | 支援 |

[^CTE]: 只要沒有下 MATERIALIZED，則 PostgreSQL 優化器處理 CTE 的邏輯和 Sub Query 一模一樣。
[^TempTable]: 要注意 Temp Table 無法觸發平行查詢。➡️[相關討論](https://stackoverflow.com/questions/69533864/why-are-scans-of-ctes-and-temporary-tables-parallel-restricted)

以下舉例，同一種查詢用 Sub Query、CTE、Temp Table 分別會長什麼樣子?

### 測試資料

隨意建立幾張資料表，塞入 Demo 用的資料。

-- 客戶資料表
DROP TABLE IF EXISTS test.customers CASCADE;
CREATE TABLE test.customers (
    customer_id bigserial PRIMARY KEY,
    customer_name varchar(100),
    customer_level int not null CHECK (customer_level BETWEEN 1 AND 3), -- 1: 超級會員, 2: 特輯會員, 3: 普通會員
    created_at timestamp with time zone DEFAULT now(),
    modified_at timestamp with time zone DEFAULT now()
);

-- 產品資料表
DROP TABLE IF EXISTS test.products CASCADE;
CREATE TABLE test.products (
    product_id bigserial PRIMARY KEY,
    product_name varchar(100),
    price numeric,
    inventory int not null default 0,
    created_at timestamp with time zone DEFAULT now(),
    modified_at timestamp with time zone DEFAULT now()
);

-- 訂單資料表
DROP TABLE IF EXISTS test.orders CASCADE;
CREATE TABLE test.orders (
    order_id bigserial PRIMARY KEY,
    order_status int CHECK (order_status BETWEEN -1 AND 2), -- 0: 待付款, 1: 已付款, 2: 已出貨, -1: 已取消
    customer_id bigint references test.customers(customer_id) on delete cascade on update cascade,
    created_at timestamp with time zone DEFAULT now(),
    modified_at timestamp with time zone DEFAULT now()
);

-- 訂單內容資料表
DROP TABLE IF EXISTS test.order_info CASCADE;
CREATE TABLE test.order_info (
    order_id bigint references test.orders(order_id) on delete cascade on update cascade,
    product_id bigint references test.products(product_id) on delete cascade on update cascade,
    quantity int,
    created_at timestamp with time zone DEFAULT now(),
    modified_at timestamp with time zone DEFAULT now()
);

-- 塞入測試資料
INSERT INTO test.customers (customer_name, customer_level) VALUES ('Customer A', 1), ('Customer B', 1), ('Customer C', 2), ('Customer D', 2), ('Customer E', 3);
INSERT INTO test.products (product_name, price, inventory) VALUES ('Product A', 10, 500), ('Product B', 20, 480), ('Product C', 30, 190);
INSERT INTO test.orders (order_status, customer_id) VALUES (0, 1), (1, 2), (-1, 3), (2, 4), (1, 5);
INSERT INTO test.order_info (order_id, product_id, quantity) VALUES (1, 1, 10), (1, 2, 5), (2, 3, 10), (2, 2, 20), (3, 1, 15), (3, 3, 30);

### Demo

#### Sub Query

用 Sub Query 的方式查詢超級客戶買的產品分布。
在這樣單純的查詢條件下，看起來 SQL 不會太難閱讀，但若串查條件較多，在閱讀上會比較不方便。

SELECT
    product_id,
    sum(quantity) AS total_quantity
FROM test.order_info
WHERE order_id IN (
    SELECT order_id
    FROM test.orders
    WHERE customer_id IN (
        SELECT customer_id
        FROM test.customers
        WHERE customer_level = 1
    )
)
GROUP BY product_id
ORDER BY product_id;

查詢結果和查詢計劃如下圖。
<center class="half">
<img src=https://hackmd.io/_uploads/ryPQ69Ddxx.png width="300"/>
<img src=https://hackmd.io/_uploads/HkbaR9Dugl.png width="300"/>
</center>

#### CTE

用 CTE 的方式查詢超級客戶買的產品分布。
看似比 Sub Query 冗長一點，但可以避免過深的巢狀 SQL，在串查條件較多的情況下會較容易閱讀。

WITH cte_super_customer AS (
    SELECT customer_id
    FROM test.customers
    WHERE customer_level = 1
), cte_orders AS (
    SELECT o.order_id
    FROM test.orders AS o
    JOIN cte_super_customer AS c
    ON o.customer_id = c.customer_id
)
SELECT
    oi.product_id,
    sum(oi.quantity) AS total_quantity
FROM test.order_info AS oi
JOIN cte_orders AS o
ON oi.order_id = o.order_id
GROUP BY oi.product_id
ORDER BY oi.product_id;

查詢結果和查詢計劃如下圖。
可以看到查詢計劃和 Sub Query 幾乎一樣。
<center class="half">
<img src=https://hackmd.io/_uploads/r1KJAqv_lg.png width="300"/>
<img src=https://hackmd.io/_uploads/Hy-myjDdxe.png width="300"/>
</center>

#### Temp Table

Temp Table 的寫法看起來最冗長，但同一 Session 下每張 Temp Table 都可以反覆查詢，非常適合資料要重複利用或是要 Debug、驗證的場景。

DROP TABLE IF EXISTS tt_super_customer;
CREATE TEMP TABLE tt_super_customer AS
SELECT customer_id
FROM test.customers
WHERE customer_level = 1;

DROP TABLE IF EXISTS tt_orders;
CREATE TEMP TABLE tt_orders AS
SELECT o.order_id
FROM test.orders AS o
JOIN tt_super_customer AS c
ON o.customer_id = c.customer_id;

DROP TABLE IF EXISTS tt_total_quantity;
CREATE TEMP TABLE tt_total_quantity AS
SELECT
    oi.product_id,
    sum(oi.quantity) AS total_quantity
FROM test.order_info AS oi
JOIN tt_orders AS o
ON oi.order_id = o.order_id
GROUP BY oi.product_id;

SELECT * FROM tt_total_quantity ORDER BY product_id;

<center class="half">
<img src=https://hackmd.io/_uploads/BkxbejvOxe.png width="300"/>
</center>

#### 比較

可以看到以上三種做法的差異，但其實他們是可以依需求混用的。

比如說，我認為串查客戶 id 的部分只取用一個欄位、查詢條件單純，就會選用 Sub Query；想特別驗證計算過程中取得的超級會員訂單id有沒有錯誤，就會選擇把這段資料存為 Temp Table 以利後續複查。
那我最後的寫法可能就會如下：

-- 混用方案: 想要驗證超級會員的訂單id
DROP TABLE IF EXISTS tt_super_customer_orders;
CREATE TEMP TABLE tt_super_customer_orders AS
SELECT order_id FROM test.orders
WHERE customer_id IN (
    SELECT customer_id FROM test.customers
    WHERE customer_level = 1
);

DROP TABLE IF EXISTS tt_total_quantity;
CREATE TEMP TABLE tt_total_quantity AS
SELECT
    oi.product_id,
    sum(oi.quantity) AS total_quantity
FROM test.order_info AS oi
JOIN tt_super_customer_orders AS o
ON oi.order_id = o.order_id
GROUP BY oi.product_id
ORDER BY product_id;

SELECT * FROM tt_total_quantity; -- 查看產品分布
SELECT * FROM tt_super_customer_orders; -- 驗證超級會員的訂單id有沒有串錯

### 結論

以下分享我個人在寫 SQL 時應用的思路。
一次性查詢、條件單純 ➡️Sub Query
條件複雜想提升可讀性 ➡️ CTE、Temp Table
需要在單一 SQL 中重複使用的中間查詢 ➡️ CTE
需要跨多個 SQL 間重複使用的中間查詢 ➡️ Temp Table