相関サブクエリ

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 -- Subquery in FROM
WHERE s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                     FROM match 
                     WHERE season = '2012/2013'); -- Subquery in 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. 相関サブクエリ

単純サブクエリ

メインクエリから独立して実行可能
全体のクエリで 1 回評価

相関サブクエリ

メインクエリの実行に依存
繰り返し評価される
- クエリの実行速度が大幅に低下

相関サブクエリ

各国の平均得点は？

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 のデータ操作