SQL WORKSHOP

Become a Mochi that writes SQL!

3

Fumi 2022/05

Who is FUMI

  • 麻吉村的後端工程師之一
  • 喜歡茶、咖啡、涼涼的山、看動畫、唱日卡（宅
  • 之後有問題都可以敲敲 slack or 找我討論

複習

review

第一題 JOIN & WHERE

加油請求(refueling_request)上會紀錄這次請求的油量(quantity)和對應到的加油站 ID(gas_station_id)。

 

今天想撈取油量大於 55 的加油請求之加油請求 ID、加油請求油量、加油站名稱(gas_stations.name)，並以加油請求 ID 由小至大排序

第一題 JOIN & WHERE

今天想撈取油量大於 55 的加油請求之加油請求 ID、加油請求油量、加油站名稱(gas_stations.name)，並以加油請求 ID 由小至大排序

SELECT：加油請求 ID、加油請求油量、加油站名稱

FROM：加油請求

JOIN：加油站

ORDER：加油請求 ID 由小至大

第一題 JOIN & WHERE

今天想撈取油量大於 55 的加油請求之加油請求 ID、加油請求油量、加油站名稱(gas_stations.name)，並以加油請求 ID 由小至大排序

SELECT 
refueling_requests.id
refueling_requests.quantity
gas_stations.name
FROM refueling_requests
JOIN gas_stations 
  ON refueling_requests.gas_station_id = gas_stations.id
WHERE refueling_requests.quantity > 55
ORDER BY refueling_requests.id

第二題 JOIN & GROUP BY

有兩間出色的加油站(gas_stations)，名稱(name)分別是 Happy Mochi Station 與 Unhappy Mochi Station QQ。想獲取這兩間加油站的總發油量，結果按照總油量由大至小排序，希望撈取的結果如下圖所示： 

gas_station_name total_quantity
Happy Mochi Station xxx(某個數字)
Unhappy Mochi Station QQ xxx(某個數字)

總加油量可以透過加油站的加油請求(refueling_requests)上的油量(quantity)加總所得

第二題 JOIN & GROUP BY

想獲取 Happy Mochi Station 與 Unhappy Mochi Station QQ 這兩間加油站的總發油量，結果按照總油量由大至小排序

SELECT：加油站名稱、加油站之加油請求油量加總

FROM：加油請求

JOIN：加油站

WHERE：名稱是 Happy Mochi Station 或 Unhappy Mochi Station

GROUP BY：加油站名稱

ORDER：加油站之加油請求油量加總 由大至小

第二題 JOIN & GROUP BY

想獲取 Happy Mochi Station 與 Unhappy Mochi Station QQ 這兩間加油站的總發油量，結果按照總油量由大至小排序

SELECT 
gas_stations.name AS gas_station_name,
SUM(refueling_requests.quantity) AS total_quantity
FROM gas_stations
JOIN refueling_requests 
  ON refueling_requests.gas_station_id = gas_stations.id
WHERE gas_stations.name 
  IN ('Happy Mochi Station', 'Unhappy Mochi Station QQ')
GROUP BY gas_stations.name
ORDER BY total_quantity DESC

AUTOPASS 結構說明

Autoplus Schema

前情提要

  • 絕大多數的表都有 created_at(新增時間)、updated_at(更新時間)
  • 絕大多數的表都有 ID，為 uuid 的型別

今天會提到：

  • 使用者 users
  • 訂單 orders
  • 付款紀錄 payments
  • 停車紀錄 parking_records
  • 加油請求 refueling_requests

使用者 UsERS

手機號碼 

email

通路 ID

顯示名稱 

是否為會員

mobile

email

distributor_id

display_name

registered

訂單 Orders

訂單類型 

交易類型 

訂單金額

收入金額 

已退款金額

type

transaction_type

amount

income_amount

refunded_amount

訂單 Orders

加油

停車

路邊停車

連鎖店

PayTaipei

月租

RefuelingOrder

ParkingOrder

OnStreetParkingOrder

InvoiceOrder

PayTaipeiOrder

LeaseOrder

訂單類型 type

訂單 Orders

加油

停車

路邊停車

PayTaipei

月租

refueling

parking

on_street_parking

pay_taipei

lease

交易類型 transaction_type

訂單 Orders

汽車美容

保險

洗車

充電

租車

維修保養

加油代收

停車場代收

悠遊付退款

其他代收

vehicle_maintenance
insurance
wash_car
charging
rental_car
vehicle_repair
refueling_payment
parking_payment
easy_wallet_refund
miscellaneous

交易類型 transaction_type

PRACTICE 1

找一筆退款金額為 20，

交易類型為加油站代收的連鎖店訂單，

撈其 ID、訂單類型、交易類型、收入金額、退款金額

PRACTICE 1

找一筆退款金額為 20，

交易類型為加油站代收的連鎖店訂單，

撈其 ID、訂單類型、交易類型、收入金額、退款金額

SELECT： ID、訂單類型、交易類型、收入金額、退款金額

FROM：orders

WHERE：退款金額為 20 且 交易類型為加油站代收

LIMIT：1

PRACTICE 1

找一筆退款金額為 20，

交易類型為加油站代收的連鎖店訂單，

撈其 ID、訂單類型、交易類型、收入金額、退款金額

SELECT id, type, transaction_type, income_amount, 
  refunded_amount
FROM orders 
WHERE refunded_amount = 20 
  AND transaction_type = 'refueling_payment'  
LIMIT 1

訂單 Orders

車牌 

使用者 ID

通路 ID

錢包 ID

付款狀態

退款狀態

plate_number

user_id

distributor_id

wallet_id

payment_state

refund_state

訂單 Orders

未付款

已付款

付款失敗

pending

paid

unpaid

付款狀態 payment_state

訂單 Orders

沒退款

退款（包含部分退款）

no_refund

refunded

 

退款狀態 refund_state

PRACTICE 2

找一筆通路 ID 為 ec31ddb3-652b-47cd-b7bd-892a62eca2f3 的部分退款訂單，

撈其 ID、訂單類型、交易類型、收入金額、退款金額

PRACTICE 2

找一筆通路 ID 為 ec31ddb3-652b-47cd-b7bd-892a62eca2f3 的部分退款訂單，

撈其 ID、訂單類型、交易類型、收入金額、退款金額

SELECT： ID、訂單類型、交易類型、收入金額、退款金額

FROM：orders

WHERE：通路 ID 為 ec31ddb3-652b-47cd-b7bd-892a62eca2f3 且 部分退款（？？？）

LIMIT：1

PRACTICE 2

找一筆通路 ID 為 ec31ddb3-652b-47cd-b7bd-892a62eca2f3 的部分退款訂單，

撈其 ID、訂單類型、交易類型、收入金額、退款金額

SELECT： ID、訂單類型、交易類型、收入金額、退款金額

FROM：orders

WHERE：通路 ID 為 ec31ddb3-652b-47cd-b7bd-892a62eca2f3 且 部分退款（？？？）

LIMIT：1

訂單金額 != 退款金額的退款訂單

PRACTICE 2

找一筆通路 ID 為 ec31ddb3-652b-47cd-b7bd-892a62eca2f3 的部分退款訂單，

撈其 ID、訂單類型、交易類型、收入金額、退款金額

SELECT id, type, transaction_type, income_amount, 
  refunded_amount
FROM orders 
WHERE refunded_amount != amount 
  AND refund_state = 'refunded' 
  AND distributor_id = 'ec31ddb3-652b-47cd-b7bd-892a62eca2f3'
LIMIT 1

訂單 Orders

交易來源 ID 

交易來源類型

次要交易來源 ID

次要交易類型 ID

付款時間

main_item_source_id

main_item_source_type

sub_item_source_id

sub_item_source_type

paid_at

訂單 Orders

停車紀錄

加油請求

路停交易

連鎖店請求

PayTaipei 交易

月租合約

ParkingRecord

RefuelingRequest

OnStreetParkingBill

Invoice

PayTaipeiTransaction

LeaseContract

交易來源類型 main_item_source_type

訂單 Orders

停車票

訪客車辨紀錄

ParkingTicket

ParkingPrevalidation

 

次要來源類型 sub_item_source_type

刷卡紀錄 PAYMENTs

類型

狀態

訂單 ID

錢包 ID

原刷卡紀錄 ID

type

state

order_id

wallet_id

refund_from_payment_id

刷卡紀錄 PAYMENTS

付款紀錄

LINE 綁卡紀錄

綁卡紀錄

退款紀錄

(NULL)

LinePayApprovedPayPayment

ValidationPayment

RefundPayment

 type 

刷卡紀錄 PAYMENTS

未經過付款流程

初始化付款流程

完成預授權

授權+請款完成

付款失敗

取消授權

pending
initialized
authorized
paid
unpaid
canceled

state

刷卡紀錄 PAYMENTS

一般付款流程

pending

預授權

pending

authorized

paid

paid

請款

授權

授權請款

canceled

取消授權

刷卡紀錄 PAYMENTS

一次性付款

pending

初始化

pending

initialized

paid

確認付款成功

付款失敗

unpaid

???

刷卡紀錄 PAYMENTs

ID type refund_from_payment_id
3de03676-5a10-4f6b-95dd-2077daabfc7e
9b1234cc-a42e-484b-ae17-8a32fc51c2ae RefundPayment 3de03676-5a10-4f6b-95dd-2077daabfc7e

刷卡紀錄 PAYMENTs

金額

收入金額

付款時間

amount

income

paid_at

 

付款成功才會有收入金額

PRACTICE 3

找 UTC 時間 2022-05-01 08:00:00 到 2022-05-02 08:00:00 之間付款成功的一般付款紀錄（不是綁定紀錄也不是退款紀錄），

撈這些紀錄的收入金額總和

PRACTICE 3

找 UTC 時間 2022-05-01 08:00:00 到 2022-05-02 08:00:00 之間付款成功的一般付款紀錄（不是綁定紀錄也不是退款紀錄），

撈這些紀錄的收入金額總和

SELECT： 收入金額總和

FROM：payments

WHERE：時間為 5/1 且 是一般付款紀錄 且 付款成功

PRACTICE 3

找 UTC 時間 2022-05-01 08:00:00 到 2022-05-02 08:00:00 之間付款成功的一般付款紀錄（不是綁定紀錄也不是退款紀錄），

撈這些紀錄的收入金額總和

SELECT SUM(amount)
FROM payments
WHERE payments.state = 'paid' 
  AND payments.type IS NULL 
  AND paid_at >= '2022-05-01 08:00:00' 
  AND paid_at <= '2022-05-02 08:00:00'

PRACTICE 3

找 UTC 時間 2022-05-01 08:00:00 到 2022-05-02 08:00:00 之間付款成功的一般付款紀錄（不是綁定紀錄也不是退款紀錄），

撈這些紀錄的收入金額總和

SELECT SUM(amount)
FROM payments
WHERE payments.state = 'paid' 
  AND payments.type IS NULL 
  AND paid_at 
    BETWEEN '2022-05-01 08:00:00' 
    AND '2022-05-02 08:00:00'

停車紀錄 parking_records

場站 ID 

場站類型 

車牌

請款金額

撥款金額

store_id

store_type

plate_number

request_amount appropriation_amount

刷卡紀錄 PAYMENTS

停車場

加油站

Parkinglot

GasStation

store_type

停車紀錄 parking_records

停車狀態 

撥款狀態 

進場時間

出場時間

離場時間

撥款請款成功時間

parking_state

  payment_state

checked_in_at

checked_out_at

left_at

paid_at

停車紀錄 PARKING_RECORDS

停車中

正常離場

被業者取消

parking

left

canceled

parking_state

停車紀錄 PARKING_RECORDS

還沒算帳

認列撥款

拒絕撥款

pending

paid

unpaid

payment_state

PRACTICE 4

以下 SQL 可以找到停車場名稱為「掃地僧內湖停車場」的停車場的一筆停車紀錄

SELECT parking_records.id, 
  parking_records.parking_state, 
  parking_records.payment_state
FROM parking_records
JOIN parkinglots 
  ON parkinglots.id = parking_records.store_id 
  AND parking_records.store_type = 'Parkinglot'
WHERE parkinglots.name = '掃地僧內湖停車場'
LIMIT 1

PRACTICE 4

請計算 2022-05-01 08:00:00 之後進場的「掃地僧內湖停車場」的停車紀錄，有幾筆目前是已撥款的？

PRACTICE 4

請計算 2022-05-01 08:00:00 之後進場的「掃地僧內湖停車場」的停車紀錄，有幾筆目前是已撥款的？

SELECT：資料筆數

FROM：停車紀錄

JOIN：停車場

WHERE：停車場名稱為掃地僧內湖停車場 且 進場時間在 2022-05-01 之後 且 撥款狀態是已撥款

PRACTICE 4

請計算 2022-05-01 08:00:00 之後進場的「掃地僧內湖停車場」的停車紀錄，有幾筆目前是已撥款的？

SELECT count(1)
FROM parking_records
JOIN parkinglots 
  ON parkinglots.id = parking_records.store_id 
  AND parking_records.store_type = 'Parkinglot'
WHERE parkinglots.name = '掃地僧內湖停車場'
AND parking_records.checked_in_at >= '2022-05-01 08:00:00'
AND parking_records.payment_state = 'paid'

加油請求 REFUELING_REQuests

加油站 ID  

車牌

油量

油種

請款金額

撥款金額

gas_station_id

plate_number

quantity

sku

request_amount appropriation_amount

停車紀錄 PARKING_RECORDS

92

95

98

柴油

gas_92

gas_95

gas_98

diesel

sku

加油請求 REFUELING_REQuests

狀態 

撥款狀態 

是否為自助加油

加油時間

撥款請款成功時間

state

  payment_state

is_self_service

refueled_at

paid_at

加油請求 REFUELING_REQUESTS

init

waiting

accepted

rejected

canceled

expired

新建

等待使用者回應

接受

拒絕

取消

過期

狀態 state

加油請求 REFUELING_REQUESTS

self_service_waiting
self_service_pre_authorized
self_service_unlocked
self_service_picked
self_service_completed
self_service_captured
self_service_interrupted

等待授權
授權成功
油槍解鎖
已取油槍
加油完成
請款完成
發生錯誤

狀態 state

加油請求 REFUELING_REQUESTS

還沒算帳

認列撥款

拒絕撥款

pending

paid

unpaid

payment_state

問問題時間

有哪張表想多多了解呢？

淺談 Query PLAN

 Introduction of Query Plan

postgres 是怎麼執行我的 SQL 呢？

圖片來源 https://www.interdb.jp/pg/pgsql03.html

The structure of a query plan is a tree of plan nodes

圖片來源 https://www.interdb.jp/pg/pgsql03.html

EXPLAIN

EXPLAIN
SELECT * 
FROM parkinglots

Seq Scan on parkinglots (cost=0.00..48225.97 rows=9897 width=1238)

EXPLAIN (FORMAT JSON) 
SELECT * 
FROM parkinglots
[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "parkinglots",
      "Alias": "parkinglots",
      "Startup Cost": 0.00,
      "Total Cost": 48225.97,
      "Plan Rows": 9897,
      "Plan Width": 1238
    }
  }
]

EXPLAIN

Node Type：node 類型
Relation Name：關聯名稱
Startup Cost：這個步驟開始前的花費成本
Total Cost：這個步驟結束後的花費成本
Plan Rows：node 估計會輸出多少列資料
Plan Width：估計每列資料的平均大小（單位是 byte）

Seq Scan on parkinglots (cost=0.00..48225.97 rows=9897 width=1238)

EXPLAIN

EXPLAIN 
SELECT * 
FROM parkinglots
WHERE created_at > '2022-05-01 08:00:00' 
  AND cooperation_state = 'in_cooperated' 
ORDER BY serial_number

Sort (cost=48275.47..48275.47 rows=1 width=1238)

Sort Key: serial_number

-> Seq Scan on parkinglots (cost=0.00..48275.46 rows=1 width=1238)

Filter: ((created_at > '2022-05-01 08:00:00+00'::timestamp with time zone) AND ((cooperation_state)::text = 'in_cooperated'::text))

EXPLAIN

EXPLAIN
SELECT * 
FROM parking_records
WHERE checked_in_at > '2022-05-01 08:00:00' 
  AND plate_number = 'AAA-1111'

Index Scan using index_parking_records_on_plate_number on parking_records (cost=0.56..1656.96 rows=3 width=630)

Index Cond: ((plate_number)::text = 'AAA-1111'::text)

Filter: (checked_in_at > '2022-05-01 08:00:00'::timestamp without time zone)

EXPLAIN ANALYSE

EXPLAIN
SELECT * 
FROM parkinglots

Seq Scan on parkinglots (cost=0.00..48224.75 rows=9775 width=1243) (actual time=0.010..47.579 rows=9828 loops=1)

Planning Time: 1.377 ms

Execution Time: 48.217 ms

和 EXPLAIN 差異在於會實際執行該 Query

EXPLAIN ANALYSE

actual time：該節點實際執行時間
Planning Time：planning 所花的時間
Execution Time：執行所花的時間

Seq Scan on parkinglots (cost=0.00..48224.75 rows=9775 width=1243) (actual time=0.010..47.579 rows=9828 loops=1)

Planning Time: 1.377 ms

Execution Time: 48.217 ms

EXPLAIN ANALYSE

EXPLAIN ANALYSE
SELECT * 
FROM parkinglots
WHERE created_at > '2022-05-01 08:00:00' 
  AND cooperation_state = 'in_cooperated' 
ORDER BY serial_number

Sort (cost=48290.72..48290.72 rows=1 width=1243) (actual time=43.210..43.212 rows=0 loops=1)

Sort Key: serial_number

Sort Method: quicksort Memory: 25kB

-> Seq Scan on parkinglots (cost=0.00..48290.71 rows=1 width=1243) (actual time=43.187..43.188 rows=0 loops=1)

Filter: ((created_at > '2022-05-01 08:00:00+00'::timestamp with time zone) AND ((cooperation_state)::text = 'in_cooperated'::text))

Rows Removed by Filter: 9829

Planning Time: 3.399 ms

Execution Time: 43.412 ms

EXPLAIN ANALYSE

EXPLAIN ANALYSE
SELECT * 
FROM parking_records
WHERE checked_in_at > '2022-05-01 08:00:00' 
  AND plate_number = 'AAA-1111'

Index Scan using index_parking_records_on_plate_number on parking_records (cost=0.56..1656.96 rows=3 width=630) (actual time=102.074..102.077 rows=1 loops=1)

Index Cond: ((plate_number)::text = 'AAA-1111'::text)

Filter: (checked_in_at > '2022-05-01 08:00:00'::timestamp without time zone)

Rows Removed by Filter: 73

Planning Time: 2.091 ms

Execution Time: 102.428 ms

 恭喜畢業！

大家用 3 小時成為了會寫 SQL 的麻吉

 

給自己一個鼓勵 👏

What NEXT?

  • 語法複習 複習班招生中
  • 更多學習資源 各種書書看起來！
  • 實戰！ 想撈什麼資料？

QuestionS?