---
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