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

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

*

接下來當大家有 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 }}

以手機作為篩選，找出該手機號碼的使用者們的訂單資料

欄位篩選條件

以手機作為篩選，找出該手機號碼的使用者們的訂單資料

欄位篩選條件

以手機作為篩選，找出該手機號碼的使用者們的訂單資料

下回預告

正式站資料案例

資料庫怎麼撈資料？

花了多少時間撈？

SQL WORKSHOP

Who is FUMI

複習

關聯式資料庫

第一題 LIMIT

第一題 LIMIT

第一題 LIMIT

第一題 LIMIT

第二題 WHERE

第二題 WHERE

第二題 WHERE

第三題 IS NOT NULL

第三題 IS NOT NULL

第三題 IS NOT NULL

第三題 IS NOT NULL

第四題 COUNT

第四題 COUNT

第四題 COUNT

第四題 COUNT

第四題 COUNT

第五題 GROUP BY

第五題 GROUP BY

第五題 GROUP BY

第五題 GROUP BY

第五題 GROUP BY

第五題 GROUP BY

第五題 GROUP BY

COUNT & GROUP BY

關聯

關聯

關聯

關聯

一對一

users

receipt_INFOS

一對多

users

Orders

多對多

users

VeHICLE_INDENTITIES

多對多

users

VeHICLE_INDENTITIES

user_vehicle_ships

多對多

users

VeHICLE_INDENTITIES

user_vehicle_ships

SQL

JOIN

JOIN

JOIN

JOIN

LEFT JOIN

LEFT JOIN

LEFT JOIN

SUBQUERY

WITH AS

WITH AS

WITH AS

WITH AS

METABASE SQL 變數功能

變數

變數

變數

變數

變數

欄位篩選條件

欄位篩選條件

欄位篩選條件

下回預告

QuestionS?