Subconsultas anidadas

Manipulación de datos en SQL

Mona Khalil

Data Scientist, Greenhouse Software

¿Subconsultas anidadas?

Subconsulta dentro de otra subconsulta
Realizar varias capas de transformación

Una subconsulta...

¿En qué medida difiere la media de cada país de la media general?

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

Una subconsulta...

| country     | avg_goals | avg_diff |
|-------------|-----------|----------|
| Belgium     | 2.8015    | 0.096    |
| England     | 2.7105    | 0.005    |
| France      | 2.4431    | -0.2624  |
| Germany     | 2.9016    | 0.196    |
| Italy       | 2.6168    | -0.0887  |
| Netherlands | 3.0809    | 0.3754   |
| Poland      | 2.425     | -0.2805  |
| Portugal    | 2.5346    | -0.1709  |
| Scotland    | 2.6338    | -0.0718  |
| Spain       | 2.7671    | 0.0616   |
| Switzerland | 2.9297    | 0.2241   |

... ¡dentro de una subconsulta!

¿En qué se diferencian los goles totales de cada mes de la media mensual de goles totales marcados?

SELECT
  EXTRACT(MONTH FROM date) AS month,
  SUM(m.home_goal + m.away_goal) AS total_goals,
  SUM(m.home_goal + m.away_goal) - 
  (SELECT AVG(goals) 
   FROM (SELECT
           EXTRACT(MONTH FROM date) AS month,
           SUM(home_goal + away_goal) AS goals
         FROM match
         GROUP BY month)) AS avg_diff
FROM match AS m
GROUP BY month;

Subconsulta interna

SELECT
  EXTRACT(MONTH from date) AS month,
  SUM(home_goal + away_goal) AS goals
FROM match
GROUP BY month;

| month | goals |
|-------|-------|
| 01    | 2988  |
| 02    | 3768  |
| 03    | 3936  |
| 04    | 4055  |
| 05    | 2719  |
| 06    | 84    |
| 07    | 366   |

Subconsulta externa

SELECT AVG(goals) 
FROM (SELECT
        EXTRACT(MONTH from date) AS month,
        SUM(home_goal + away_goal) AS goals
FROM match
GROUP BY month)

2944.75

Consulta final

SELECT
  EXTRACT(MONTH FROM date) AS month,
  SUM(m.home_goal + m.away_goal) AS total_goals,
  SUM(m.home_goal + m.away_goal) - 
  (SELECT AVG(goals) 
   FROM (SELECT
           EXTRACT(MONTH FROM date) AS month,
           SUM(home_goal + away_goal) AS goals
         FROM match
         GROUP BY month) AS s) AS diff
FROM match AS m
GROUP BY month;

| month | total_goals | diff     |
|-------|-------------|----------|
| 01    | 5821        | -36.25   |
| 02    | 7448        | 1590.75  |
| 03    | 7298        | 1440.75  |
| 04    | 8145        | 2287.75  |

Subconsultas anidadas correlacionadas

Las subconsultas anidadas pueden ser correlacionadas o no correlacionadas
- O... una combinación de ambas
- Pueden hacer referencia a información de la subconsulta externa o la consulta principal

Subconsultas anidadas correlacionadas

¿Cuál es la media de goles marcados por cada país en la temporada 2011/2012?

SELECT
  c.name AS country,
  (SELECT AVG(home_goal + away_goal)
   FROM match AS m
   WHERE m.country_id = c.id
         AND id IN (
             SELECT id
             FROM match
             WHERE season = '2011/2012')) AS avg_goals 
FROM country AS c
GROUP BY country;

Subconsultas anidadas correlacionadas

¿Cuál es la media de goles marcados por cada país en la temporada 2011/2012?

SELECT
  c.name AS country,
  (SELECT AVG(home_goal + away_goal)
   FROM match AS m
   WHERE m.country_id = c.id
         AND id IN (
             SELECT id -- Begin inner subquery
             FROM match
             WHERE season = '2011/2012')) AS avg_goals 
FROM country AS c
GROUP BY country;

Subconsulta anidada correlacionada

¿Cuál es la media de goles marcados por cada país en la temporada 2011/2012?

SELECT
  c.name AS country,
  (SELECT AVG(home_goal + away_goal)
   FROM match AS m
   WHERE m.country_id = c.id -- Correlates with main query
         AND id IN (
             SELECT id -- Begin inner subquery
             FROM match
             WHERE season = '2011/2012')) AS avg_goals 
FROM country AS c
GROUP BY country;

Subconsultas anidadas correlacionadas

| country     | avg_goals        |
|-------------|------------------|
| Belgium     | 2.87916666666667 |
| England     | 2.80526315789474 |
| France      | 2.51578947368421 |
| Germany     | 2.85947712418301 |
| Italy       | 2.58379888268156 |
| Netherlands | 3.25816993464052 |
| Poland      | 2.19583333333333 |
| Portugal    | 2.64166666666667 |
| Scotland    | 2.6359649122807  |
| Spain       | 2.76315789473684 |
| Switzerland | 2.62345679012346 |

¡Vamos a practicar!

Manipulación de datos en SQL