SQL WORKSHOP
Become a Mochi that writes SQL!
JOIN ...
ON...
2
Fumi 2022/05
Who is FUMI
- 麻吉村的後端工程師之一
- 喜歡茶、咖啡、涼涼的山、看動畫、唱日卡（宅
- 之後有問題都可以敲敲 slack or 找我討論
複習
review
關聯式資料庫
- 資料庫(Databases)裡有資料表(Tables)
- 資料表(Tables)裡有欄位(Columns)
- 欄位有他的結構(structure)跟資料(content)
第一題 LIMIT
撈出一筆 orders，撈其 id、type、(income_amount - refunded_amount)
第一題 LIMIT
撈出一筆 orders，撈其 id、type、(income_amount - refunded_amount)
SELECT： id、type、(income_amount - refunded_amount)
FROM：orders
LIMIT：一筆
第一題 LIMIT
SELECT
id,
type,
(income_amount - refunded_amount)
FROM orders
LIMIT 1撈出一筆 orders，撈其 id、type、(income_amount - refunded_amount)
第一題 LIMIT
SELECT
id,
type,
(income_amount - refunded_amount)
FROM orders
LIMIT 1撈出一筆 orders，撈其 id、type、(income_amount - refunded_amount)
數字欄位可以做加減乘除計算
第二題 WHERE
撈出金額 (income_amount) 大於等於 130 且 type 為 parking 的 orders，
撈其 id、type、income_amount 欄位
第二題 WHERE
撈出金額 (income_amount) 大於等於 130 且 type 為 parking 的 orders，
撈其 id、type、income_amount 欄位
SELECT： id、type、income_amount
FROM：orders
ＷHERE：income_amount 大於等於 130，type 為 parking
第二題 WHERE
撈出金額 (income_amount) 大於等於 130 且 type 為 parking 的 orders，
撈其 id、type、income_amount 欄位
SELECT id, type, income_amount
FROM orders
WHERE income_amount >= 130
AND type = 'parking'第三題 IS NOT NULL
想撈出 username 不是空值(NULL)的 users ，
撈其mobile、username 欄位
第三題 IS NOT NULL
想撈出 username 不是空值(NULL)的 users ，
撈其mobile、username 欄位
SELECT：mobile, username
FROM：users
ＷHERE：username 不是空值(NULL)
第三題 IS NOT NULL
想撈出 username 不是空值(NULL)的 users ，
撈其mobile、username 欄位
SELECT mobile, username
FROM users
WHERE username IS NOT NULL第三題 IS NOT NULL
想撈出 username 不是空值(NULL)的 users ，
撈其mobile、username 欄位
SELECT mobile, username
FROM users
WHERE username IS NOT NULL- 是空值： IS NULL
- 不是空值： IS NOT NULL
第四題 COUNT
想撈出 username 不是空值的 users 的筆數
第四題 COUNT
想撈出 username 不是空值的 users 的筆數
[想法一]
SELECT：COUNT 總筆數
FROM：users
ＷHERE：username 不是空值(NULL)
第四題 COUNT
想撈出 username 不是空值的 users 的筆數
SELECT COUNT(1)
FROM users
WHERE username IS NOT NULL第四題 COUNT
想撈出 username 不是空值的 users 的筆數
[想法二]
SELECT：COUNT username
FROM：users
第四題 COUNT
想撈出 username 不是空值的 users 的筆數
SELECT COUNT(username)
FROM users第五題 GROUP BY
找出訂單最多的使用者前三筆（訂單上的 user_id 若為相同，代表是同一個使用者的訂單），
請撈出 user_id 和訂單數
第五題 GROUP BY
訂單上的 user_id 若為相同，
代表是同一個使用者的訂單
| id | type | user_id |
|---|---|---|
| 1 | parking | 34 |
| 2 | refueling | 34 |
| 3 | parking | 222 |
1 號訂單和 2 號訂單都是 34 號使用者產生的訂單
第五題 GROUP BY
找出訂單最多的使用者 ➡
(1) 根據使用者分群來 COUNT 訂單數量
(2) 有大到小排序，最上面的才是訂單最多的使用者
使用者前三筆 ➡
LIMIT 3
第五題 GROUP BY
找出訂單最多的使用者前三筆（訂單上的 user_id 若為相同，代表是同一個使用者的訂單），
請撈出 user_id 和訂單數
SELECT：user_id 和訂單數（資料筆數）
FROM：orders
GROUP BY：user_id
ORDER BY：訂單數 由大到小排（才能拿到最多的）
LIMIT：3
第五題 GROUP BY
找出訂單最多的使用者前三筆（訂單上的 user_id 若為相同，代表是同一個使用者的訂單），
請撈出 user_id 和訂單數
SELECT user_id, COUNT(1)
FROM orders
GROUP BY user_id
ORDER BY COUNT(1) DESC
LIMIT 3第五題 GROUP BY
找出訂單最多的使用者前三筆（訂單上的 user_id 若為相同，代表是同一個使用者的訂單），
請撈出 user_id 和訂單數
SELECT user_id, COUNT(1) AS order_count
FROM orders
GROUP BY user_id
ORDER BY order_count DESC
LIMIT 3第五題 GROUP BY
找出訂單最多的使用者前三筆（訂單上的 user_id 若為相同，代表是同一個使用者的訂單），
請撈出 user_id 和訂單數
SELECT user_id, COUNT(1) AS order_count
FROM orders
GROUP BY user_id
ORDER BY order_count DESC
LIMIT 3用 alias 也可以
COUNT & GROUP BY
坂下夕里（2021），圖解SQL查詢的基礎知識｜以MySQL為例，碁峰。
關聯
Relations
關聯
兩張表的資料如何關聯在一起？
關聯
| id | name | birth |
|---|---|---|
| 1 | Andy | 2000-01-01 |
| 2 | Carter | 2001-12-31 |
| id | name | birth |
|---|---|---|
| 1 | Andy | 2000-01-01 |
| 2 | Carter | 2001-12-31 |
users
| id | type | user_id |
|---|---|---|
| 1 | Refueling | 1 |
| 2 | Parking | 1 |
orders
關聯
| id | name | birth |
|---|---|---|
| 1 | Andy | 2000-01-01 |
| 2 | Carter | 2001-12-31 |
| id | name | birth |
|---|---|---|
| 1 | Andy | 2000-01-01 |
| 2 | Carter | 2001-12-31 |
users
| id | type | user_id |
|---|---|---|
| 1 | Refueling | 1 |
| 2 | Parking | 1 |
orders
一對一
每個使用者（user）有一個發票資訊（receipt_info）
users
receipt_INFOS
1
1
id ID
name 名稱
id ID
user_id 使用者 ID
number 手機載具號 碼
一對多
每個使用者（user）有很多訂單（orders）
users
Orders
1
*
id ID
name 名稱
id ID
user_id 使用者 ID
type 訂單類型
amount 訂單金額
多對多
每個使用者（user）可以綁多個車牌(vehicle_identities)
同一個車牌可以屬於很多個使用者
users
VeHICLE_INDENTITIES
id ID
name 名稱
id ID
uid 車牌號碼
???
多對多
users
VeHICLE_INDENTITIES
id ID
name 名稱
id ID
uid 車牌號碼
user_vehicle_ships
id ID
user_id 使用者 ID
vehicle_indentity_id 車輛 ID
多對多
users
VeHICLE_INDENTITIES
id ID
name 名稱
id ID
uid 車牌號碼
user_vehicle_ships
id ID
user_id 使用者 ID
vehicle_indentity_id 車輛 ID
1
*
1
1
*
接下來當大家有 diagram 時，就會更好了解每個 table 之間的關係
只是現在的 Autoplus 沒有這樣的 diagram QQ
勉強可以參考一下 tka 畫的
SQL
Structured Query Language
JOIN
SELECT users.mobile, orders.*
FROM orders
JOIN users ON users.id = orders.user_id
WHERE users.mobile = '0911222333'撈取手機為 0911222333 的使用者們的訂單資料
| mobile | id | type | amount | ... |
|---|---|---|---|---|
| 0911222333 | 1 | parking | 30 | ... |
| 0911222333 | 2 | refueling | 1083 | ... |
JOIN
| A | aaa | ... |
| B | bbb | ... |
| C | ccc | ... |
| C | ... |
| A | ... |
| C | ... |
| B | ... |
| NULL | ... |
JOIN
| C | ... |
| A | ... |
| C | ... |
| B | ... |
| C | ccc | ... |
| A | aaa | ... |
| C | ccc | ... |
| B | bbb |
JOIN
LEFT JOIN
SELECT staffs.name, staff_sports.name AS sport
FROM staffs
LEFT JOIN staff_sports
ON staffs.favorite_sport_id = staff_sports.id
工作人員(staffs)喜歡的運動可以透過 favorite_sport_id 找到
想列出所有工作人員的名字與運動的名字（沒有則留空）
| name | sport |
|---|---|
| Fumi | dancing |
| Xxx | NULL |
| Ooo | hiking |
LEFT JOIN
| A | aaa | ... |
| B | bbb | ... |
| C | ccc | ... |
| C | ... |
| A | ... |
| C | ... |
| B | ... |
| NULL | ... |
LEFT JOIN
| C | ... |
| A | ... |
| C | ... |
| B | ... |
| NULL | ... |
| C | ccc | ... |
| A | aaa | ... |
| C | ccc | ... |
| B | bbb | |
| NULL | NULL | ... |
SUBQUERY
SELECT orders.id, orders.type,
orders.income_amount
FROM orders
WHERE user_id IN
(SELECT id FROM users
WHERE mobile = '0911222333')撈取手機為 0911222333 的使用者們的訂單資料
| id | type | amount | ... |
|---|---|---|---|
| 1 | parking | 30 | ... |
| 2 | refueling | 1083 | ... |
WITH AS
當 SQL 組得很複雜...
SELECT ...
FROM 表A
JOIN 表B ON ...
WHERE 欄位 IN (
SELECT ...
FROM (
SELECT ...
FROM 表C
GROUP BY ...
) AS 表D
JOIN 表E ON ...
WHERE ...
)https://www.postgresql.org/docs/current/queries-with.html#id-1.5.6.12.7
WITH AS
整理 SQL 的魔法：
一般資料表運算式(Common Table Expression，CTE)！
WITH AS
WITH specified_mobile_users AS (
SELECT id
FROM users
WHERE mobile = '0911222333')
SELECT orders.id, orders.type,
orders.income_amount
FROM orders
WHERE user_id IN (
SELECT id
FROM specified_mobile_users
)例子一：撈取手機為 0911222333 的使用者們的訂單資料
WITH AS
WITH high_value_refueling_order_count AS (
SELECT distributor_id, SUM(income_amount) AS refueling_amount
FROM orders
JOIN users ON users.id = orders.user_id
WHERE type = 'refueling' AND income_amount > 2000
GROUP BY distributor_id
), high_value_parking_order_count AS (
SELECT distributor_id, SUM(income_amount) AS parking_amount
FROM orders
JOIN users ON users.id = orders.user_id
WHERE type = 'parking' AND income_amount > 100
GROUP BY distributor_id
)
SELECT distributors.name,
high_value_refueling_order_count.refueling_amount,
high_value_parking_order_count.parking_amount
FROM distributors
JOIN high_value_refueling_order_count
ON high_value_refueling_order_count.distributor_id = distributors.id
JOIN high_value_parking_order_count
ON high_value_parking_order_count.distributor_id = distributors.id
例子二：每個通路的特定條件加油、停車訂單之金額加總
METABASE SQL 變數功能
Metabase SQL Parameters
變數
SELECT users.mobile, orders.id,
orders.type, orders.income_amount
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.mobile = {{ mobile }}以手機作為篩選，找出該手機號碼的使用者們的訂單資料
變數
SELECT users.mobile, orders.id,
orders.type, orders.income_amount
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.mobile = {{ mobile }}以手機作為篩選，找出該手機號碼的使用者們的訂單資料
變數
以手機作為篩選，找出該手機號碼的使用者們的訂單資料
變數
類型一：文本
變數
類型二：數字
欄位篩選條件
SELECT users.mobile, orders.id,
orders.type, orders.income_amount
FROM orders
JOIN users ON orders.user_id = users.id
WHERE {{ mobile }}以手機作為篩選，找出該手機號碼的使用者們的訂單資料
欄位篩選條件
以手機作為篩選，找出該手機號碼的使用者們的訂單資料
欄位篩選條件
以手機作為篩選，找出該手機號碼的使用者們的訂單資料
下回預告
正式站資料案例
資料庫怎麼撈資料？
花了多少時間撈？
QuestionS?
SQL workshop 2
By Meng-Ying Tsai
