Zaman desenleri ve hava durumu: ne zaman, neden binilir?

Saat ve gün desenleri, hafta içi ile hafta sonu farkı, saatlik hava durumuyla birleştirip yağmur ve sıcaklık etkisini ölçmek; saat başına normalize etmek.

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

İki farklı kullanıcı, iki farklı desen

Hafta içi yolculuklar sabah 8 ve akşam 18 civarında iki zirve yapar: işe gidiş ve dönüş. Hafta sonu ise öğleden sonraya yayılan tek bir tepe vardır. Bu deseni görmek için saat × gün türü çapraz tablosu yeterlidir.

Normalizasyon: sayı değil oran

“Yağmurlu saatlerde kaç yolculuk oldu?” sorusunun cevabı yanıltıcıdır, çünkü yağmurlu saat sayısı kuru saatlerden çok azdır. Doğru karşılaştırma saat başına ortalama yolculuktur: önce saat başına yolculuk sayısını bul, sonra hava koşuluna göre ortalamasını al.

WITH s AS (     -- saat başına yolculuk (yalnızca yolculuk olan saatler)
  SELECT strftime('%Y-%m-%d %H:00:00', started_at) AS saat, COUNT(*) AS n
  FROM bk_trips GROUP BY strftime('%Y-%m-%d %H:00:00', started_at)
),
h AS (          -- payda: her koşulda kaç saat var (hava tablosundan)
  SELECT CASE WHEN w.rain_mm > 0 THEN 'yağmurlu' ELSE 'kuru' END AS hava, COUNT(*) AS saat_sayisi
  FROM bk_weather w
  WHERE CAST(strftime('%H', w.obs_hour) AS INTEGER) BETWEEN 10 AND 19
  GROUP BY CASE WHEN w.rain_mm > 0 THEN 'yağmurlu' ELSE 'kuru' END
),
y AS (          -- pay: bu saatlerde başlayan yolculuklar
  SELECT CASE WHEN w.rain_mm > 0 THEN 'yağmurlu' ELSE 'kuru' END AS hava, SUM(s.n) AS yolculuk
  FROM s JOIN bk_weather w ON w.obs_hour = s.saat
  WHERE CAST(strftime('%H', w.obs_hour) AS INTEGER) BETWEEN 10 AND 19
  GROUP BY CASE WHEN w.rain_mm > 0 THEN 'yağmurlu' ELSE 'kuru' END
)
SELECT h.hava, h.saat_sayisi, ROUND(1.0 * COALESCE(y.yolculuk, 0) / h.saat_sayisi, 1) AS saat_basina
FROM h LEFT JOIN y ON y.hava = h.hava;

Pay ve paydayı ayrı hesaplamanın bir nedeni daha var: bazı yağmurlu saatlerde hiç yolculuk yoktur. Saatleri yolculuk tablosundan türetirsen bu sıfırlı saatler kaybolur ve yağmurlu saatin ortalaması olduğundan yüksek çıkar. Payda her zaman olası tüm saatleri içeren hava tablosundan gelmelidir. Hava tablosundan başlayıp her saat için saatlik özeti LEFT JOIN ile aramak da doğru sonucu verir, ancak büyük tabloda çok daha yavaştır.

Yalnızca 10:00–19:00 arasına bakmak adil bir karşılaştırma sağlar, çünkü yağmurlar bu saatlerde yağmış; gece saatleri zaten sakindir. Bu tür karıştırıcı değişkenleri kontrol etmek analizin kalitesini belirler.