Ana içeriğe geç
Kesit SQL
İleri · Veri Bilimi

Korelasyon ve basit doğrusal regresyon

Pearson korelasyonu ve en küçük kareler eğimini SQL ile hesaplamak.

Uygulamada aç, 5 alıştırmayı çözÜcretsiz, hesap gerekmez, kurulum yok.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Pearson korelasyonu

r = (nΣxy − ΣxΣy) / √[(nΣx² − (Σx)²)(nΣy² − (Σy)²)]

PostgreSQL, Snowflake, BigQuery'de CORR(x, y) hazır gelir. Formülle hesaplamak, mantığını anlamak için iyidir. Soru: Ürün fiyatı ile satılan adet arasında ilişki var mı?

WITH d AS (
  SELECT p.price AS x, COALESCE(SUM(oi.quantity), 0) AS y
  FROM products p LEFT JOIN order_items oi ON oi.product_id = p.product_id
  GROUP BY p.product_id, p.price
)
SELECT ROUND(
  (COUNT(*) * SUM(x * y) - SUM(x) * SUM(y)) /
  (SQRT(COUNT(*) * SUM(x * x) - SUM(x) * SUM(x)) * SQRT(COUNT(*) * SUM(y * y) - SUM(y) * SUM(y)))
, 3) AS pearson_r
FROM d;

Doğrusal regresyon (y = a + bx)

Eğim b = (nΣxy − ΣxΣy) / (nΣx² − (Σx)²), kesişim a = ȳ − b·x̄. PostgreSQL'de REGR_SLOPE, REGR_INTERCEPT, REGR_R2.

Korelasyon nedensellik değildirUcuz ürünlerin çok satması, ucuzluğun satışa neden olduğunu kanıtlamaz; ürün türü (kitap vs. monitör) ortak bir etken olabilir.

Derinleş: korelasyonu doğru okumak

Pearson korelasyon katsayısı (r), iki sayısal değişken arasındaki doğrusal ilişkinin yönünü ve gücünü −1 ile +1 arasında ölçer. Formülün eşdeğer bir yazımı ortalamalarla kurulur ve SQL'de daha okunaklıdır:

r = (AVG(xy) − AVG(x)·AVG(y)) / (√(AVG(x²) − AVG(x)²) · √(AVG(y²) − AVG(y)²))

Pedal İzmir'de saatlik yolculuk sayısı ile hava sıcaklığı arasındaki ilişkiye bakalım. Üç aylık 2.208 saatlik gözlemin tamamında r ≈ 0,48 çıkıyor: “sıcak hava bisiklet kullanımını artırıyor” demek cazip. Ama gündüz saatleri hem daha sıcak hem daha kalabalık; asıl ortak etken günün saati. Saati sabitleyince (yalnızca her günün 17:00 gözlemi) tablo değişir:

WITH saatlik AS (
  SELECT strftime('%Y-%m-%d %H:00:00', started_at) AS saat, COUNT(*) AS yolculuk
  FROM bk_trips
  WHERE strftime('%H', started_at) = '17'
  GROUP BY strftime('%Y-%m-%d %H:00:00', started_at)
),
d AS (
  SELECT w.temp_c AS t, w.rain_mm AS r, 1.0 * COALESCE(s.yolculuk, 0) AS y
  FROM bk_weather w
  LEFT JOIN saatlik s ON s.saat = w.obs_hour
  WHERE strftime('%H', w.obs_hour) = '17'
)
SELECT COUNT(*) AS gun,
       ROUND((AVG(t * y) - AVG(t) * AVG(y)) /
             (SQRT(AVG(t * t) - AVG(t) * AVG(t)) * SQRT(AVG(y * y) - AVG(y) * AVG(y))), 3) AS r_sicaklik,
       ROUND((AVG(r * y) - AVG(r) * AVG(y)) /
             (SQRT(AVG(r * r) - AVG(r) * AVG(r)) * SQRT(AVG(y * y) - AVG(y) * AVG(y))), 3) AS r_yagmur
FROM d;

Aynı saat diliminde sıcaklıkla ilişki zayıflar (r ≈ 0,12), yağmurla ilişki ise çok güçlü görünür (r ≈ −0,89). Fakat dikkat: 92 günün yalnızca 4'ünde 17:00'de yağmur var. Bu güçlü katsayıyı birkaç uç gözlem üretiyor; 4 günlük kanıtla “yağmur kullanımı %X düşürür” denmez.

Korelasyon nedensellik değildirOrtak bir etken (saat, mevsim, kampanya), ters yönlü etki (çok satılan ürün indirime girer) veya tesadüf güçlü bir r üretebilir. Nedensellik için deney (A/B testi) ya da etkeni sabitleyen bir analiz gerekir.

Kontrol listesi

Alıştırmalara geç (5)