İç içe alt sorgular

SQL ile Veri İşleme

Mona Khalil

Data Scientist, Greenhouse Software

İç içe alt sorgular?

Başka bir alt sorgunun içinde alt sorgu
Birden çok dönüşüm katmanı uygula

Bir alt sorgu...

Her ülkenin ortalaması genel ortalamadan ne kadar sapıyor?

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;

Bir alt sorgu...

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

...bir alt sorgunun içinde!

Her ayın toplam golü, ortalama aylık toplamdan nasıl sapıyor?

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;

İç alt sorgu

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   |

Dış alt sorgu

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

Nihai sorgu

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  |

İlişkili iç içe alt sorgular

İç içe alt sorgular ilişkili veya ilişkisiz olabilir
- Ya da... ikisinin birleşimi
- Dış alt sorgu veya ana sorgudan referans alabilir

İlişkili iç içe alt sorgular

2011/2012 sezonunda her ülkenin ortalama golü nedir?

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;

İlişkili iç içe alt sorgular

2011/2012 sezonunda her ülkenin ortalama golü nedir?

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 -- İç alt sorgu başlangıcı
             FROM match
             WHERE season = '2011/2012')) AS avg_goals 
FROM country AS c
GROUP BY country;

İlişkili iç içe alt sorgu

2011/2012 sezonunda her ülkenin ortalama golü nedir?

SELECT
  c.name AS country,
  (SELECT AVG(home_goal + away_goal)
   FROM match AS m
   WHERE m.country_id = c.id -- Ana sorguyla ilişkilendirir
         AND id IN (
             SELECT id -- İç alt sorgu başlangıcı
             FROM match
             WHERE season = '2011/2012')) AS avg_goals 
FROM country AS c
GROUP BY country;

İlişkili iç içe alt sorgular

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

Hadi pratik yapalım!

SQL ile Veri İşleme