Hi, 大家好！

昨天嘗試解了自己出的情境，並嘗試用簡單的subquery 和Active Record 來得到同樣的結果，這對SQL 小白來說腦袋已用到了極致

而今天想研究的主題是CTE，其全名是Common Table Expression，跟使用subquery 看似有點像，但結構和使用時機有點差異，找到的精簡解釋如：

A Common Table Expression is a temporary data set to be used as part of a query. It only exists during the execution of that query; it cannot be used in other queries even within the same session.

A subquery is a nested query; it’s a query within a query. This means it gives a result from a query as a temporary data set which can be used again within that query.

翻譯蒟蒻：CTE 是個暫存的資料集，只能在執行query 程序間使用；其執行的順序是有方向性的，即便是同個query session 也不見得能重複使用；subquery 則是巢狀查詢，在同個query 程序裡可重複被使用

但魯魯目前還無法完全明白其優劣和適用時機，所以今天的任務便是研究CTE 的架構、並用此解出情境4（各個user 各自completed 的todos 完成率）的結果囉！

我們首先來看看PostgreSQL documentation對CTE 的定義及說明：

WITH Queries (Common Table Expressions)

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.
Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

翻譯蒟蒻：CTE 直翻為通用資料表運算式，也稱作With statement，目的是利用小段的輔助CTE 來處理龐大的query，這些語句是為query 所定義出的暫存資料集，在完成後便會消失；CTE 內能用的SQL expressions包含SELECT、INSERT、UPDATE、DELETE

接著讓我們先來看看CTE 的結構：

WITH cte_name1 (column_list) AS (
    CTE_query_definition 
), 
statement;

若有兩個CTEs：

WITH cte_name1 (column_list) AS (
    CTE_query_definition 
), cte_name2 AS (  -- 用逗號相連
    CTE_query_definition 
), 
statement;

若根據以上的定義來思考，除了龐大的query 可被拆成小區塊幫助解讀外，這些暫存的query CTE 也會在程序後消失，不僅大量減少了query 的loading statement，同時也能優化query 速度

而這些CTE 因為有先後讀取的方向性，若是專案內有重複query 的statement，理想上甚至能將其抽離成獨立的Module 或Class 並在適當時機使用以達到重複運用之效；不過這些都是自己的理想狀況，目前還無法提出有力的證據證實，只能往後持續鑽研確認了

緊接著讓我們來嘗試使用CTE 吧：

情境4：各個user 各自completed 的todos 完成率

WITH completed_count AS (
	SELECT id, completed
	FROM todos
	WHERE completed = TRUE)
SELECT "userId", ROUND(COUNT(completed)::NUMERIC / COUNT("userId"), 2)
FROM todos
WHERE id IN (SELECT id FROM completed_count)  -- 這裡好像怪怪的 XD
GROUP BY "userId"

然後... 就失敗了，請讓被翻爛的書再研究練習一下 XD

失敗的地方這些結果不是自己要的，再看看CTE 怎麼和原本的資料join 起來 XD

參考資料：

1. CTEs versus Subqueries
2. PostgreSQL Documentation: Queries with WITH
3. PostgreSQL Tutorial: CTE

那麼今天的研究就先到這啦，謝謝大家~~