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
WHERE: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
WHERE: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
WHERE: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
SQL workshop 2
- 549