Subconsultas correlacionadas
Manipulación de datos en SQL
Mona Khalil
Data Scientist, Greenhouse Software
Subconsulta correlacionada
- Utiliza valores de la consulta externa para generar un resultado
- Repite la ejecución para cada fila generada en el conjunto de datos final
- Se utiliza para la unión avanzada, el filtrado y la evaluación de datos
Un ejemplo sencillo
- ¿En qué fases de partidos se suelen marcar más goles que la 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 ejemplo sencillo
- ¿En qué fases de partidos se suelen marcar más goles que la 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 ejemplo correlacionado
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 ejemplo correlacionado
| stage | avg_goals |
|-------|-----------|
| 3 | 2.83 |
| 4 | 2.8 |
| 6 | 2.78 |
| 8 | 3.09 |
| 10 | 2.96 |
Subconsultas sencillas y correlacionadas
Subconsulta sencilla
- Se puede ejecutar independientemente de la consulta principal
- Evaluado una vez en toda la consulta
Subconsulta correlacionada
- Dependiente de la consulta principal que se ejecuta
- Evaluado en bucles
- Ralentiza significativamente la ejecución de las consultas
Subconsultas correlacionadas
- ¿Cuál es la media de goles marcados en cada país?
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 |
Subconsultas correlacionadas
- ¿Cuál es la media de goles marcados en cada país?
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 |
¡Vamos a practicar!
Manipulación de datos en SQL
