Apa yang Anda tulis bukan yang dilihat SQL

Meningkatkan Performa Kueri di PostgreSQL

Amy McCarty

Instructor

Urutan operasi aljabar

 

  • Leksikal (sesuai penulisan)
  • Logis (sesuai eksekusi)

 

PEMDAS BODMAS
Parenthesis Brackets
Exponents Order
Multiplication /Division Division /Multiplication
Addition /Subtraction Addition /Subtraction
Meningkatkan Performa Kueri di PostgreSQL

Menerapkan urutan operasi

 

Leksikal:

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

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

$$ x = 14 \div 2 $$

$$ x = 7 $$

 

Logis:

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

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

$$ x = 2 + 6 $$

$$ x = 8 $$

Meningkatkan Performa Kueri di PostgreSQL

Urutan operasi logis SQL

Urutan Klausa Tujuan
1 FROM Menunjuk ke tabel atau beberapa tabel jika kueri memakai join
2 WHERE Memfilter atau membatasi rekaman
3 GROUP BY Mengelompokkan rekaman
4 SUM(), COUNT(), dll. Agregasi
5 SELECT menentukan kolom yang dikembalikan 
SELECT COUNT(*) FROM tableA WHERE col1 = 77
Meningkatkan Performa Kueri di PostgreSQL

GROUP BY dan agregasi

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
Meningkatkan Performa Kueri di PostgreSQL

Urutan operasi GROUP BY dan agregasi

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements
urutan klausa SQL kolom tersedia
1 FROM weather_events semua
3 GROUP BY elements elements
4 COUNT elements
Meningkatkan Performa Kueri di PostgreSQL

GROUP BY harus cocok dengan agregasi

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements, storm
Meningkatkan Performa Kueri di PostgreSQL

GROUP BY harus cocok dengan agregasi

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

Hasil

elements storm count
water blizzard 1
water tornado 1
wind tornado 2
wind haboob 2
Meningkatkan Performa Kueri di PostgreSQL

Urutan operasi logis SQL (lanjutan)

Urutan Klausa Tujuan
...
5 SELECT menentukan kolom yang dikembalikan
6 DISTINCT menghapus duplikasi
7 ORDER BY mengurutkan hasil
8 LIMIT mengurangi baris
Meningkatkan Performa Kueri di PostgreSQL

Distinct dan 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
Meningkatkan Performa Kueri di 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
Meningkatkan Performa Kueri di 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
urutan klausa SQL baris tersedia
1 FROM weather_events semua
Meningkatkan Performa Kueri di 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
urutan klausa SQL baris tersedia
1 FROM weather_events semua
5 SELECT storm, elements semua
Meningkatkan Performa Kueri di 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
urutan klausa SQL baris tersedia
1 FROM weather_events semua
5 SELECT storm, elements semua
6 DISTINCT 1, 2, 3, 5
Meningkatkan Performa Kueri di 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
urutan klausa SQL baris tersedia
1 FROM weather_events semua
5 SELECT storm, elements semua
6 DISTINCT 1, 2, 3, 5
7 ORDER BY storm 1, 5, 2, 3
Meningkatkan Performa Kueri di 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
urutan klausa SQL baris tersedia
1 FROM weather_events semua
5 SELECT storm, elements semua
6 DISTINCT 1, 2, 3, 5
7 ORDER BY storm 1, 5, 2, 3
8 LIMIT 3 1, 5, 2
Meningkatkan Performa Kueri di PostgreSQL
Urutan Klausa Tujuan Batas
1 FROM menunjuk ke tabel
2 WHERE memfilter atau membatasi rekaman # baris
3 GROUP BY mengelompokkan rekaman # kolom
4 SUM, COUNT, dll. agregasi # baris
5 SELECT menentukan kolom yang dikembalikan # kolom
6 DISTINCT menghapus duplikasi # baris
7 ORDER BY mengurutkan hasil
8 LIMIT memfilter rekaman # baris
Meningkatkan Performa Kueri di PostgreSQL

Ayo berlatih!

Meningkatkan Performa Kueri di PostgreSQL

Preparing Video For Download...