Ortak Tablo İfadeleri

SQL ile Veri İşleme

Mona Khalil

Data Scientist, Greenhouse Software

Alt sorgular eklerken...

Sorgu karmaşıklığı hızla artar!
- Bilgiyi takip etmek zorlaşır

Çözüm: Ortak Tablo İfadeleri!

Ortak Tablo İfadeleri

Ortak Tablo İfadeleri (CTE’ler)

Ana sorgudan önce tanımlanan tablo
FROM ifadesinde ad verilerek sonra kullanılır

CTE kurulum

WITH cte AS (
    SELECT col1, col2
    FROM table)

SELECT
    AVG(col1) AS avg_col
FROM cte;

FROM’daki bir alt sorguyu alın

SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10) AS s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

Başa yerleştirin

(
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

Başa yerleştirin

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

CTE’yi gösterin

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)
SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

Tüm CTE’leri gösterin

WITH s1 AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10),
s2 AS (                              -- Yeni alt sorgu
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) <= 1
)
SELECT
  c.name AS country,
  COUNT(s1.id) AS high_scores,
  COUNT(s2.id) AS low_scores         -- Yeni sütun
FROM country AS c
INNER JOIN s1
ON c.id = s1.country_id
INNER JOIN s2                        -- Yeni join
ON c.id = s2.country_id
GROUP BY country;

Neden CTE kullanılır?

Bir kez çalıştırılır
- CTE sonra bellekte tutulur
- Sorgu performansını artırır
Sorgu düzenini iyileştirir
Diğer CTE’lere başvurabilir
Kendi kendine başvurabilir (SELF JOIN)

Hadi pratik yapalım!

SQL ile Veri İşleme