Корельовані підзапити

Маніпулювання даними в SQL

Mona Khalil

Data Scientist, Greenhouse Software

Корельований підзапит

Використовують значення з зовнішнього запиту для формування результату
Перезапускаються для кожного рядка фінального набору даних
Застосовуються для складного об'єднання, фільтрації та оцінювання даних

Простий приклад

На яких стадіях матчу зазвичай забивають більше за середнє голів?

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');

Простий приклад

На яких стадіях матчу зазвичай забивають більше за середнє голів?

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 -- Підзапит у FROM
WHERE s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                     FROM match 
                     WHERE season = '2012/2013'); -- Підзапит у WHERE

Корельований приклад

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

Корельований приклад

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

Прості vs. корельовані підзапити

Простий підзапит

Можна виконати незалежно від основного запиту
Обчислюється один раз у всьому запиті

Корельований підзапит

Залежить від основного запиту для виконання
Обчислюється в циклах
- Відчутно уповільнює виконання запиту

Корельовані підзапити

Яка середня кількість голів у кожній країні?

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 |

Корельовані підзапити

Яка середня кількість голів у кожній країні?

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 |

Давайте потренуємось!

Маніпулювання даними в SQL