Wat jij schrijft, is niet wat SQL uitvoert

Queryprestaties verbeteren in PostgreSQL

Amy McCarty

Instructor

Algebraïsche rekenvolgorde

 

  • Lexicaal (zoals geschreven)
  • Logisch (zoals uitgevoerd)

 

PEMDAS BODMAS
Parenthesis Brackets
Exponents Order
Multiplication /Division Division /Multiplication
Addition /Subtraction Addition /Subtraction
Queryprestaties verbeteren in PostgreSQL

Rekenvolgorde toepassen

 

Lexicaal:

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

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

$$ x = 14 \div 2 $$

$$ x = 7 $$

 

Logisch:

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

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

$$ x = 2 + 6 $$

$$ x = 8 $$

Queryprestaties verbeteren in PostgreSQL

SQL logische volgorde

Volgorde Clausule Doel
1 FROM Verwijst naar de tabel(len), ook bij joins
2 WHERE Filtert of beperkt records
3 GROUP BY Groepeert records
4 SUM(), COUNT(), etc. Aggregeert
5 SELECT kiest kolommen om te tonen 
SELECT COUNT(*) FROM tableA WHERE col1 = 77
Queryprestaties verbeteren in PostgreSQL

GROUP BY en aggregaties

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
Queryprestaties verbeteren in PostgreSQL

Volgorde: GROUP BY en aggregaties

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements
volgorde SQL-clausule beschikbare kolommen
1 FROM weather_events alle
3 GROUP BY elements elements
4 COUNT elements
Queryprestaties verbeteren in PostgreSQL

GROUP BY moet overeenkomen met aggregaties

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements, storm
Queryprestaties verbeteren in PostgreSQL

GROUP BY moet overeenkomen met aggregaties

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

Resultaten

elements storm count
water blizzard 1
water tornado 1
wind tornado 2
wind haboob 2
Queryprestaties verbeteren in PostgreSQL

SQL logische volgorde (vervolg)

Volgorde Clausule Doel
...
5 SELECT kiest kolommen om te tonen
6 DISTINCT verwijdert duplicaten
7 ORDER BY sorteert resultaten
8 LIMIT beperkt rijen
Queryprestaties verbeteren in PostgreSQL

DISTINCT en 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
Queryprestaties verbeteren in 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
Queryprestaties verbeteren in 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
volgorde SQL-clausule beschikbare rijen
1 FROM weather_events alle
Queryprestaties verbeteren in 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
volgorde SQL-clausule beschikbare rijen
1 FROM weather_events alle
5 SELECT storm, elements alle
Queryprestaties verbeteren in 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
volgorde SQL-clausule beschikbare rijen
1 FROM weather_events alle
5 SELECT storm, elements alle
6 DISTINCT 1, 2, 3, 5
Queryprestaties verbeteren in 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
volgorde SQL-clausule beschikbare rijen
1 FROM weather_events alle
5 SELECT storm, elements alle
6 DISTINCT 1, 2, 3, 5
7 ORDER BY storm 1, 5, 2, 3
Queryprestaties verbeteren in 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
volgorde SQL-clausule beschikbare rijen
1 FROM weather_events alle
5 SELECT storm, elements alle
6 DISTINCT 1, 2, 3, 5
7 ORDER BY storm 1, 5, 2, 3
8 LIMIT 3 1, 5, 2
Queryprestaties verbeteren in PostgreSQL
Volgorde Clausule Doel Beperkingen
1 FROM verwijst naar de tabel(len)
2 WHERE filtert of beperkt records # rijen
3 GROUP BY groepeert records # kolommen
4 SUM, COUNT, etc. aggregeert # rijen
5 SELECT kiest kolommen om te tonen # kolommen
6 DISTINCT verwijdert duplicaten # rijen
7 ORDER BY sorteert resultaten
8 LIMIT beperkt records # rijen
Queryprestaties verbeteren in PostgreSQL

Laten we oefenen!

Queryprestaties verbeteren in PostgreSQL

Preparing Video For Download...