Cevap

Zaman serisi verilerinin işlenmesi sorunu, analitik ve klasik SQL programlama kesişiminde ortaya çıkmaktadır. SQL-92'de özel pencere fonksiyonları bulunmadığından, dinamik metriklerin (hareketli toplam, ortalama vb.) ve zaman koşullarının hesaplanması için alt sorgularla uğraşmak gerekmektedir.

Sorun — kaydırmalı pencere üzerinden toplama, zamana göre önceki/sonraki değerleri bulma, rastgele takvim aralığı (örneğin, haftalık/aylık ölçülerin hesaplanması) için etkili gruplama yapan standart araçların olmamasıdır.

Çözüm:

Sadece standart araçlar kullanarak, her satır için korelasyona dayalı alt sorgular ya da hesaplanmış kritere (örneğin, aya, haftaya) göre gruplama yapılmaktadır:

Kod örneği:

-- Pencere fonksiyonu olmadan haftalara göre gruplama örneği
SELECT 
   YEAR(event_date) AS year,
   WEEK(event_date) AS week,
   SUM(value) AS total
FROM timeseries
GROUP BY YEAR(event_date), WEEK(event_date)
ORDER BY year, week;

-- Önceki kaydı bulmak için korelasyonlu alt sorgu
SELECT t1.id, t1.event_date, t1.value,
       (
         SELECT t2.value 
           FROM timeseries t2 
          WHERE t2.event_date < t1.event_date
          ORDER BY t2.event_date DESC
          LIMIT 1
       ) as prev_value
FROM timeseries t1;

Ana özellikler:

Zaman fonksiyonları (YEAR, MONTH, WEEK vb.) üzerinden açık bir şekilde gruplama ve toplama yapılması gerekir.
Kaydırmalı hesaplamalar elde etmek için korelasyonlu alt sorgular veya geçici tablolar kullanmak zorundasınız.
Büyük veri setlerinde performans düşer — her satıra yapılan alt sorgu hızla işlem süresini uzatır.

Kandırmaca soruları.

WEEK(event_date) her tarih için takvim haftasını her zaman net bir şekilde tanımlar mı?

Hayır — farklı DBMS'ler (ve hatta aynı DBMS'nin parametreleri) yılın ilk haftasını farklı şekillerde tanımlamaktadır (örneğin, ISO 8601 vs. Amerika sistemi), bu da toplama sırasında farklı sonuçlara neden olabilir. Fonksiyonun çalışma modunu açık bir şekilde belirtmek veya YEARWEEK kullanmak gerekir.

SELECT YEARWEEK(event_date, 1)  -- 1: ISO haftası pazartesi günü başlar
FROM timeseries;

Korelasyonlu alt sorgu, önceki değeri ararken otomatik olarak çiftleri siler mi?

Hayır, korelasyonlu alt sorgu varsayılan olarak çiftleri filtrelemez. Eğer tabloda bir tarihe birden fazla olay varsa — alt sorgu sıralamaya göre ilkini döndürecektir, ancak diğerlerini göz ardı edecektir.

GROUP BY ile tarihler üzerinden zamanı dikkate almadan toplayabilir miyiz?

Evet, ancak zamanı açık bir şekilde hariç tutmak gerekir, örneğin DATE(event_date) veya TRUNC(event_date) kullanarak çeşitli DBMS'lerde:

SELECT DATE(event_datetime), COUNT(*)
FROM events
GROUP BY DATE(event_datetime)

Tipik hatalar ve anti-patletler

Raporlama dönemleri için takvim standartları ve yerel ayarlar göz önüne alınmadan YEAR/MONTH/WEEK kullanımı
Büyük veri setlerinde yürütme süresinde patlayıcı bir artışa yol açan çok karmaşık korelasyonlu alt sorgular oluşturma
Zaman fonksiyonlarıyla çalışırken saat dilimini göz önünde bulundurmama

Gerçek hayattan bir örnek

Olumsuz vaka

Ekip, parametreleri ISO-8601'e ayarlamadan WEEK(date) fonksiyonlarına göre haftalık analitik raporlar yapıyordu. Sonuç olarak, ocak ayının ilk haftasına ait raporlar "kayboluyordu" — bazı olaylar Amerikan mantığına göre geçen yılın aralık ayına ait oluyordu. Analitik tutarlı değildi.

Artılar:

Dönemlerin birleştirilmesini hızlı bir şekilde gerçekleştirdiler

Eksiler:

Veriler yanlış, raporlar iş mantığıyla çelişiyor

Olumlu vaka

Uzman, YEARWEEK(date, 1) ve bir takvim tablosu uygulayarak raporlar arasında ülkeler ve iş birimleri arasında uyumu önemli ölçüde artırdı.