Truy vấn lồng tương quan

Xử lý dữ liệu trong SQL

Mona Khalil

Data Scientist, Greenhouse Software

Truy vấn lồng tương quan

Dùng giá trị từ truy vấn bên ngoài để tạo kết quả
Chạy lại cho mỗi hàng trong tập kết quả cuối
Dùng cho nối bảng nâng cao, lọc và đánh giá dữ liệu

Ví dụ đơn giản

Những vòng đấu nào có xu hướng nhiều bàn thắng hơn mức trung bình?

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

Ví dụ đơn giản

Những vòng đấu nào có xu hướng nhiều bàn thắng hơn mức trung bình?

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 trong FROM
WHERE s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                     FROM match 
                     WHERE season = '2012/2013'); -- Subquery trong WHERE

Ví dụ tương quan

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

Ví dụ tương quan

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

Truy vấn lồng đơn giản vs. tương quan

Truy vấn lồng đơn giản

Có thể chạy độc lập với truy vấn chính
Chỉ được tính một lần trong toàn bộ truy vấn

Truy vấn lồng tương quan

Phụ thuộc vào truy vấn chính để thực thi
Được đánh giá trong vòng lặp
- Làm chậm thời gian chạy đáng kể

Truy vấn lồng tương quan

Trung bình số bàn thắng ở mỗi quốc gia là bao nhiêu?

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 |

Truy vấn lồng tương quan

Trung bình số bàn thắng ở mỗi quốc gia là bao nhiêu?

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 |

Hãy thực hành!

Xử lý dữ liệu trong SQL