Quello che scrivi non è ciò che SQL esegue

Migliorare le prestazioni delle query in PostgreSQL

Amy McCarty

Instructor

Ordine delle operazioni algebriche

Lessicale (come scritto)
Logico (come eseguito)

PEMDAS	BODMAS
Parentesi	Brackets
Esponenti	Order
Moltiplicazione /Divisione	Division /Multiplication
Addizione /Sottrazione	Addition /Subtraction

Applicare l'ordine delle operazioni

Lessicale:

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

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

$$ x = 14 \div 2 $$

$$ x = 7 $$

Logico:

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

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

$$ x = 2 + 6 $$

$$ x = 8 $$

Ordine logico SQL

Ordine	Clausola	Scopo
1	FROM	Indica la tabella o le tabelle (se ci sono join)
2	WHERE	Filtra/limita i record
3	GROUP BY	Raggruppa i record
4	SUM(), COUNT(), ecc.	Aggrega
5	SELECT	sceglie le colonne da restituire

SELECT COUNT(*) FROM tableA WHERE col1 = 77

GROUP BY e aggregazioni

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 - 
il tuo codice ha generato un errore

la colonna "storm" deve comparire
nella clausola GROUP BY o essere usata in una
funzione di aggregazione

Ordine tra GROUP BY e aggregazioni

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

ordine	clausola SQL	colonne disponibili
1	FROM weather_events	tutte
3	GROUP BY elements	elements
4	COUNT	elements

GROUP BY deve corrispondere alle aggregazioni

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

GROUP BY deve corrispondere alle aggregazioni

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

Risultati

elements	storm	count
water	blizzard	1
water	tornado	1
wind	tornado	2
wind	haboob	2

Ordine logico SQL (continua)

Ordine	Clausola	Scopo
...
5	SELECT	sceglie le colonne da restituire
6	DISTINCT	rimuove i duplicati
7	ORDER BY	ordina i risultati
8	LIMIT	rimuove righe

DISTINCT e 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

ordine	clausola SQL	righe disponibili
1	FROM weather_events	tutte

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

ordine	clausola SQL	righe disponibili
1	FROM weather_events	tutte
5	SELECT storm, elements	tutte

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

ordine	clausola SQL	righe disponibili
1	FROM weather_events	tutte
5	SELECT storm, elements	tutte
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

ordine	clausola SQL	righe disponibili
1	FROM weather_events	tutte
5	SELECT storm, elements	tutte
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

ordine	clausola SQL	righe disponibili
1	FROM weather_events	tutte
5	SELECT storm, elements	tutte
6	DISTINCT	1, 2, 3, 5
7	ORDER BY storm	1, 5, 2, 3
8	LIMIT 3	1, 5, 2

Ordine	Clausola	Scopo	Limiti
1	FROM	indica la/e tabella/e
2	WHERE	filtra/limita i record	# righe
3	GROUP BY	raggruppa i record	# colonne
4	SUM, COUNT, ecc.	aggrega	# righe
5	SELECT	sceglie le colonne da restituire	# colonne
6	DISTINCT	rimuove i duplicati	# righe
7	ORDER BY	ordina i risultati
8	LIMIT	filtra i record	# righe

Ayo berlatih!

Migliorare le prestazioni delle query in PostgreSQL