Vnořené poddotazy

Manipulace s daty v SQL

Mona Khalil

Data Scientist, Greenhouse Software

Vnořené poddotazy?

Poddotaz uvnitř jiného poddotazu
Provádí více vrstev transformace

Poddotaz...

O kolik se průměr každé země lišil od celkového průměru?

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;

Poddotaz...

| 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   |

...uvnitř poddotazu!

O kolik se celkový počet gólů každého měsíce liší od měsíčního průměru vstřelených gólů?

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;

Vnitřní poddotaz

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   |

Vnější poddotaz

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

Výsledný dotaz

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  |

Korelované vnořené poddotazy

Vnořené poddotazy mohou být korelované nebo nekorelované
- Nebo...kombinace obojího
- Mohou odkazovat na data z vnějšího poddotazu nebo hlavního dotazu

Korelované vnořené poddotazy

Jaký byl průměrný počet gólů v každé zemi v sezóně 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;

Korelované vnořené poddotazy

Jaký byl průměrný počet gólů v každé zemi v sezóně 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;

Korelovaný vnořený poddotaz

Jaký byl průměrný počet gólů v každé zemi v sezóně 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;

Korelované vnořené poddotazy

| 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 |

Pojďme si procvičit!

Manipulace s daty v SQL