Sottoquery nella clausola FROM
Manipolazione dei dati in SQL
Mona Khalil
Data Scientist, Greenhouse Software
Sottoquery in FROM
- Ristruttura e trasforma i tuoi dati
- Trasforma i dati da lunghi a larghi prima di selezionare
- Prefiltra i dati
- Calcola aggregati di aggregati
- Quali 3 squadre hanno la media più alta di gol in casa?
- Calcola la
AVGper ogni squadra - Ottieni le 3 medie più alte
- Calcola la
- Quali 3 squadre hanno la media più alta di gol in casa?
Sottoquery in FROM...
SELECT
t.team_long_name AS team,
AVG(m.home_goal) AS home_avg
FROM match AS m
LEFT JOIN team AS t
ON m.hometeam_id = t.team_api_id
WHERE season = '2011/2012'
GROUP BY team;
| team | home_avg |
|----------------------|------------------|
| 1. FC Köln | 1.13725490196078 |
| 1. FC Nürnberg | 1.27058823529412 |
| 1. FSV Mainz 05 | 1.43697478991597 |
| AC Ajaccio | 1.12280701754386 |
...alle query principali!
FROM (SELECT
t.team_long_name AS team,
AVG(m.home_goal) AS home_avg
FROM match AS m
LEFT JOIN team AS t
ON m.hometeam_id = t.team_api_id
WHERE season = '2011/2012'
GROUP BY team)
...alle query principali!
FROM (SELECT
t.team_long_name AS team,
AVG(m.home_goal) AS home_avg
FROM match AS m
LEFT JOIN team AS t
ON m.hometeam_id = t.team_api_id
WHERE season = '2011/2012'
GROUP BY team) AS subquery
...alle query principali!
SELECT team, home_avg
FROM (SELECT
t.team_long_name AS team,
AVG(m.home_goal) AS home_avg
FROM match AS m
LEFT JOIN team AS t
ON m.hometeam_id = t.team_api_id
WHERE season = '2011/2012'
GROUP BY team) AS subquery
...alle query principali!
SELECT team, home_avg
FROM (SELECT
t.team_long_name AS team,
AVG(m.home_goal) AS home_avg
FROM match AS m
LEFT JOIN team AS t
ON m.hometeam_id = t.team_api_id
WHERE season = '2011/2012'
GROUP BY team) AS subquery
ORDER BY home_avg DESC
LIMIT 3;
| team | home_avg |
|----------------|----------|
| FC Barcelona | 3.8421 |
| Real Madrid CF | 3.6842 |
| PSV | 3.3529 |
Cose da ricordare
Puoi creare più sottoquery in una clausola
FROM- Assegna loro un alias!
- Uniscile!
Puoi unire una sottoquery a una tabella in
FROM- Includi colonne di unione in entrambe le tabelle!
Facciamo pratica!
Manipolazione dei dati in SQL
