Sottoquery correlate

Manipolazione dei dati in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Sottoquery correlata

Usa valori dalla query esterna per generare un risultato
Rieseguita per ogni riga generata nel set di dati finale
Usata per join avanzati, filtri e valutazioni

Un esempio semplice

Quali fasi delle partite tendono ad avere un numero di gol superiore alla media?

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

Un esempio semplice

Quali fasi delle partite tendono ad avere un numero di gol superiore alla media?

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

Un esempio correlato

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

Un esempio correlato

| stage | avg_goals |
|-------|-----------|
| 3     | 2.83      |
| 4     | 2.8       |
| 6     | 2.78      |
| 8     | 3.09      |
| 10    | 2.96      |

Sottoquery semplice vs. correlata

Sottoquery semplice

Può essere eseguita indipendentemente dalla query principale
Valutata una volta per l'intera query

Sottoquery correlata

Dipende dalla query principale per l'esecuzione
Valutata in loop
- Rallenta significativamente l'esecuzione

Sottoquery correlate

Qual è il numero medio di gol segnati in ogni paese?

SELECT
  c.name AS country,
  AVG(m.home_goal + m.away_goal) 
     AS avg_goals
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY country;

| country     | avg_goals        |
|-------------|------------------|
| Belgium     | 2.89344262295082 |
| England     | 2.76776315789474 |
| France      | 2.51052631578947 |
| Germany     | 2.94607843137255 |
| Italy       | 2.63150867823765 |
| Netherlands | 3.14624183006536 |
| Poland      | 2.49375          |
| Portugal    | 2.63255360623782 |
| Scotland    | 2.74122807017544 |
| Spain       | 2.78223684210526 |
| Switzerland | 2.81054131054131 |

Sottoquery correlate

Qual è il numero medio di gol segnati in ogni paese?

SELECT
  c.name AS country,
  (SELECT 
     AVG(home_goal + away_goal)
   FROM match AS m
   WHERE m.country_id = c.id) 
     AS avg_goals
FROM country AS c
GROUP BY country;

| country     | avg_goals        |
|-------------|------------------|
| Belgium     | 2.89344262295082 |
| England     | 2.76776315789474 |
| France      | 2.51052631578947 |
| Germany     | 2.94607843137255 |
| Italy       | 2.63150867823765 |
| Netherlands | 3.14624183006536 |
| Poland      | 2.49375          |
| Portugal    | 2.63255360623782 |
| Scotland    | 2.74122807017544 |
| Spain       | 2.78223684210526 |
| Switzerland | 2.81054131054131 |

Facciamo pratica!

Manipolazione dei dati in SQL