你寫的不是 SQL 看到的

改進 PostgreSQL 的查詢效能

Amy McCarty

Instructor

代數的運算順序

 

  • 詞法順序（書寫）
  • 邏輯順序（執行）

 

PEMDAS BODMAS
Parenthesis Brackets
Exponents Order
Multiplication /Division Division /Multiplication
Addition /Subtraction Addition /Subtraction
改進 PostgreSQL 的查詢效能

套用運算順序

 

詞法：

$$ x = 2 + (8 + 4) \div 2 $$

$$ x = 10 + 4) \div 2 $$

$$ x = 14 \div 2 $$

$$ x = 7 $$

 

邏輯：

$$ x = 2 + (8 + 4) \div 2 $$

$$ x = 2 + \frac{12}{2} $$

$$ x = 2 + 6 $$

$$ x = 8 $$

改進 PostgreSQL 的查詢效能

SQL 的邏輯運算順序

順序 子句 目的
1 FROM 指向資料表；若有連接則指定多表
2 WHERE 篩選或限制紀錄
3 GROUP BY 將紀錄分組
4 SUM()、COUNT() 等 彙總
5 SELECT 指定要回傳的欄位 
SELECT COUNT(*) FROM tableA WHERE col1 = 77
改進 PostgreSQL 的查詢效能

Group by 與彙總

event_location storm elements days
Russia blizzard water 1
Argentina tornado water 1
Argentina tornado wind 1
Australia tornado wind 1
Kuwait haboob wind 2
USA haboob wind 2 
SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements

 

No output - 
your code generated an error

column "storm" must appear in the 
GROUP BY clause or be used in an 
aggregate function
改進 PostgreSQL 的查詢效能

Group by 與彙總的運算順序

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements
順序 SQL 子句 可用欄位
1 FROM weather_events all
3 GROUP BY elements elements
4 COUNT elements
改進 PostgreSQL 的查詢效能

Group by 必須對應彙總欄位

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements, storm
改進 PostgreSQL 的查詢效能

Group by 必須對應彙總欄位

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements, storm

結果

elements storm count
water blizzard 1
water tornado 1
wind tornado 2
wind haboob 2
改進 PostgreSQL 的查詢效能

SQL 邏輯運算順序（續）

順序 子句 目的
...
5 SELECT 指定要回傳的欄位
6 DISTINCT 去除重複
7 ORDER BY 排序結果
8 LIMIT 限制筆數
改進 PostgreSQL 的查詢效能

DISTINCT 與 LIMIT

row_no location storm elements days
1 Russia blizzard water 1
2 Argentina tornado water 1
3 Argentina tornado wind 1
4 Australia tornado wind 1
5 Kuwait haboob wind 2
6 USA haboob wind 2 
SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
改進 PostgreSQL 的查詢效能
row_no location storm elements days
1 Russia blizzard water 1
2 Argentina tornado water 1
3 Argentina tornado wind 1
4 Australia tornado wind 1
5 Kuwait haboob wind 2
6 USA haboob wind 2 
SELECT DISTINCT storm, elements
FROM weather_events 
ORDER BY storm LIMIT 3
改進 PostgreSQL 的查詢效能
row_no location storm elements days
1 Russia blizzard water 1
2 Argentina tornado water 1
3 Argentina tornado wind 1
4 Australia tornado wind 1
5 Kuwait haboob wind 2
6 USA haboob wind 2 
SELECT DISTINCT storm, elements
FROM weather_events 
ORDER BY storm LIMIT 3
順序 SQL 子句 可用列
1 FROM weather_events all
改進 PostgreSQL 的查詢效能
row_no storm elements
1 blizzard water
2 tornado water
3 tornado wind
5 haboob wind

 

 

SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
順序 SQL 子句 可用列
1 FROM weather_events all
5 SELECT storm, elements all
改進 PostgreSQL 的查詢效能
row_no storm elements
1 blizzard water
2 tornado water
3 tornado wind
5 haboob wind

 

 

SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
順序 SQL 子句 可用列
1 FROM weather_events all
5 SELECT storm, elements all
6 DISTINCT 1, 2, 3, 5
改進 PostgreSQL 的查詢效能
row_no storm elements
1 blizzard water
5 haboob wind
2 tornado water
3 tornado wind

 

 

SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
順序 SQL 子句 可用列
1 FROM weather_events all
5 SELECT storm, elements all
6 DISTINCT 1, 2, 3, 5
7 ORDER BY storm 1, 5, 2, 3
改進 PostgreSQL 的查詢效能
row_no storm elements
1 blizzard water
5 haboob wind
2 tornado water

 

 

 

SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
順序 SQL 子句 可用列
1 FROM weather_events all
5 SELECT storm, elements all
6 DISTINCT 1, 2, 3, 5
7 ORDER BY storm 1, 5, 2, 3
8 LIMIT 3 1, 5, 2
改進 PostgreSQL 的查詢效能
順序 子句 目的 限制
1 FROM 指向資料表
2 WHERE 篩選或限制紀錄 筆數
3 GROUP BY 將紀錄分組 欄數
4 SUM、COUNT 等 彙總 筆數
5 SELECT 指定要回傳的欄位 欄數
6 DISTINCT 去除重複 筆數
7 ORDER BY 排序結果
8 LIMIT 篩選紀錄 筆數
改進 PostgreSQL 的查詢效能

一起來練習吧！

改進 PostgreSQL 的查詢效能

Preparing Video For Download...