# SQL WORKSHOP

Become a Mochi that writes SQL!

3

Fumi 2022/05

# Who is FUMI

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

# 複習

review

## 第一題 JOIN & WHERE

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

FROM：加油請求

JOIN：加油站

ORDER：加油請求 ID 由小至大

## 第一題 JOIN & WHERE

``````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_station_name total_quantity
Happy Mochi Station xxx(某個數字)
Unhappy Mochi Station QQ xxx(某個數字)

## 第二題 JOIN & GROUP BY

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

FROM：加油請求

JOIN：加油站

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

GROUP BY：加油站名稱

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

## 第二題 JOIN & GROUP BY

``````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

email

mobile

email

distributor_id

display_name

registered

type

transaction_type

amount

income_amount

refunded_amount

## 訂單 Orders

PayTaipei

RefuelingOrder

ParkingOrder

OnStreetParkingOrder

InvoiceOrder

PayTaipeiOrder

LeaseOrder

## 訂單 Orders

PayTaipei

refueling

parking

on_street_parking

pay_taipei

lease

## 訂單 Orders

vehicle_maintenance
insurance
wash_car
charging
rental_car
vehicle_repair
refueling_payment
parking_payment
easy_wallet_refund
miscellaneous

## PRACTICE 1

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

FROM：orders

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

LIMIT：1

## PRACTICE 1

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

plate_number

user_id

distributor_id

wallet_id

payment_state

refund_state

pending

paid

unpaid

no_refund

refunded

## PRACTICE 2

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

FROM：orders

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

LIMIT：1

## PRACTICE 2

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

FROM：orders

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

LIMIT：1

## PRACTICE 2

``````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

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

## 訂單 Orders

ParkingTicket

ParkingPrevalidation

## 刷卡紀錄 PAYMENTs

type

state

order_id

wallet_id

refund_from_payment_id

## 刷卡紀錄 PAYMENTS

LINE 綁卡紀錄

(NULL)

LinePayApprovedPayPayment

ValidationPayment

RefundPayment

type

pending
initialized
authorized
paid
unpaid
canceled

state

pending

pending

authorized

paid

paid

canceled

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

amount

income

paid_at

## PRACTICE 3

SELECT： 收入金額總和

FROM：payments

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

## PRACTICE 3

``````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

``````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

store_id

store_type

plate_number

request_amount appropriation_amount

Parkinglot

GasStation

store_type

parking_state

payment_state

checked_in_at

checked_out_at

left_at

paid_at

parking

left

canceled

parking_state

pending

paid

unpaid

payment_state

## PRACTICE 4

``````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

SELECT：資料筆數

FROM：停車紀錄

JOIN：停車場

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

## PRACTICE 4

``````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

gas_station_id

plate_number

quantity

sku

request_amount appropriation_amount

92

95

98

gas_92

gas_95

gas_98

diesel

sku

state

payment_state

is_self_service

refueled_at

paid_at

init

waiting

accepted

rejected

canceled

expired

## 加油請求 REFUELING_REQUESTS

self_service_waiting
self_service_pre_authorized
self_service_unlocked
self_service_picked
self_service_completed
self_service_captured
self_service_interrupted

pending

paid

unpaid

payment_state

# 淺談 Query PLAN

Introduction of Query Plan

postgres 是怎麼執行我的 SQL 呢？

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

## 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 ANALYSE

actual time該節點實際執行時間
Planning Timeplanning 所花的時間
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

## What NEXT?

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