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?