Pencere Fonksiyonları

SQL ile Veri İşleme

Mona Khalil

Data Scientist, Greenhouse Software

Toplu değerlerle çalışmak

Tüm toplu olmayan sütunlar ile GROUP BY kullanmanızı gerektirir

SELECT
  country_id,
  season,
  date,
  AVG(home_goal) AS avg_home
FROM match
GROUP BY country_id;

ERROR: column "match.season" must appear in the GROUP BY 
clause or be used in an aggregate function

Pencere fonksiyonlarına giriş

Önceden üretilmiş bir sonuç kümesi (bir pencere) üzerinde hesaplama yapar
Toplulaştırma hesaplamaları
- SELECT içindeki alt sorgulara benzer
- Kümülatif toplamlar, sıralamalar, hareketli ortalamalar

Pencere fonksiyonu nedir?

2011/2012'de her maçta kaç gol atıldı ve bu ortalama ile nasıl karşılaştırılır?

SELECT
  date,
  (home_goal + away_goal) AS goals,
  (SELECT AVG(home_goal + away_goal) 
     FROM match
     WHERE season = '2011/2012') AS overall_avg
FROM match
WHERE season = '2011/2012';

| date       | goals | overall_avg       |
|------------|-------|-------------------|
| 2011-07-29 | 3     | 2.71646           |
| 2011-07-30 | 2     | 2.71646           |
| 2011-07-30 | 4     | 2.71646           |
| 2011-07-30 | 1     | 2.71646           |

Pencere fonksiyonu nedir?

2011/2012'de her maçta kaç gol atıldı ve bu ortalama ile nasıl karşılaştırılır?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    AVG(home_goal + away_goal) OVER() AS overall_avg
FROM match
WHERE season = '2011/2012';

| date       | goals | overall_avg       |
|------------|-------|-------------------|
| 2011-07-29 | 3     | 2.71646           |
| 2011-07-30 | 2     | 2.71646           |
| 2011-07-30 | 4     | 2.71646           |
| 2011-07-30 | 1     | 2.71646           |

RANK Oluşturma

Maçlar, atılan gol sayısına göre kaçıncı sıradadır?

SELECT
    date,
    (home_goal + away_goal) AS goals
FROM match
WHERE season = '2011/2012';

| date       | goals |
|------------|-------|
| 2011-07-29 | 3     |
| 2011-07-30 | 2     |
| 2011-07-30 | 4     |
| 2011-07-30 | 1     |

RANK Oluşturma

Maçlar, atılan gol sayısına göre kaçıncı sıradadır?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    RANK() OVER(ORDER BY home_goal + away_goal) AS goals_rank
FROM match
WHERE season = '2011/2012';

| date       | goals | goals_rank |
|------------|-------|------------|
| 2012-04-28 | 0     | 1          |
| 2011-12-26 | 0     | 1          |
| 2011-09-10 | 0     | 1          |
| 2011-08-27 | 0     | 1          |
| 2012-01-07 | 0     | 1          |

RANK Oluşturma

Maçlar, atılan gol sayısına göre kaçıncı sıradadır?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    RANK() OVER(ORDER BY home_goal + away_goal DESC) AS goals_rank
FROM match
WHERE season = '2011/2012';

| date       | goals | goals_rank |
|------------|-------|------------|
| 2011-11-06 | 10    | 1          |
| 2011-08-28 | 10    | 1          |
| 2012-05-12 | 9     | 3          |
| 2012-02-12 | 9     | 3          |
| 2012-03-09 | 9     | 3          |

Temel farklar

Sorgunun ORDER BY hariç tüm kısımlarından sonra işlenir
- Veritabanı yerine sonuç kümesindeki bilgiyi kullanır
PostgreSQL, Oracle, MySQL, SQL Server'da mevcut...
- ...ama SQLite'ta DEĞİL

Haydi pratik yapalım!

SQL ile Veri İşleme