Büyük sorgu 4: havayolu günlük operasyon panosu

Soru

Kanat Havayolları operasyon merkezi her sabah bir pano açıyor: Dün kaç uçuş planlandı, kaçı iptal edildi, zamanında kalkış oranı ne, son yedi günün eğilimi ne, rötarların başlıca nedeni neydi? Bu soruların her biri farklı bir grain düzeyinde hesaplanır ve sonunda gün düzeyinde birleşir.

Sorgu

WITH ucus AS (           -- 1) Uçuş düzeyi: gün, gecikme, iptal bayrağı
  SELECT f.flight_id, date(f.sched_dep) AS gun, f.status,
         ROUND((julianday(f.actual_dep) - julianday(f.sched_dep)) * 1440) AS gecikme
  FROM hv_flights f
  WHERE f.sched_dep >= '2024-12-01' AND f.sched_dep < '2025-01-01'
),
gunluk AS (              -- 2) Gün düzeyi: planlanan, iptal, zamanında kalkan
  SELECT gun,
         COUNT(*) AS planlanan,
         SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS iptal,
         SUM(CASE WHEN gecikme <= 15 THEN 1 ELSE 0 END) AS zamaninda,
         COUNT(gecikme) AS kalkan
  FROM ucus
  GROUP BY gun
),
neden AS (               -- 3) Her günün rötar nedenleri ve dakikaları
  SELECT u.gun, d.reason, SUM(d.minutes) AS dakika
  FROM hv_delays d
  JOIN ucus u ON u.flight_id = d.flight_id
  GROUP BY u.gun, d.reason
),
baslica AS (             -- 4) Günün en çok dakika getiren nedeni (eşitlikte alfabetik)
  SELECT gun, reason, dakika,
         ROW_NUMBER() OVER (PARTITION BY gun ORDER BY dakika DESC, reason) AS sira
  FROM neden
)
SELECT g.gun,                 -- 5) Pano: günlük değerler + 7 günlük hareketli zamanında kalkış
       g.planlanan, g.iptal,
       ROUND(100.0 * g.zamaninda / g.kalkan, 1) AS otp,
       ROUND(100.0 * SUM(g.zamaninda) OVER w / SUM(g.kalkan) OVER w, 1) AS otp_7gun,
       b.reason AS baslica_neden,
       b.dakika AS neden_dk
FROM gunluk g
LEFT JOIN baslica b ON b.gun = g.gun AND b.sira = 1
WINDOW w AS (ORDER BY g.gun ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
ORDER BY g.gun;

Adım adım

İki önemli ayrıntı

• Hareketli oran: 7 günün oranlarının ortalaması değil, 7 günün toplam zamanında kalkışının toplam kalkışa oranıdır: SUM(zamaninda) OVER w / SUM(kalkan) OVER w.
• Adlandırılmış pencere: WINDOW w AS (...) aynı çerçeveyi iki kez yazmaktan kurtarır. SQL Server 2022 öncesi ve bazı SQL türleri desteklemez; o durumda çerçeve her OVER içinde tekrarlanır.