虛擬表(Virtual Table)

前言

除了 index 之外，如何 Query table 也是一門學問，
是否曾經看過種 Query 花了 200 多行還沒寫完?
有時將多個小 query 分別完成後，
最後再做一個 query 來整併然後輸出結果，是不是可以更清楚明瞭呢？
今天要來介紹的就是虛擬表(Virtual Table)，
他並不是一個真的存 RawData 的表，而是由 query 來完成的表。
接下來將一一說明各式虛擬表。

CTE (Common Table Expressions)

介紹

還在將所有 SubQuery 串成一大串作 Query 嗎？其實有更好的選擇。
將多個小 query 分別完成後，
最後再做一個 query 來整併然後輸出結果。
這些 Query 都會 in memory 處理，所以會查到即時資料

小提醒， WITH 是獨立的 Query，
最後的 Query 也是獨立的不過會等待 CTE 完成後再做取用
當然你也可以全部 CTE 完後不做 Select，只不過就變成沒有輸出了。

語法

WITH cte_name (column_list) AS (
    CTE_query_definition
)

statement;

範例

WITH checkout (total) AS (
    Select sum(price) as total
    from order where userId='xxx'
)
WITH off (discount) AS (
    Select discount
    from user where id='xxx'
)

select (select total from checkout)*(select discount from off) as final;

Recursive CTE

介紹

剛剛介紹了 CTE，可以將各類的 Query 切成數個小的 Query，
最後再利用一個 Query，取出 CTE 的結果。
這不就是 division and conquer 的精神嗎？
如果再加上 Recursive，那麼就可以完全達到 division and conquer 了！

語法

WITH RECURSIVE cte_name (column_list) AS (
    CTE_query_definition
)
statement;

範例

這次來實作一個 link list，從頭到尾 query 一次

1. 先來插入一點資料

INSERT INTO link_list (id， name， next)
VALUES
     (0， 'r'，1)，
     (1， 'a'，2)，
     (2， 'b'，3)，
     (3， 'c'，null);

2. 查詢

WITH RECURSIVE  query_link AS (
    select * from rec where id=0 -- non-recursive term
    union
    select sub.* from rec as sub
    inner join query_link as current on  current."next"=sub."id"  -- recursive term
)
select * from query_link;

3. 結果

   id	name	next
   0	r	1
   1	a	2
   2	b	3
   3	c	null

View

介紹

當你寫了一個非常精妙的 Sql 語句，希望每次存取該 Query 時，都如同一般表一樣。
這時我們就可以建立 View 來將此存下來。
這些 Query 都會 in memory 處理，所以會查到即時資料。

語法

CREATE VIEW view_name (column_list) AS query;

範例

CREATE VIEW vista AS SELECT 'Hello World';

Recursive VIEW

介紹

一樣的 View 也有 RECURSIVE

語法

CREATE RECURSIVE VIEW view_name (column_list) AS query;

範例

1. 查詢

CREATE RECURSIVE VIEW num_1_100 (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM num_1_100 WHERE n < 100;

2. 結果

   id
   1
   2
   ...
   100

MTV (Materialized Views)

介紹

上面介紹的 Query 都會 in memory 處理，所以會查到即時資料，
但如果今天想要 Query 大量資料時，如果每次 Query 可能會耗費許多時間，
是否可以暫時將這些 Query 後的結果存下來呢？
MTV (Materialized Views)是你的救星，
可以將結果直接存入硬碟之中，最後如同一般表一樣只需要花費查詢該 MTV 表的時間。
但這些資料會隨著時間而落後，可以額外利用程式撰寫 cronjob 來更新 MTV 表。

語法

CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

其中， WITH DATA 和 WITH NO DATA 代表著，MTV 在建立的過程中，要不要執行 query，來載入 data。
如果只是先要一個空的 MTV，那麼就使用 WITH NO DATA。

範例

CREATE MATERIALIZED VIEW query_link AS
select * from num_1_100
WITH DATA

更新

因為 mtv 已經是真實的將資料建立一份到硬碟中了，所以不會再是即時的資料。
我們可以自行撰寫 cronjob 來做更新資料的動作，
refresh 過程中 postgres 預設會 lock 住整張 mtv 無法讀取。
如果想要避免可以加上 CONCURRENTLY。

• 直接更新: REFRESH MATERIALIZED VIEW view_name;
• CONCURRENTLY: REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

結語

這單元介紹了各式虛擬表(Virtual Table)，
有了這個概念，不管是多困難的 query 都可以被 division and conquer。
雖然這裡只介紹了使用 CTE query link list，
但其實只要稍微變化一下更可以推廣 query tree 或 graph。
CTE 不只是拆解多個小 query，有時還可以達到 Parallel Query 的功能，加速查找。
最後的 MTV 將 analytical processing 額外做處理，
讓該表的 Query 與其他 OLTP 所使用的 Table 已經完全分離了，
可以說是達到最簡易的 OLAP 的效果。

參考資料

1. 7.8. WITH Queries (Common Table Expressions)
2. CREATE VIEW
3. 15.2. When Can Parallel Query Be Used?
4. 41.3. Materialized Views
5. PostgreSQL Views
6. PostgreSQL CTE
7. Learn PostgreSQL Recursive Query By Example
8. 架構面試題 #3， RDBMS 處理樹狀結構的技巧
9. Linked list with CTE
10. 提升服務效能、減輕 DB 負擔！(2): Materialized View