Subkueri di mana-mana! Dan praktik terbaik!

Manipulasi Data di SQL

Mona Khalil

Data Scientist, Greenhouse Software

Sebanyak apa pun subkueri yang Anda mau...

Bisa memuat banyak subkueri di SELECT, FROM, WHERE

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

Formatkan kueri Anda

Selaraskan SELECT, FROM, WHERE, dan GROUP BY

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

Anotasi kueri Anda

/* Kueri ini memfilter col1 = 2
 dan hanya memilih data dari table1 */
SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

Anotasi kueri Anda

SELECT
    col1,
    col2,
    col3
FROM table1 -- tabel ini punya 10.000 baris
WHERE col1 = 2; -- Filter WHERE nilai 2

Indentasi kueri Anda

Indentasikan subkueri Anda!

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 IN
        (SELECT id
         FROM table2
         WHERE year = 1991);

Indentasi kueri Anda

SELECT 
  date, 
  hometeam_id, 
  awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win'
       WHEN hometeam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea home loss'
       WHEN awayteam_id = 8455 AND home_goal > away_goal
            THEN 'Chelsea away loss'
       WHEN (hometeam_id = 8455 OR awayteam_id = 8455) 
            AND home_goal = away_goal THEN 'Chelsea Tie'
       END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

Panduan Gaya SQL Holywell

Perlukah subkueri itu?

Subkueri butuh daya komputasi
- Seberapa besar basis data Anda?
- Seberapa besar tabel sumber kueri?
Apakah subkueri itu benar-benar perlu?

Filter setiap subkueri dengan benar!

Perhatikan filter Anda!

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

Ayo berlatih!

Manipulasi Data di SQL