你写的不是 SQL 看到的

提升 PostgreSQL 查询性能

Amy McCarty

Instructor

代数中的运算顺序

 

• 词法（书写顺序）
• 逻辑（执行顺序）

 

PEMDAS	BODMAS
Parenthesis	Brackets
Exponents	Order
Multiplication /Division	Division /Multiplication
Addition /Subtraction	Addition /Subtraction

应用运算顺序

 

词法：

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

SQL 逻辑执行顺序

顺序	子句	作用
1	FROM	指向表（如有 JOIN 包含多表）
2	WHERE	过滤记录
3	GROUP BY	分类分组
4	SUM()、COUNT() 等	聚合
5	SELECT	指定返回列

SELECT COUNT(*) FROM tableA WHERE col1 = 77

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

 

无输出 - 
你的代码产生了错误

列 "storm" 必须出现在 
GROUP BY 子句中，或用于某个
聚合函数

GROUP BY 与聚合的执行顺序

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

顺序	SQL 子句	可用列
1	FROM weather_events	全部
3	GROUP BY elements	elements
4	COUNT	elements

GROUP BY 要与聚合匹配

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

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

SQL 逻辑执行顺序（续）

顺序	子句	作用
...
5	SELECT	指定返回的列
6	DISTINCT	去重
7	ORDER BY	排序
8	LIMIT	截取行数

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

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

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	全部

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	全部
5	SELECT storm, elements	全部

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	全部
5	SELECT storm, elements	全部
6	DISTINCT	1, 2, 3, 5

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	全部
5	SELECT storm, elements	全部
6	DISTINCT	1, 2, 3, 5
7	ORDER BY storm	1, 5, 2, 3

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	全部
5	SELECT storm, elements	全部
6	DISTINCT	1, 2, 3, 5
7	ORDER BY storm	1, 5, 2, 3
8	LIMIT 3	1, 5, 2

顺序	子句	作用	影响
1	FROM	指向表
2	WHERE	过滤记录	行数
3	GROUP BY	分类分组	列数
4	SUM、COUNT 等	聚合	行数
5	SELECT	指定返回列	列数
6	DISTINCT	去重	行数
7	ORDER BY	排序
8	LIMIT	限制记录	行数

Ayo berlatih!

提升 PostgreSQL 查询性能