---
title: 📖 Effective SQL
tags: extract
disqus: yipohackmd
---

# [Effective SQL](https://www.tenlong.com.tw/products/9789864764358)

## 序

- SQL everywhere (即便 NoSQL 也 not only SQL)
- 雖是 國際標準，但產品間有微妙差異。
- 藉本書從錯誤中學習

## 前言

- 術語
* SQL (Structured Query Language)
* RDBMS (Relational Database Management System)
- 思維方式：程序式 → 集合式
- SQL 史
* IBM 研究員 (Codd 博士) 提出
* 各 RDBMS 百家爭鳴：System/R、Oracle、Ingres、DB2
* 官方標準的制訂過程：SQL/86、SQL/89、SQL/92、SQL3、SQL/Foundation
- [DB Engines Ranking](https://db-engines.com/en/ranking)
* 只代表有測過
- 範例資料庫 ([GitHub](https://github.com/TexanInParis/Effective-SQL))

# CH1

- 模型本身要設計好，才寫得出好 SQL。
- 更深的坑：《[Database Design for Mere Mortals, 3/e](https://www.tenlong.com.tw/products/9780321884497)》

## 01：要有主鍵

- 主鍵：(尤其 關聯式資料庫) 總要有個「代號」來表示特定一筆資料 (悲劇：系統允許資料表沒有主鍵)
* 內容唯一
* 不可為空
* 可以由多個欄位組合而成 (複合主鍵)
* 仍要明確定義給系統知道 (以便進行索引加速查詢…等)
- 理論上
* 穩定 (永不改變)
* 盡量簡單 (整數 > 浮點數、單欄 > 多欄)
- 實務上
* 數值主鍵：自動產生無意義整數
+ `IDENTITY` (MS SQL)、`AUTO_INCREMENT` (MySQL)、`serial` (PostgreSQL)
+ `Sequence` 物件
* 文字主鍵
+ 需要修改時會很麻煩
+ 防止重複：可能只是需要 unique 屬性
* 複合主鍵
+ 較沒效率 (索引/聯集 時)
+ 有時是合理的 (如：多對多關聯表)
- 參考完整性 (RI)：關聯的對象一定要存在

## 02：消除重複資料

- 重複資料造成麻煩 → 正規化
- 以 銷售記錄 為例
* 新增：無法在銷售前預先輸入車型資料
* 新增：每次銷售皆得重複輸入大部份資料 (耗時間、空間，易輸入錯誤)
* 修改：更改姓名須更新大量資料 (且前提是資料一致)
* 刪除：可能一併刪除客戶資料
- 解法
* 拆分成四個資料表 (客戶/業務員/車型/交易)，以 外來鍵 聯結其他資料表的 主鍵。
* 透過 `JOIN` 查詢 (建立虛擬資料表) 重建出原表格

## 03：消除重複群組

- 有著 `member1`、`member2`、`member3`、… 的欄位在，大概就是出錯了。
* 難以防止重複
* 難以查詢
- 欄的代價高；列的代價低。
- 解法：正規化為 一對多 關聯表
- `UNION` 查詢
* `UNION ALL`：保留重複資料
* 每個 `SELECT` 的欄位 (型別/順序) 皆須相同 (名稱用第一個 `SELECT` 的 `AS`)

## 04：一欄一屬性

- 一個欄位只儲存一個屬性 (不可切割 的資料)
- 姓名 → 姓+名
* 否則難以據 姓氏 或 名稱 搜尋
* `LIKE` 很沒效率
- 地址 → 門牌號碼+街道巷弄+城市+州+國家+郵遞區號
* 同樣難以據 各屬性 搜尋
* 便於以 城市/州/國家 彙整資料
- 再以 `CONCAT` 串起各欄位值
* 此處各平台略有差異
* (可以寫判斷式？)
- 仔細考量需求加以細分

## 05：儲存計算結果不好

- 現有資料就能計算而得的結果 → 沒有儲存的必要
* 新增/刪除/修改 都要一併更新 (不如需要時再算)
* 除非有什麼好處能大於成本 (如資料庫不需再變動)
- 觸發器：當指定對象 新增/刪除/修改 時，自動執行指令。
* 難以撰寫
* 運算成本
- (決定性：相同輸入下，每次呼叫皆保證有相同結果。)
- 欄的表示式：指定函式供其呼叫 (加在 `Orders`)
* 非決定性 (因為依靠其他表的資料)，無法建立索引。
* 運算成本 (每列皆執行一次，不如到時再 `GROUP BY`)
- 虛擬欄：`GENERATED [ALWAYS] AS` (加在 `Order_Details`)
* `A * B` 是決定性的，可以建立索引。
* 運算成本
- (?) 不做索引時，以檢視表定義計算 > 在資料表儲存計算。

## 06：`FOREIGN KEY`

- 確保 能找到參照對象
* `ON UPDATE <action>`
* `ON DELETE <action>`
* action [(ref)](https://www.postgresql.org/docs/current/sql-createtable.html)
+ `CASCADE`：一併 更新/刪除 所關聯的列
+ `RESTRICT`：禁止操作
+ `NO ACTION`/`SET NULL`/`SET DEFAULT`
- 便於 圖形化工具 建立 `JOIN` 指令
- 加上 `FOREIGN KEY`
* 事先於 `CREATE TABLE`
* 事後用 `ALTER TABLE`：原有資料不保證符合約束
- `FOREIGN KEY` 通常搭配 索引

## 07：視需求設計

- **問題** 若 員工/廠商 同時也是 客戶，會造成資料重複。 (員工/廠商/客戶 的欄位類似)
* 解法一：合併為 聯絡人 資料表 → 如何辨別身份為 員工/廠商/客戶？
* 解法二：聯絡人 再關聯 3 個資料表
+ 得以抽出特定身份的資料 (員工主管、廠商網站)
+ 應用端邏輯將更複雜 (畢竟是特例，乾脆就重複)
- **問題** 據區域分派客戶給員工？
* 解法一：直接以 郵遞區號 關聯兩資料表
* 解法二：在 客戶 用外來鍵關聯 員工
+ 客戶更新地址時，如何更新所負責的員工？
+ 但較靈活 (若有其他指派條件)
* 解法三：額外建立 領域 資料表來指派 郵遞區號 給 員工
+ 避免一個 郵遞區號 有兩位以上的員工
- **問題** 各式產品有各式屬性
* 解法一：一個表有所有各式屬性 → 好像不太好 (why?
* 解法二：以欄位儲存 JSON 或 XML → 難查詢
* 解法三：以列代替欄來儲存屬性
- 結構化/半結構化：需定妥 欄 與 型別 才能新增資料 與否
- 是個挑戰，視需求而定。

## 08：3NF 以上

- 違反 4NF、5NF 較少見，可能發生於
* 有 複合鍵 時
* 有 多個 多對多 關係時
- 4NF (multi-valued dependency)：兩不相干的多值屬性，不該放在同個表內。
* 否則所有排列組合皆須列舉
- 5NF (join depenedency)：三個角色以上的多對多關係，應妥善拆分為多個關聯表格。
* 書上例子：(a, b)、(b, c)、(a, b, c)
- 6NF：除了 主鍵 外，頂多再有一個欄位。
* 不再需要 `NULL`
- 無損分解：`JOIN` 後仍可得到原本資料表 (??

## 09：反正規化

- 正規化的資料表
* 空間小：易放入快取，編輯效能佳。
* 無重複：`GROUP BY`、`DISTINCT` 需求少
* 但有 `JOIN` 的成本
- 唯讀而不寫入的資料庫，正規化反而較沒效率。 → 反正規化
- (一些實例) 依照所需查詢，設計 (恐會違背其他條款的) 資料表。
* 盡情索引 (甚至每個欄位都做)
* 原本需 `JOIN` 才能得到的欄位 → 直接複製給所需表格
* 可以儲存計算結果
* 可以在單欄儲存多個屬性
- 資料倉儲專家 這部份是在攻三小??

# CH2

- **索引** 是查詢的關鍵！
- 請 開發者 和 資料庫管理員 (DBA) 合作

## 10：空值 與 索引

- `NULL` (空)：未知 或 缺少資料
* `NULL` 與 任何東西 比較 (甚至 `NULL = NULL`) 皆為 false → `IS NULL` 才能判斷
* 主鍵 不得為 空
* 部份系統視 空字串 為 空 → 有些可設定
- 若欄位大多為 空值，索引恐浪費空間。 (why? 從大資料找小東西才有用
* 部份系統可排除 (過濾索引)
* (Oracle: 為何研究強制索引空值？)
- 若索引視空值相等，`UNIQUE` 欄位 (以索引達成) 不能有多個 空值。

## 11：掃描 與 索引

- 查詢 由快到慢
* 唯一搜尋：找到就停
* 索引掃描：較小、以掃描為目的設計，所以快。
* 資料表掃描：大比例查詢才會較快
- 編輯資料須一併更新索引，應評估成本。 (恐未加速讀取反拖累寫入)
* 所以不編輯的話，就可以盡情索引。
- 實作方式
* B-tree (最常見)
+ 叢集索引：據指定 欄位 將資料表排序 → 所以每個資料表只有一個
+ 非叢集索引：由額外的樹結構指向資料表，有自己的順序。
+ 叢集 (即使高比例) > 資料表掃描 (10% 以上) > 非叢集
* Hash
* …等
- 從大資料表找小部份資料，索引才有幫助。
* 若大部份欄位的值皆相同 → 仍逐一讀取多數資料，索引沒幫助。
* 小資料表做什麼都很快 (全部載入記憶體)
- 時常一起查詢的欄就一起索引
* 欄位的順序有差

## 12：索引的其他用途

連接 (`JOIN`)

- 三個常用演算法 (每次連接兩個)
* 套疊迴圈
+ 先查詢 A (主控查詢!?)，再據每列結果查詢 B。
+ 索引能加速查詢
+ 若首次查詢結果少，效能就高。
* Hash
+ 因為使用 雜湊表，連接指定的欄位不需索引。 (誰維護 雜湊表？
+ 頂多是索引 查詢條件 內的欄位有助加速
* 排序-合併
+ 只對連接述詞索引是沒用的 (??
+ 左右方向沒差
+ 要先將兩方排序，成本較高。 → 若有索引提供順序，很有優勢。

叢集

- 時常一起讀取的資料就收在一起 (節省 I/O)
- 若查詢所需的資料都已在索引裡，甚至不必讀取原資料表。

排序 (`ORDER BY`)

- 排序
* 耗時間與空間
* 要完成才知道如何進行下個階段
- 索引是事先排序好的小抄
* 依序讀取即可
* 得以利用 管線 方式串連各階段 (節省空間)
- 順序索引 也可用於 逆序排序 (倒著讀取即可)
* 反之亦然
* 除非多欄索引

## 13：不得己才 觸發器

- 觸發器 (trigger)：在 `INSERT`/`UPDATE`/`DELETE` 時執行指令
* 可移植性較差
- 通常有更好的做法
* 防止孤兒紀錄？ 做法 06：DRI 實作更簡單、執行有效率
* 維護計算結果？ 做法 05
- 較適合的場合
* 維護反正規化的重複資料
* 更複雜的約束
* 複雜的預設值
* 跨資料庫的參考

## 14：過濾索引

- 過濾索引：針對較常查詢的條件製作索引
* 索引的量小，成本低效率佳。
- 語法：加上 `WHERE` 條件
* `WHERE NULL` 或 `WHERE NOT NULL`
* 只能使用 決定性 函式
* 不能用 `OR`
* SQL Server
+ 不能參考 計算出的欄 或 hierarchyID (??
+ 不能用 `BETWEEN`、`NOT IN`、`CASE`
- 被過濾的欄不能包含在索引中 (??
- 實例
* 只查詢低庫存量的產品
* 只查詢特定狀態的文件
- 同個欄可建立多個 過濾索引
- 條件符合的話，過濾索引 也可用在 排序 (`ORDER BY`)。
- 分割資料表 (??

## 15：約束

- 約束：任何操作都會檢查，若違反則被取消。
* `NOT NULL`：確保有值
* `UNIQUE`：確保不重複 (容許空值)
* `PRIMARY KEY`：就是 主鍵 (有點像 `UNIQUE`)
* `FOREIGN KEY`：指向其他資料表的主鍵
* `CHECK`：(對欄或資料表) 下條件限制
* `DEFAULT`：預設值
- 約束以外的方法
* 應用端程式：壞主意，這是資料庫自己的責任。
* 預存程序：修改比較困難、務必只透過 預存程序。
* 觸發器：做法 13

## 16：不一定標準

*以為 SQL 是標準嗎？殘念！其實各系統實作不一！*

- 排序
* 空值 可能在 非空值 之前或之後
* 部份系統能以參數指定
- 限制輸出筆數
* 標準有三種做法 1) `FETCH FIRST` 2) 窗函式 3) 游標
* 各家支援程度不同
- `BOOLEAN`
* 標準：不一定得支援，但其值可為
+ `TRUE`
+ `FALSE`
+ `UNKNOWN` 或 `NULL`
* 有的支援；有的不支援；有的以其他方式提供。
+ Access：Yes/No
+ SQL Server：`BIT`
+ MySQL：`TINYINT(1)`
- 函式：差別太了…
- `UNIQUE`
* 標準
+ `UNIQUE` 隱含 `NOT NULL`
+ 若容許空 (沒有 `NOT NULL`)，則容許多個 空值 (即 `NULL != NULL`)。
* 各家略有不同

## 17：函式索引

- 函式索引、虛擬欄、表示式索引、對計算欄索引，都是類似功能。
* 不同系統在不同版本有不同的支援程度，要留意。
* 在 SQL Server 索引計算欄有些限制
* 無法使用才回到土炮做法 -- (以觸發器、應用程式) 維護額外欄位再索引
- **實務** 部份系統查詢時嚴格區分大小寫 → 可使用 函式索引 解決
* `WHERE UPPER(name) = 'yipo'` 通常需資料表掃描
* 若建立 函式索引 即可利用
- 用於索引的函式 (含自訂函式) 必須是 決定性的
* 以 `DETERMINISTIC` (Oracle)、`IMMUTABLE` (PostgreSQL) 宣告函式具有 決定性
- 還是要考量索引的成本

(最後的摘要錯棚了嗎？)

# CH3

把壞資料表弄成好看的樣子

## 18：`VIEW`

- 以 檢視表 (`VIEW`) 呈現正規化後的樣子
* 消除重複資料：分別挑選欄位 並 `DISTINCT` → 便容易發現錯誤，並在原資料表修正。
* 消除重複群組：挑選重複欄位 並 `UNION`
- 檢視表 可能無法 `UPDATE` (因為用了 `DISTINCT` 和 `UNION`)
* 部份系統提供 `INSTEAD OF` 觸發器 → 能透過 檢視表 來更新 底層資料表
- 其他使用場合
* 專注特定的欄或列
* 簡化或更名欄位
* 組合多個資料表
* 省去重複輸入複雜的指令
+ 確保一致性
+ 參數化檢視表 (??
* 遮蔽敏感資訊
+ 搭配限制權限
+ 限制 欄 或 列 皆可
+ `WITH CHECK OPTION` 防止超出範圍的編輯
* 提供相容性
* 據權限或角色呈現不同資料
* 彙整資料：`SUM()`、`AVERAGE()` 等
* 匯入/匯出 串接其他程式
- 拿檢視表製作檢視表：有效能疑慮
* 例子：以檢視表製作彙整資料，其中連接了兩張檢視表。
* 原理：解構檢視表語法成為等效語法
* 問題
+ 對中間結果的欄位求值，恐浪費計算工作。
+ 被濾掉的列造成無謂的 IO
* 最好直接參考原始資料表

## 19：ETL

- Extract, Transform, Load (ETL)：匯入外部資料的工具
- 以 Access 為例
1. 從 試算表 匯入
2. 辨識首列可能為欄位名稱
3. 辨識欄位可能的型別
4. 忽略不重要的欄
5. 挑選主鍵、是否 `AUTO INCREMENT` (或不要主鍵)
6. 命名資料表
7. 整合其他工具分析
8. 進行後續正規化 (Table Analyzer)
* 看來仍需以 `UNION` 反轉重複群組
- 大部份系統皆提供有類似工具 (或其他開源或付費工具)
- 常見的錯誤是… (?? → 要在匯入、正規化多花心思吧？

## 20：`SUMMARY`

- 整合資料，呈現較易讀易懂的資訊；預先計算，加快速度。
- 土炮做法：建立額外資料表來 匯總 (summary) 原始資料表
* 以 觸發器 在原資料更動時一併更新 (若時常異動要留意處理成本)
* 以 預存程序 定期全盤重新建立 (通常較好)
- DB2 支援 匯總資料表
* 來源可以是多個資料表
* 更新：自動 或 手動
* 效能佳、具最佳化選項 `ENABLE QUERY OPTIMIZATION` (使用已匯總的資料??)
* 省去維護 觸發器 或 預存程序 的麻煩
- 語法
* 類似 實質化檢視表 (加了 `GROUP BY`)
* 實質化查詢限制使用 `INNER JOIN` (??
* `COUNT(*)` 以使用 `REFRESH IMMEDIATE` (??
* Oracle：以 實質化檢視表 (`MATERIALIZED VIEW`) 達成
* MS SQL：對 檢視表 建構 索引 達成
* 系統各有不同，建議參考文件。
- 成本
* 佔儲存空間
* 須維護、管理 (觸發器、預存程序、約束??)
* 根據使用情境設計需匯總的資料
* 不同的 分組 或 過濾 需多個 匯總資料表
* 可能需要排程更新
- 行內匯總 → 來避免維護、管理的成本
* 在現有資料表加 匯總欄
* 以 `INSERT INTO` 陳述資料
* 非匯總欄設定為特定值
* 好處：匯總與細節可一併或分開查詢
* 壞處：查詢時留意區分匯總與細節

## 21：以 `UNION` 整理資料

- 舉例討論如何以 `UNION` 整理資料表
1. 從 Excel 來的試算表 → 先設法匯入
2. 不同月份資料要分開查詢 → 直接以 `UNION` 將各月份查詢合併
3. ⑴ 分不出是哪個月 ⑵ 欄位名稱不太對 → ⑴ 於 `SELECT` 增加月份欄位 ⑵ 善用 `AS` 更名欄位
4. (在最後一個子句) 加個 `ORDER BY` 排序資料
+ 也可寫做 `ORDER BY 2, 1`
- 規則
* 欄位數目相同
* 欄位順序相同
* 每個欄位的型態要相容
* (欄位名稱沒差)
- `UNION ALL` 不排除重複，速度較快。

# CH4

找出資料 或 濾掉資料

## 22：加減乘除

- 選擇
* 過濾列
* `WHERE`、`HAVING`
- 投影
* 挑選欄，或搭配 `GROUP BY` 使用函式。
* `SELECT`
* 沒 投影 的欄位仍可用於 選擇
- 連接
* 以指定欄位組合兩個資料表
* 在 `FROM` 中使用 `JOIN`
+ `INNER JOIN`：只保留兩方皆存在的值
+ `OUTER JOIN`：保留 左/右/兩 方所有的值，未存在該方的欄填 `NULL`。
+ (僅部份系統支援) `NATURAL JOIN`：同 `INNER JOIN` 唯不必以 `ON` 指定欄位，而自動對應同名的欄。
- 交集
* 兩表欄位需相符
* `INTERSECT` 或以 `INNER JOIN` 仿造
* 保留重複的列：`INTERSECT ALL`
- 聯集
* 兩表欄位需相符
* `UNION`
* 保留重複的列：`UNION ALL`
- 差集
* 兩表欄位需相符
* `EXCEPT`、`MINUS` (Oracle)，或以 `OUTER JOIN` 仿造 (MySQL、Access)。
* 保留重複的列：`EXCEPT ALL`
- 笛卡兒積
* 兩資料表間，列的所有排列組合。
* 在 `FROM` 列出資料表，或 `CROSS JOIN`。
- 除法
* A 表欄位的值符合 B 表所有欄位的列
* 未有系統直接支援，須自行仿造。 → 26

## 23：找不相符

- 方法㈠：`NOT IN` -- 較慢，必須掃過整個資料表。
- 方法㈡：`NOT EXIST` -- 較快，發現一筆就停止子查詢。
- 方法㈢：`LEFT JOIN` 但 `WHERE` 右方為 `NULL` 的列。(沮喪連接)
- 速度仍視不同系統而定

## 24：`CASE`

- SQL 界的 `IF/THEN/ELSE`，可在 值表示式 之處使用。
- 舉例：據客戶等級打折
* 建立 等級-折扣 對應表，再據等級與客戶表格 `JOIN`。 → 維護較容易
* 使用 `CASE` 判斷等級直接給出折扣 → 省 `JOIN` 成本
- 更多其他場合
* 記錄代碼但顯示全名
* 處理各國貨幣
* 攝氏與華氏

**簡單型**

CASE 算式
  WHEN 數值㈠ THEN
    結果㈠
  WHEN 數值㈡ THEN
    結果㈡
  ELSE
    結果㈢
END;

**搜尋型**

CASE
  WHEN 條件㈠ THEN
    結果㈠
  WHEN 條件㈡ THEN
    結果㈡
  ELSE
    結果㈢
END;

- 可以是任何條件
- 找到符合的條件就中止

---

- 在 `WHERE`、`HAVING` 用 `CASE` 效率好像不太好…
- 更多其他例子
* 據生日計算確切年齡
* 買過 skateboard 但沒買 helmet 的客人

## 29：小心過濾 `LEFT JOIN`

- 問題：1) 所有客戶 2) 與他們 2015Q4 的訂單
- 嘗試㈠：`LEFT JOIN` 後，直接以 `WHERE` 限制日期範圍。
* 少了很多客戶 -- 唯 2015Q4 有下單者 (根本與 `INNER JOIN` 相同)
- 嘗試㈡：再 `OR` 訂單為 `NULL` 者
* 只多了從未下單的客人 (缺少 2015Q4 未下單的客戶)
- 留意 查詢的運行流程
- 解法：先過濾好 訂單 (子查詢)，再與 客戶 `LEFT JOIN`。
* 若要找 2015Q4 未下單者，也可改為 沮喪連接。

# CH5

## 30：`GROUP BY`

**查詢的運行流程**

| # | 指令 | 說明 |
| -:|:--- |:--- |
| 1 | `FROM` | 指定來源資料
| 2 | `WHERE` | 過濾來源資料
| 3 | `GROUP BY` | 對來源資料分組
| 4 | `HAVING` | 過濾分組資料
| 5 | `SELECT` | 挑選輸出欄位、統計分組資料
| 6 | `ORDER BY` | 排序最終結果

---

- `GROUP BY`：將各列分組，同組間的指定欄位具有相同的值，以便統計數據。
* 分組欄位 未必要出現在 `SELECT` (但有點怪)
* `GROUP BY` 裡不可用別名 (why?
- 在 `SELECT` 時，非分組欄位 要套用 彙整函式 (*f*(集合) = 值、決定性函式)，以便統計。
* `COUNT()`、`SUM()`
* `AVG()`、`MIN()`、`MAX()`
* 變異數、標準差
- 指定 分組欄位
* 在 `GROUP BY`：可串接多個欄位為 分組欄位。
* 在 `SELECT`：可再串接欄位，但不可拆分 分組欄位。
- 仍要以 `ORDER BY` 確認最終結果的順序
- 過濾：`WHERE` > `HAVING`，預先降低資料量。
* 在 Access，明確分離條件，來產生較好的 SQL 指令。

**進階的分組操作**

⑴ `ROLLUP`：樹狀

- (-, -) 50
* (Red, -) 25
+ (Red, M) 15
+ (Red, L) 10
* (Blue, -) 25
+ (Blue, M) 20
+ (Blue, L) 5

⑵ `CUBE`：(多維) 陣列

| Color\Dimension | M | L | Sum |
|:---- | --:| --:| --:|
| Red | 15 | 10 | 25 |
| Blue | 20 | 5 | 25 |
| Sum | 35 | 15 | 50 |

⑶ `GROUPING SETS`：一次好幾組

| Color | Dimension | Quantity |
|:------ |:------ | --:|
| Red | `NULL` | 25 |
| Blue | `NULL` | 25 |
| `NULL` | M | 35 |
| `NULL` | L | 15 |
| `NULL` | `NULL` | 50 |

## 31：`GROUP BY` 盡量明確

- 舊標準 (SQL-92) 規定，欄位必定要在以下兩處擇一出現：(為何如此規定??
* 於 `SELECT` 套用 彙整函數
* 於 `GROUP BY` 成為 分組欄位
- 舉例：統計每位客人的訂單資料
* `CustomerID` (主鍵) 已為 `UNIQUE`，再 `GROUP BY` 其他 `Customers` 欄位沒有作用。 → 功能相依
* 新標準 (SQL-99) 便可省略無用的欄位
* 若系統尚未支援：先以子查詢 `GROUP BY` 再 `JOIN` (例子中的子查詢就有符合舊標準嗎??
- 為了容易理解，在 `GROUP BY` 註明欄位時，越明確越好。

## 32：`HAVING`

- `HAVING`：在彙整後過濾：1) 比較彙整結果 2) 與其他彙整結果比較
- 問題㈠：(期間內) 交貨時間較平均長的廠商
* 於 `HAVING` 比較另個子查詢的結果
* 在 `HAVING` 不能使用 `SELECT` 中以 `AS` 定義的別名
- 問題㈡：(期間內) 銷售量超過同類平均的產品
* 主查詢 與 子查詢 皆需同個連接結果 → 先以 CTE (`WITH`) 定義再共用

## 33：若不 `GROUP BY`

- 太多的彙整資料且無法取得所需的細節/沒有支援窗口函式 (??
- 問題㈠：各類別的 最高 酒精含量
* 使用 `GROUP BY Category`
- 問題㈡：各類別的 最高 酒精含量，與其產地。
* 直接加 `Country` 當然不行 (效果是細分群組)
* `JOIN` 自己，並比較 酒精濃度 -- `左 < 右`，若右為 `NULL` 則左最大。
- 討論
* 未用到 `GROUP BY` → `l.Category = r.Category`
* 未用到 `MAX()` → `l.MaxABV < r.MaxABV`
* 重點：避免高成本的 `GROUP BY` (若還有 關聯子查詢 成本更高)

## 34：`COUNT(*)`

簡單問題反而容易出錯

- 問題：1) 列出所有類別 2) 計算各類別元素個數
- 方法㈠
* 所有類別 → `OUTER JOIN`
* 每個類別至少會有一列
+ (x) `COUNT(*)`：一併計算 `NULL`
+ (o) `COUNT(欄位)`：忽略 `NULL`
- 方法㈡
* 子查詢：`SELECT class_name, (SELECT COUNT(item_id) …) …`
* 計數索引，而非實際的列，會較快。
* 查詢分析工具 → 44
* 關聯子查詢 → 41
* 速度仍視不同系統而定
- 追求效率 → 多試各種方法
* 效率測試 → CH7

## 35：小心過濾 `LEFT JOIN` (`HAVING` 版)

- 資料庫
* 食譜、食譜類型
* 食材、食材類型
* 食譜-食材 的 多對多 關聯表
- 問題：「主菜」類食譜中，「香料」類食材少於三樣者。
- 嘗試㈠
* 自 食譜類型、經 關聯表、到 食材類型，共串連五個資料表，方能限定 「主菜」食譜 與 「香料」食材。
* `INNER JOIN` 無法找到 無食材 的食譜 (例子很怪啦，作者盡力了~)
- 嘗試㈡
* 改用 `LEFT JOIN`
* (同 做法 29) 因 `WHERE` 限制，消去了外連接的效果。
- 正解
* 先查好「香料」再與「主菜」 `LEFT JOIN`
* 留意也別誤用 `COUNT(*)` → 34
* 或將 「香料」條件 從 `WHERE` 移至 `JOIN … ON` 亦可

## 36：`COUNT()` 的靈活運用

- 三種用法
* `COUNT(*)`：全部個數
* `COUNT(ALL <expr>)`：對每個元素求值後，計算 **非空值** 的個數。
+ 預設即是 `ALL`，故可省略為 `COUNT(<expr>)`。
* `COUNT(DISTINCT <expr>)`：對每個元素求值後，計算 **非空值** 且 **不重複** 的個數。
- 一張資料表以各種方式 `COUNT()` 能得到不同結果
- `<expr>` 通常是 欄位名稱，而其實是算式。
* 搭配 `CASE` 可據條件計數
* 再搭配 `DISTINCT` 用法多多
* 能一次計數多個結果，而無需 `WHERE`。

## 37：窗口

- 舊標準裡，列沒有「相鄰」的概念。
* 符合條件就好；順序不重要，`ORDER BY` 只是顯示用途。
* 難以寫出 累計 這類的表
- SQL:2003 導入「窗口」 -- `SELECT SUM(column) OVER (PARTITION BY … ORDER BY …)`
* `PARTITION BY`：將資料分組
+ 類似 `GROUP BY`，但只作用在該函式 (而非整個查詢)。
+ 省略則不分組 → 整個資料表為一組
* `ORDER BY`：指定在窗口內的順序
* 每個 `OVER` 皆能有各自不同的設定

## 38：列號、排名

- `ROW_NUMBER()` 產生列號
- `RANK()` 考慮相同排名
* 不要名次跳號 → `DENSE_RANK()`
* 或調整條件不讓平手發生
- 皆要有 窗口；皆要有 `ORDER BY`。

## 39：`RANGE`/`ROWS`

- 預設限制行為對動態彙整表示式無效 (??
- 場合：只需一小段而非全部
* 三個月內的報表 (o) 整個歷史記錄 (x)
* 比較去年同期 (o) 比較上期 (x)
- `RANGE`/`ROWS` + `BETWEEN … AND …`
* `CURRENT ROW`
* `UNBOUNDED PRECEDING` (可單獨使用，無需 `BETWEEN`)
* `UNBOUNDED FOLLOWING` (同上)
* `N PRECEDING` (只適用於 `ROWS`)
* `N FOLLOWING` (同上)
- 討論
* 便不能沒有 `ORDER BY`
* 不能以算式指定窗口大小 (通用資料表運算式??)
- 差別在當值重複時
* `RANGE`：視為同個群組，計算結果皆會相同。
* `ROWS`：仍視為各別的列，會逐列計算出各別結果。
- 範例：三個月 (去年/今年/來年 同月) 平均
* 據固定列數，若中間缺少資料 (如缺少某月資料)，將計算錯誤。 → 產生好範圍再 `LEFT JOIN`
* `LAG()`/`LEAD()`：(窗口中) 往 前/後 第 N 列的該欄

# CH6

# CH7

## 44：分析工具

- 不同方法在不同系統上有不同的執行效率 → 以 (不同的) 分析工具來判優劣
- 最佳化程序 將 SQL 指令 轉換成 執行計劃

**IBM DB2**

- 前置動作：確保 解釋資料表 存在
* 不存在則執行 `CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', …)`
- `EXPLAIN PLAN FOR …`
* 不執行該查詢，而儲存 執行計劃 於上述資料表中。
- 分析工具
* db2exfmt：顯示解釋資訊 (??
* db2expln：顯示靜態 SQL 的存取計劃資訊 (??
* 也可以自行下 SQL 指令查詢 解釋資料表
* IBM Data Studio 有 Access Plan Diagram

**Access**

- 前置動作：啟用 SHOWPLAN.OUT
* 據 Access 版本 與 作業系統位元 修改登錄檔
* 用完記得關掉
- 如往常般執行查詢，執行計劃 將儲存於 SHOWPLAN.OUT 文字檔。
* 檔案位在 我的文件 或 當前預設目錄
- 分析工具
* 沒有圖形檢視工具
* Showplan Capturer

**MS SQL**

- Management Studio 工具列上有兩個按鈕，可得到視覺化圖表。
* 立即顯示預估 執行計劃
* 於執行時捕捉實際 執行計畫
- 還可比較兩個查詢
- 執行時得到額外結果
* `SET STATISTICS XML ON;` 得到 XML
* `SET STATISTICS PROFILE ON;` 輸出至資料表 → 難閱讀，將廢棄
* 用完記得 `OFF`

**MySQL**

- `EXPLAIN …`
* 免前置動作
* 輸出為列表
* MySQL Workbench 有視覺化圖表

**Oracle**

- 產生執行計劃
* `EXPLAIN PLAN FOR …`：儲存於 `PLAN_TABLE` 資料表
* 不一定與實際執行相同
- 檢視執行計劃
* `DBMS_XPLAN`：`SELECT * FROM TABLE(dbms_xplan.display)`
* Oracle SQL Developer 以樹狀顯示

**PostgreSQL**

- `EXPLAIN …`，並有 [諸多選項](https://www.postgresql.org/docs/current/sql-explain.html) 可供設定。
- `BIND` 參數要先 `PREPARE` 再 `EXECUTE`
- pgAdmin 提供圖形檢視

## 45：元資料

- 元資料 (meta-data)：關於 資料庫 (資料表、欄位、等各式物件) 本身的 資料
- SQL 標準訂有 `INFORMATION_SCHEMA` (系統檢視表)
* 第三方工具多半也是藉此獲得資訊 (所以也能手動查詢)
* `.TABLES`：資料表、檢視表
* `.TABLE_CONSTRAINS`：資料表約束
+ 可搭配 `.TABLES` 找出無主鍵的資料表
* `.VIEW_COLUMN_USAGE`：檢視表所依賴的資料表欄位
+ 只用於 `WHERE` 或 `ON` 的欄位也算
* 此部份標準常被忽略於實作 (或支援不完整)
* 標準也尚未涵蓋全部元資料 (獨特索引??)
- 標準較具移植性，但仍須瞭解各系統各自提供的方式。

## 46：執行計劃

- 各系統雖實作、術語不同，但仍可討論 執行計劃 的基本原則。
- SQL 欲抽象陳述資料，而隱藏查詢細節，實作本應交由 最佳化程序 決定。
* 討論 執行計劃 其實有點違反抽象層的初衷
- 解讀 執行計劃：想像自己是 最佳化程序
* 資料庫 如 圖書館
* 索引 如 分類卡片
* 資料表 如 書架
* 資料列 如 書
- 如何索引？據如何查詢資料庫而定。
* 圖書館裡需要怎樣的分類卡片？
* 有時也許不必走到書架，藉分類卡片就能找到答案。
- 例㈠：據區碼找城市
* 藉索引過濾區碼 (index seek)，再至書架取得城市 (key lookup) → 當結果多、資料列分散時，效率差。
* 將 區碼、城市 一併索引 → 索引全部搞定，不必走到書架。
- 例㈡：找無人下訂的產品 (語法使用 子查詢)
* (量多) 執行 左反半連接 (半連接：只找第一個相符)
* (量少) 才用 子查詢 方式
* 據 資料多寡、資料分佈 產生不同 執行計畫
- 例㈢：某員工負責的訂單
* 若結果量多又資料分散 → 不如掃過整個資料表
* 將 員工代號 與 訂單編號 一併索引來改善效率
- 全盤考量，僅為少數場合建立索引並不划算。

# CH8

討論使用 笛卡兒積 (`CROSS JOIN`) 的場合

## 47：笛卡兒積 + 標注

- **問題** 每位客戶買過與沒買過的產品
- 方法㈠：`LEFT JOIN`
1. 每位客戶 與 所有產品 的 笛卡兒積
* 語法：`FROM a, b` (部份系統轉為 `FROM a CROSS JOIN b`)
2. 每位客戶 曾購買的 產品 清單
* 訂單 `INNER JOIN` 訂單細目，以取得 客戶-產品 的關連。
3. 表⑴ `LEFT JOIN` 表⑵，以標注產品曾否購買。
- 方法㈡：(同樣產生 笛卡兒積) 改以「`IN` 子查詢」方式標注
* 優劣視情況而定

## 48

# CH9

- 對應表
* 從 1 (或 0) 開始的數字序列
* 一段期間的日期序列
* 樞紐序列

## 51：空白列

- 總有時候需要一些空白列
- 做法：`UNION ALL` 數字序列
* 只因 `UNION` 較慢 (而非要保留重複值)
* 產生數字序列
+ 遞迴 CTE
+ 事先準備好的資料表
- 寫成函式以參數代入所需空白列數
- 表值函式：回傳資料表的函式 (相對於 純量函式)
* 能用在 `FROM`，可想成 可參數化 的檢視表。
* 內聯：最佳化程序 能看到 表值函式 的內部，得出更佳的 執行計劃。

## 52：以 對應表 拆解資料 (股票)

- 問題：對照股票的 買進 與 賣出 計算獲利
- 做法
1. 把 交易記錄 拆成每股一列：以 數字序列 `<=` 交易股數 為條件 `INNER JOIN`
2. 各別過濾出 買進/賣出 的 每股交易 資料表，並以 窗口函式 據 日期 產生 交易次序。
3. 據 股票代號 與 交易次序 `INNER JOIN` 買進/賣出，即可計算每股之 買賣價差。
- 視情況處理 買多於賣 (或相反) 的狀況

## 53：以 對應表 拆解資料 (折價券)

- 問題：據客戶消費級距產生折價券
* 贈送張數無公式對應，故列有 張數對應表。
- 做法
1. 期間內客戶消費金額：1) `GROUP BY` + `SUM()` 2) `WHERE … BETWEEN`
2. 每位客戶可得折價券張數：表⑴ 與 張數對應表 `JOIN` (條件以 `BETWEEN` 指定)
3. 據 表⑵ 製作 每列一張折價券 的表供列印：同樣以 數字序列 `<=` 可得張數 方式 `JOIN`

## 54：區間統計

- 值要相同才能 `GROUP BY`
- 問題：依分數區間統計成績
- 做法
1. 每位學生的各科成績：`JOIN` 學生、科目、關聯表
2. 每位學生的各科等第：表⑴ `JOIN` 分數-等第 對應表
* 確保同一分數不會落入兩個區間
3. 再以等第 `GROUP BY` 統計數據
- 確保對應表涵蓋所有範圍，以免有所遺漏。
* 使用 `CHECK` 禁止無效值
* 在 對應表 對應 無效值 為 無效值
- 視情況調整範圍大小 (區間不一定得相同大小)

## 55：日期對應表

- 操作 日期/時間 十分麻煩
* 時常要串連多個函式 (可讀性差)
* 部份系統未完整支援 型別/函式
- 做法
* 日期對應表：每日一列，各欄位提供當日的各項屬性。
+ 據需求可自行擴充 (如：工作日)
* 以查表取代呼叫函式：各日期各 `JOIN` 一次對應表，以對應表欄位進行 判斷/運算。
- 效能
* 以 磁碟 I/O 取代 CPU 時間，操作數量多才有優勢。
* 日期對應表 無需修改，可盡量建構索引 (省去讀取原資料表)。
* 部份系統可強制將資料表載入記憶體 (再減少 I/O)
- (分析所需查詢，以妥善建構索引。)

## 56：行程表

1. 行程表：將 日期/時間 拆做兩欄以便查詢 (雖有 `datetime` 型別)
2. 日期對應表：簡單/複雜 版皆可
3. 表⑵ `LEFT JOIN` 表⑴ 即是
* 指定特定期間 → 小心過濾 `LEFT JOIN`

## 57：旋轉資料表

- 旋轉：將原本逐筆列出的資料，改為逐欄顯示。 (為了製作報表)
- 問題：2015 年每位經紀人各個月的合約統計
- 嘗試㈠：(逐筆列出) 簡單地 `GROUP BY`
* 不易比較各經紀人的表現
* 以季為單位統計，而 Q1 從 5/1 開始 (會計季)。
- 嘗試㈡：(旋轉資料) 各欄位利用 `CASE WHEN` 過濾當月份資料
* 據 會計季 統計將更不容易實作
- 嘗試㈢：據每季日期範圍，建立對應表。
* 具 Q1-Q4 四個欄位，當季者填 1 其他填 0。
* 查詢時各欄位便能與之相乘，以替代 `CASE WHEN`。
- 各系統另有 (非標準的) 旋轉方式

# CH10