PostgreSQL: Zaman Aralıklarına Göre Sonuçları Gruplandırma

# PostgreSQL’de Zaman Aralıklarına Göre Gruplandırma

PostgreSQL veritabanınızda zaman serileri verisiyle çalışıyorsanız, verinizi anlamlı bir şekilde analiz etmek için zaman aralıklarına göre gruplandırma yapmanız gerekecektir. Örneğin, web sitenizin günlük ziyaretçilerini saatlik, günlük veya haftalık olarak analiz etmek, bir e-ticaret platformundaki satışları aylık olarak özetlemek veya sensörlerden gelen verileri dakikalık aralıklarla incelemek gibi birçok senaryoda bu teknik hayati önem taşır. Bu makalede, PostgreSQL’de zaman aralıklarına göre gruplandırmayı adım adım, yeni başlayanlardan uzmanlara kadar her seviyeye uygun bir şekilde ele alacağız.

Öğrenme Yol Haritası:

* Yeni Başlayan: date_trunc() fonksiyonu ile basit gruplandırma, basit kod örnekleri.
* Orta Seviye: Gerçek dünya senaryoları, generate_series() fonksiyonu ile eksik zaman aralıklarının doldurulması, performans optimizasyon ipuçları.
* İleri Seviye: Karmaşık zaman aralıkları, performans analizi, edge case’ler ve çözümleri, paralel sorgulama teknikleri.

PostgreSQL Zaman Aralıklarına Göre Gruplandırma: Temel Kavramlar

PostgreSQL, zaman verilerini işlemek için güçlü fonksiyonlar sunar. Bu fonksiyonların en önemlisi date_trunc()‘tır. date_trunc() fonksiyonu, bir tarih/zaman değerini belirli bir zaman birimine (örneğin, saniye, dakika, saat, gün, ay, yıl) yuvarlar. Bu yuvarlama işlemi, zaman aralıklarına göre gruplandırmanın temelini oluşturur. Diğer önemli fonksiyonlar arasında extract() (tarih/zaman değerinden spesifik bir bileşeni çıkarmak için) ve interval (süreleri temsil etmek için) yer alır.

PostgreSQL Zaman Aralıklarına Göre Gruplandırma: Adım Adım Uygulama

Örneğin, bir “log” tablomuz olsun ve bu tabloda her girişin bir timestamp sütunu bulunsun. Bu log kayıtlarını saatlik olarak gruplayıp her saat dilimindeki kayıt sayısını bulmak istiyoruz.

Yeni Başlayan Seviyesi:

SELECT date_trunc('hour', timestamp) AS saat, COUNT(*) AS kayıt_sayısı
FROM log
GROUP BY saat
ORDER BY saat;

Bu sorgu, timestamp sütununu saatlere yuvarlar (date_trunc('hour', timestamp)), sonra bu yuvarlanmış saatlere göre gruplar (GROUP BY saat) ve her saat dilimindeki kayıt sayısını sayar (COUNT(*)). Son olarak, sonuçları saat sırasına göre sıralar (ORDER BY saat).

Orta Seviye:

Şimdi, bir e-ticaret sitesinin günlük satışlarını analiz edelim. Her satışın tarih ve tutar sütunlarını içeren bir “satislar” tablomuz olsun. Günlük satışları hesaplamak ve eksik günleri sıfır satış ile göstermek istiyoruz.

WITH günlük_satislar AS (
    SELECT date(tarih) AS satış_günü, SUM(tutar) AS toplam_tutar
    FROM satislar
    GROUP BY satış_günü
),
tüm_günler AS (
    SELECT generate_series(min(satış_günü), max(satış_günü), '1 day'::interval) AS gün
    FROM günlük_satislar
)
SELECT t.gün, COALESCE(ds.toplam_tutar, 0) AS günlük_satis
FROM tüm_günler t
LEFT JOIN günlük_satislar ds ON t.gün = ds.satış_günü
ORDER BY t.gün;

Bu sorgu, öncelikle generate_series() fonksiyonunu kullanarak satışların yapıldığı minimum ve maksimum tarihler arasında tüm günleri oluşturur. Daha sonra, LEFT JOIN ile günlük satışlar tablosunu birleştirir. Eğer bir günde satış yoksa, COALESCE fonksiyonu sayesinde 0 değeri gösterilir. Bu sayede, tüm günlerin satış verilerini içeren eksiksiz bir rapor elde edilir.

PostgreSQL Zaman Aralıklarına Göre Gruplandırma: İleri Düzey Teknikler ve Optimizasyon

İleri Seviye:

Karmaşık zaman aralıkları için, date_trunc()‘ı birden çok kez kullanabilir veya daha karmaşık koşulları CASE ifadeleriyle birleştirebiliriz. Örneğin, haftanın günlerine göre gruplandırıp, her günün saatlik satışlarını görmek isteyebiliriz.

SELECT
    TO_CHAR(tarih, 'Day') AS gün_adı,
    date_trunc('hour', tarih) AS saat,
    SUM(tutar) AS toplam_tutar
FROM
    satislar
GROUP BY
    gün_adı, saat
ORDER BY
    gün_adı, saat;

Performans Optimizasyonu:

* İndeksler: timestamp veya tarih sütunlarına uygun indeksler oluşturmak sorguların hızını önemli ölçüde artırır.
* Parçalama: Büyük tablolar için, zaman aralıklarına göre parçalama (partitioning) performansı iyileştirir.
* Materyalize Görünümler: Sık kullanılan sorgular için materyalize görünümler (materialized views) oluşturarak performans kazanılabilir. [Fatih Soysal’ın PostgreSQL performans optimizasyonu yazıları](https://fatihsoysal.com) bu konuda daha detaylı bilgi sağlayacaktır.
* Sorgu Planlayıcısını Anlamak: EXPLAIN komutu ile sorgu planını inceleyerek performans darboğazlarını tespit edebilirsiniz.

Gerçek Dünya Senaryoları ve Vaka Çalışmaları

* Web Analitiği: Web sunucusu loglarını saatlik, günlük veya haftalık olarak analiz ederek site trafiğini izleyebilirsiniz.
* IoT Veri Analizi: Sensörlerden gelen verileri zaman aralıklarına göre gruplayarak trendleri ve anormallikleri tespit edebilirsiniz.
* Finansal Veri Analizi: Hisse senedi fiyatlarını günlük, haftalık veya aylık olarak analiz ederek yatırım kararları alabilirsiniz.
* Telekomünikasyon: Çağrı kayıtlarını zaman aralıklarına göre gruplayarak servis kalitesini değerlendirebilirsiniz.

Sonuç

PostgreSQL, zaman serileri verilerini analiz etmek için güçlü fonksiyonlar sunar. date_trunc(), generate_series(), extract() ve interval gibi fonksiyonları kullanarak verinizi istediğiniz zaman aralıklarına göre gruplayabilir ve anlamlı bilgiler elde edebilirsiniz. Performans optimizasyonu için indeksler, parçalama ve materyalize görünümlerden faydalanmayı unutmayın. [Fatih Soysal’ın blogunda](https://fatihsoysal.com) PostgreSQL ile ilgili daha fazla ipucu ve teknik bilgi bulabilirsiniz.

Sıkça Sorulan Sorular:

1. Eksik zaman aralıklarını nasıl doldurabilirim? generate_series() fonksiyonu ile eksik aralıkları oluşturup LEFT JOIN ile ana veriyle birleştirebilirsiniz.
2. Çok büyük tablolar için performansı nasıl iyileştirebilirim? İndeksler, parçalama ve materyalize görünümler kullanarak performansı artırabilirsiniz.
3. Farklı zaman birimlerini aynı sorgu içinde nasıl kullanabilirim? CASE ifadeleri veya birden çok date_trunc() kullanarak farklı zaman birimlerini aynı sorgu içinde kullanabilirsiniz.
4. Zaman dilimlerini nasıl yönetebilirim? timezone ayarlarını doğru kullanarak zaman dilimlerini doğru şekilde yönetebilirsiniz.
5. Karmaşık zaman aralıkları için nasıl sorgu yazabilirim? CASE ifadeleri, alt sorgular ve interval kullanarak karmaşık zaman aralıklarını yönetebilirsiniz.

Yazar: Fatih Soysal