玩幾個有感的實用範例， 一次學會 SQL 的 join 與 group by

我認為學 linux 不應該從又煩又無趣的安裝學起； 同樣地， 學 SQL 語法也不應該從又煩又無趣的用戶管理和 create table 學起。 SQL 裡面最常用且有點複雜的有趣指令應該非 join 莫屬吧。 想要學會 設計理想的資料庫 schema 可能需要修一門課； 但如果只是想要學會 存取現成的資料庫 其實只需要照著我這兩三篇文章做應該就足以應付大部分的需求了。

首先把最精簡輕巧的 sqlite 與它的前端 litecli 安裝起來並且拿 sakila 範例資料庫試車。 其次要找到你手邊的資料庫的 ER Diagram。 如果找不到， 可以參考 我的第二篇， 自己繪製。 今天這篇 (第三篇) 我們就拿 sakila 範例資料庫來練習 join 與 group by。

再來開一個瀏覽器新分頁或看圖軟體， 隨時檢視 ER Diagram。 我在下 join 指令的時候， 一定都要看著 ER Diagram。 下 litecli sqlite-sakila.db 進入範例資料庫， 然後：

1. 列出所有表格並且約略跟 ER Diagram 對照一下、 熟悉一下： .tables
2. 檢視 address 表格的欄位名稱： pragma table_info(address)
3. 只查看表格中有興趣的欄位： select address, city_id from address
4. 用 join 查表查出城市名稱和國家代號： select address.address, city.city from address join city on city.city_id=address.city_id
5. 一個指令同時做兩個 join 同時印出城市名稱和國家名稱： select address.address, city.city, country.country from address join city on address.city_id=city.city_id join country on city.country_id=country.country_id
6. 同上， 但列印時精簡一點、 佔少一點版面： select address.address, substr(city.city,0,12) as city_name, substr(country.country,0,12) as country_name from address join city on address.city_id=city.city_id join country on city.country_id=country.country_id
7. 建立一個 view： create view addr_list as select address.address_id, address.address, city.city, country.country from address join city on address.city_id=city.city_id join country on city.country_id=country.country_id 可以把 view 想成是 一個唯讀的 table 或是一個複雜的 select 指令的短名簡稱， 也可以拿來跟別的 table 做 join。 這裡建這個 view 的目的就是要讓後面的作業變得簡短一點。

大多數的 join 指令基本上就只是用代號查表而已； 而查表的依據當然就是 ER Diagram 圖中的箭頭， 原則上都是長這樣： [主要資料表的外鍵]=[參考表格的主鍵]。 操作提示： 我習慣輸入的順序是 "select from"、 [主要資料表]、 "join"、 [參考表格]、 "on"。 再來依序是 [等號右邊]、 (按幾次 alt-b 向左移動遊標、) [等號左邊]、 "="、 (按幾次 alt-b 向左移動遊標 到 "select" 與 "from" 中間) [想要列印的欄位]， 這樣才能享受到最多的欄位名稱快打 (補完， completion)。

即使沒有很詳細地研究整個資料庫的 schema 也可以猜得出來： 這是一家 DVD 租賃店 (年輕人知道這是什麼嗎？) 的資料庫。 假設我們想知道哪些客戶是大戶、 對本店貢獻最多的營收。 從 ER Diagram 看得出來， 我們需要 "payment" 跟 "customer" 這兩個表格的資訊。

1. 列出所有付款紀錄的金額跟客戶代號： select customer_id, amount from payment
2. 按照客戶合併多列： select customer.last_name, customer.first_name, sum(payment.amount) from payment join customer on payment.customer_id=customer.customer_id group by payment.customer_id
3. 小數點印兩位就好， 且按照貢獻營收金額排序： select customer.last_name, customer.first_name, round(sum(payment.amount),2) as cust_pay from payment join customer on payment.customer_id=customer.customer_id group by payment.customer_id order by cust_pay desc

最後得到這樣的輸出：

+--------------+-------------+----------+
| last_name    | first_name  | cust_pay |
+--------------+-------------+----------+
| SEAL         | KARL        | 221.55   |
| HUNT         | ELEANOR     | 216.54   |
| SHAW         | CLARA       | 195.58   |
...
| WYMAN        | BRIAN       | 52.88    |
| OBRIEN       | LEONA       | 50.86    |
| BOWMAN       | CAROLINE    | 50.85    |
+--------------+-------------+----------+

最後一句 as cust_pay 的目的是幫這個新欄位取一個名字， 讓後面的 order by 可以稱呼它。 每當使用 group by 的時候， 大概只有兩大類的欄位有意義： (1) 群組的依據本身 (例如 customer_id) 或是相依於它的欄位 (例如 last_name 與 first_name)， 或是 (2) 數值欄位的 "摘要" 例如 sum(), count(), average(), max(), min(), ... 等等， 也就是 敘述統計的統計量數。

以下幾個練習題可以測試你的理解。 試著列出：

1. 每一位演員在多少部影片裡面出現？ 按出現次數由高至低排序， 像 這樣
2. 每一種類別有多少部影片？ 按數量由高至低排序， 像 這樣
3. 每個國家的客戶各貢獻了多少營收？ 按金額由高至低排序， 像 這樣 建議拿上面建立的 addr_list 來 join， 答案會簡短一些。
4. 每一種類別的影片各貢獻了多少營收？ 按金額由高至低排序。

前面幾題的參考解答在網頁原始碼的註解裡面。 題目中 "期待的輸出" 存成 csv 格式。 可以這樣檢視比較好讀： column -tns, q1.csv | less 。 看著 ER Diagram 下 join 的指令， 是不是很簡單呢？ 最後一題沒有參考解答； 提示如下： 因為在貨架上， 每一部片可能不只一個 copy (可能同時會有不只一位客戶想租這一部熱門影片)， inventory 這個表格的每一列代表一個實體的 copy。 這一題牽涉 6 個表格、 5 個 join， 建議用 create view 指令分兩三次完成。

還有， 最後一題算出來的結果全部加總， 會比 select sum(amount) from payment 短少一點點。 可以用 select * from payment where rental_id isnull 查看短少的原因。 原來是因為 payment 表格當中有幾列， 它們指向 rental 表格的那個外鍵欄位 (rental_id) 是空的。 (意味著： 付款原因不明， 不是因為租借。) 因為我們一路都做 inner join， 這幾列就被略過了。 如果當初用 left join， 兩個數字就會相等了。 關於 inner/left/right/outer join， 詳見 圖解。

二到三篇教學文， 學會 DBMS 的基本操作與觀念， 很划算吧？ 沒耐性的學生應該會喜歡。 我知道， 因為我自己就是沒耐性的學生 :-)