Toplantı odasında iki rapor yan yana duruyordu. Müşterinin yakıt departmanının Excel'le elde hesapladığı aylık rölanti süresi: 218 saat. Bizim platformun aynı filo için raporladığı: 342 saat. Yüzde 57 fark. Müşteri haklı olarak sordu: "Hanginize inanayım?" O gün masada verdiğim cevabı hâlâ savunuyorum: "İkimiz de yanlışız ama farklı sebeplerden." Onların Excel'i kısa rölantileri gözden kaçırıyordu; bizim SQL ise aynı rölantiyi bazen üç kere sayıyordu. Bu yazı, o utançtan doğan düzeltmenin — ve altında yatan güzel SQL probleminin — hikâyesi.
Rölanti Neden Zor: Sinyalin Kekemeliği
Rölanti tanım olarak basit: motor çalışıyor, araç durmuyor... pardon, duruyor. Kontak açık, hız sıfır. Cihaz zaten her 10-30 saniyede bir kayıt atıyor; hız ve kontak bilgisi içinde. "Hızı sıfır ve kontağı açık kayıtların süresini topla" — ilk sürümün mantığı buydu ve iki yerden su alıyordu.
Birincisi, sinyal kekeme. Araç ışıkta dururken GPS hızı 0, 2, 0, 3, 0 km/s diye titriyor (önceki Kalman yazımı okuyanlar tanıdık gelecek). Titremenin her sıfırdan çıkışı, naif algoritmada rölantiyi "bitiriyor", geri dönüşü yeni bir rölanti "başlatıyor". Beş dakikalık tek bir bekleyiş, raporda dört ayrı iki dakikalık rölanti olarak görünüyor; müşterinin "3 dakikadan uzun rölantileri listele" filtresi hepsini kaçırıyor. İkincisi, veri delikli. GPRS kopmalarında 40-50 saniyelik kayıt boşlukları oluşuyor; boşluğun iki yakasındaki rölantiler ayrı mı, aynı mı? Cevabınız yoksa raporunuz keyfe keder demektir.
Problemi soyutlayınca literatürdeki adıyla karşılaşıyorsunuz: gaps and islands. Elinizde zaman damgalı satırlar var; ardışık ve "aynı durumda" olanları adalara toplamak, adalar arasındaki boşlukları yönetmek istiyorsunuz. Rölanti hesabı, adaların süre toplamı. SQL bu problemi döngüsüz, tek sorguda çözebiliyor — yeter ki pencere fonksiyonlarınız olsun.
Sıra Numarası Farkının Küçük Mucizesi
Klasik çözümün özü zekice bir gözlem. Kayıtlara zamana göre sıra numarası verin (ROW_NUMBER, tüm kayıtlar üzerinden) ve bir de yalnızca rölanti kayıtlarına ayrı bir sıra numarası verin. Ardışık rölanti kayıtlarında iki numara birlikte artar; farkları sabit kalır. Rölanti zinciri kesilip tekrar başladığında fark değişir. Yani (genel sıra − rölanti sırası) değeri, her adaya özgü bir kimliktir; GROUP BY ile adaları toplayıverirsiniz. İlk kez gördüğümde kâğıtta üç örnek çizerek ikna olabilmiştim — ekibe anlatırken de herkes aynı üç çizimi yaptı. (Anlamadan kopyalanan SQL, üretimde anlamadan patlar; çizdirin.)
Biz üretimde bunun LAG'li varyantını kullandık, çünkü ada tanımımız sadece "durum aynı" değil: araya giren zaman boşluğu da toleransı aşmamalı. Sorgunun omurgası üç katman. İlk katman her kayıt için LAG ile önceki kaydın zamanını ve durumunu getiriyor. İkinci katman "yeni ada başlangıcı" bayrağını hesaplıyor: durum değiştiyse ya da önceki kayıtla aram 90 saniyeden fazlaysa 1, değilse 0. Üçüncü katman bu bayrağın kümülatif toplamını alıyor — SUM(bayrak) OVER (PARTITION BY arac_id ORDER BY zaman) — ve işte size ada numarası. Kümülatif toplam, her bayrakta bir artan bir sayaçtır; bayraksız satırlar önceki adaya yapışır. Dış sorgu ada numarasına göre gruplar: MIN(zaman) başlangıç, MAX(zaman) bitiş, sayısı, süresi.
90 saniyelik tolerans rakamı da masa başında değil, veriyle seçildi: kayıt aralığımız en kötü ihtimalle 30 saniye, GPRS kopmalarının yüzde 95'i 60 saniyenin altında. 90 saniye ikisini de affediyor, gerçek bir "motoru kapatıp beş dakika sonra tekrar çalıştırma" olayını ise birleştirmiyor. Toleransı 300 saniyeye çıkarıp deneyince toplam rölanti yüzde 9 arttı — yani bu tek parametre, müşteriye giden rakamı gözle görülür oynatıyor. Parametreleri raporun dipnotuna yazdırdık; metodoloji şeffaflığı, iki rakam yarışırken tek güvenilirlik kaynağı.
Hız Titremesini Kim Susturacak?
Kekeme sinyal problemine dönersek: onu SQL'de değil, SQL'den önce çözdük. Durum tespiti (rölanti mi, seyir mi, park mı) artık işleme hattında, histerezisle yapılıyor: rölantiye girmek için hızın 20 saniye boyunca 3 km/s altında kalması, çıkmak için 10 saniye boyunca 5 km/s üstünde seyretmesi gerekiyor. İki eşiğin farklı olması bilinçli — tek eşik, eşiğin tam üstünde titreyen sinyali yine kekeme yapar. SQL katmanına temiz bir durum sütunu inince sorgu da sadeleşti. Genel bir ders var burada: her problemi çözebileceğiniz katman ile çözmeniz gereken katman farklıdır; sinyal işleme SQL'in işi değil.
1,9 Milyar Satırda Pencere Açmak
Performans faslı: pencere fonksiyonları MySQL'e 8.0 ile geldi ve bu ihtiyaç, bizim 5.7'den 8.0'a göç gerekçelerimizin başındaydı. (5.7'de aynı işi kullanıcı değişkenleriyle — @prev := ... numarasıyla — yapan bir sürümümüz vardı; çalışıyordu ama optimizer'ın değişken sırasına dair hiçbir garantisi olmadığı için her sürüm yükseltmede kalbimiz ağzımızdaydı.) Aylık rapor, 300 araçlık filoda yaklaşık 25 milyon satırı tarıyor; pencereli sorgu ilk denemede 4 dakika sürdü. İki düzeltmeyle 19 saniyeye indi: partisyon budamasının çalıştığından emin olmak (tarih filtresi çıplak sütunla) ve PARTITION BY arac_id ORDER BY zaman düzenine birebir uyan bir bileşik indeks — pencere fonksiyonunun sıralama maliyeti, indeks zaten sıralıysa buharlaşıyor. Ayrıca raporu gece önceden hesaplayıp özet tabloya yazan bir iş ekledik; sabah raporu artık 25 milyon satıra değil, hazır adalara bakıyor.
Ailenin bir başka üyesi de sık lazım oluyor: örtüşen aralıkları birleştirme. Diyelim elinizde başlangıç-bitiş çiftleri halinde olaylar var (bakım kayıtları, sürücü mesai dilimleri) ve iç içe geçenleri tek aralığa indirmek istiyorsunuz. Burada numara, satırları başlangıca göre sıralayıp o ana kadar görülen en büyük bitişi pencereyle taşımak: MAX(bitis) OVER (ORDER BY baslangic ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING). Yeni satırın başlangıcı bu değerden büyükse yeni bir grup başlıyor demektir; gerisi yine bayrak + kümülatif toplam. İskeletin aynı kalıp yalnızca "ada başlangıcı" tanımının değişmesi, bu desen ailesinin en zarif yanı.
Müşteriyle ikinci toplantı, düzeltmeden üç hafta sonraydı. Yeni rakamımız 246 saatti; Excel yöntemlerindeki eksiği (kısa rölantiler) kendi verileriyle gösterdik, bizim eski çift sayma hatamızı da saklamadan anlattık. Sözleşme yenilendi. Rakamların birbirine değil, tanımlara yakınsaması gerektiğini o gün iki taraf da öğrendi.
Test tarafı için de bir alışkanlık edindik: bu sorgular öyle sinsi ki, kenar vakalarını sabit bir "işkence veri seti" ile tutuyoruz. Gün sınırında başlayıp biten rölanti, tek kayıtlık ada, tam tolerans sınırında boşluk, gece yarısını yatay kesen aralık, hiç rölantisi olmayan araç. Sorguya her dokunuşta bu set beklenen çıktılarla karşılaştırılıyor. Pencere fonksiyonlu sorgularda "küçük" bir değişikliğin neyi bozduğunu gözle görmek neredeyse imkânsız; sabit veri seti, tek güvenilir hakem.
Zaman aralığı birleştirme, rölantiyle sınırlı bir numara değil; aynı sorgu iskeletiyle mesai içi çalışma sürelerini, bölge içinde kalış aralıklarını, hatta sunucu loglarından kesinti pencerelerini çıkarıyoruz. Elinizde "ardışık satırları olaya dönüştürme" cinsinden bir problem varsa yazın, sorgu iskeletini örnek veriyle birlikte gönderirim; kâğıda üç örnek çizmeyi de ihmal etmeyin.