SQL’de Özet Tarih Tablosu / Date dimension table

Bu blogda doğrudan yeni teknolojileri anlatmak yerine eski ve yeni teknolojileri gözetmeksizin günlük hayatta, projelerde ihtiyaç duyulan yöntemleri, ipuçlarını, konuları, örnekleri, yazılım geliştirme sürecinde dikkat edilmesi gereken konuları vermeye çalışıyoruz. Yazılım mühendisliğiyle ve C# veya VB.NET programlama dilleriyle ilgili detaylı konuları yakın zamanda çıkacak olan kitaplarımda vermeye çalıştım. Bu küçük yazıda da özellikle veri ambarı raporlama sistemlerinde işlerimizi kolaylaştıracak Tarih tablosunun içeriğini vereceğiz. “Date dimension table” olarak isimlendirilen bu tablo tüm data warehouse sistemlerinde ortak olup sistemin kullanım amacına uygun olarak farklı detaylar içerir. Belli tarih aralığının detaylarını içeren bu tablo çalışma anında tarihsel hesaplamaların neden olacağı performans ve zaman kaybını engeller. Bu tablo aynı zamanda OLTP uygulamalarında lookup olarak ta kullanılabilir.

Örnek verdiğimiz aşağıda tabloda bir tarihin, tarih formatı, hafta ve ay bilgisi, haftanın, ayın ve yılın kaçıncı günü olduğu, haftasonuna denk gelip gelmediği gibi bilgiler verilmektedir.

CREATE TABLE L_Tarih(
	Bugun int NOT NULL, --YYYYMMDD formatında tarih. Tablonun primary key'i.
	FormatliBugun char(10) NULL,--DD.MM.YYYY formatında tarih
	GunAd char(10) NULL,
	AyAd char(10) NULL,
	TarihAd varchar(50) NULL,--'10 Aralık 2007 Pazartesi' gibi
	BuYil char(4) NULL,--Sene bilgisi
	BuAy char(6) NULL,--Yıl ve ay bilgisi. YYYYMM
	BuHafta char(6) NULL,--Yıl ve hafta bilgisi. YYYYWW
	Dun char(10) NULL,
	GecenHaftaBugun char(10) NULL,
	GecenAyBugun char(10) NULL,
	GecenYil char(4) NULL,
	GecenYilBugun char(10) NULL,
	GecenYilBuHafta char(6) NULL,
	GecenYilBuAy char(6) NULL,
	HaftaIlkGun char(10) NULL,--Bugünün bulunduğu haftanın başı
	HaftaSonGun char(10) NULL,--Bugünün bulunduğu haftanın sonu
	AyIlkGun char(10) NULL,
	AySonGun char(10) NULL,
	HaftaGunNo int NULL,--Haftanın kaçıncı günü
	AyGunNo int NULL,--Ayın kaçıncı günü
	YilGunNo int,--Yılın kaçıncı günü
	AyGunSayi int NULL,
	YilIlkGun char(10) NULL,
	YilSonGun char(10) NULL,
	HaftaSonu bit NULL--Haftasonu olup olmadığını belirtir
)

Şeması bu şekilde olan tabloya aşağıdaki script’i çalıştırarak iki tarih aralığındaki tarih detaylarını yazdıralım.

DECLARE @IlkTarih DATETIME
DECLARE @SonTarih DATETIME

SET @IlkTarih = '01-01-2007'
SET @SonTarih = '31-12-2008'

DECLARE @Bugun INT, @FormatliBugun CHAR(10), @GunAd CHAR(10), @AyAd CHAR(10),
	@TarihAd VARCHAR(50), @BuYil CHAR(4), @BuAy CHAR(6), @BuHafta CHAR(6),
	@Dun CHAR(10), @GecenHaftaBugun CHAR(10), @GecenAyBugun CHAR(10),
	@GecenYil CHAR(4), @GecenYilBugun CHAR(10),@GecenYilBuHafta CHAR(6),
	@GecenYilBuAy CHAR(6),@HaftaGunNo INT, @AyGunNo INT,@YilGunNo INT,
	@HaftaIlkGun CHAR(10), @HaftaSonGun CHAR(10),@AyGunSayi INT,
	@AyIlkGun CHAR(10), @AySonGun CHAR(10), @YilIlkGun CHAR(10),
	@YilSonGun CHAR(10), @HaftaSonu BIT

WHILE @IlkTarih 
Bu scriptleri dili Türkçe olan bir kullanıcıyla çalıştırdığımızda tarih tablosu Türkçe formatına göre oluşturulur. L_Tarih tablosuna 2007 ve 2008 yıllarına ait tarih bilgileri eklenmiş olur.
<img src="http://www.ahmetkaymaz.com/wp-content/uploads/Tarih_Lookup_Tablosu.jpg"/>
Bu tablo üzerinde bazı sorgular çalıştıralım. 2008 yılında hangi günden kaç adet bulunduğunu sorgulayalım.
SELECT Max(HaftaGunNo) [Gün Numarası],GunAd [Gün Adı], COUNT(*) Adet
FROM L_Tarih WHERE BuYil = 2008
GROUP BY GunAd ORDER BY 1
Gün Numarası Gün Adı Adet
1 Pazartesi 52
2 Salı 53
3 Çarşamba 53
4 Perşembe 52
5 Cuma 52
6 Cumartesi 52
7 Pazar 52

Görüldüğü gibi 2008 yılında 53 adet Salı ve Çarşamba günü bulunmaktadır. Aynı şekilde 2008 yılında ayların kaçar güne sahip olduğunu sorgulayalım.

SELECT BuAy [Ay], Max(AyAd) [Ay Adı], COUNT(Bugun) [Gün Sayısı]
FROM L_Tarih WHERE BuYil = 2008
GROUP BY BuAy ORDER BY 1
Ay Ay Adı Gün Sayısı
200801 Ocak 31
200802 Şubat 29
200803 Mart 31
200804 Nisan 30
200805 Mayıs 31
200806 Haziran 30
200807 Temmuz 31
200808 Ağustos 31
200809 Eylül 30
200810 Ekim 31
200811 Kasım 30
200812 Aralık 31

Bu tür tablolar OLAP uygulamalarında özetleme seviyeleri olarak kullanılır. Örneğin özellikle reel büyüme değerleri için haftalık, aylık, yıllık karşılaştırmalar herhangi bir tarih hesaplaması yapılmadan kolayca buradaki veriler kullanılabilir. Örneğin satış, sipatiş tablolarıyla JOIN edilerek farklı zaman dilimine göre satış veya sipariş analizleri yapılabilir.

SQL’de Özet Tarih Tablosu / Date dimension table” üzerine bir düşünce

  1. UNSAL OSMA

    SP ile cursor kullanıp tarihler arası toplu kayıt yapmaya çalışıyorum.kaydetme kısmı çalışıyor ama update kısmında sorun yaşıyorum update etmek istediğimde tarih aralığındaki tüm deperleri bitiş tarihi ile aynı yapıyor.kodlarım aşağıda acaba bakabilirmisiniz nerde hata yapıyorum.bir haftadır çatlattı beni çözemedim.ALTER PROCEDURE diger @bastarih datetime,
    @bittarih datetime,
    @otel nvarchar(50),
    @kort nvarchar(50),
    @zaman1 text,
    @kayitno intASDECLARE @tarih1 datetime
    DECLARE @tarih2 datetime
    DECLARE @otel1 nvarchar(50)
    DECLARE @kort1 nvarchar(50)
    DECLARE @zaman19 nvarchar(50)
    DECLARE @kayitno1 int
    DECLARE @MyCursor CURSOR SET @MyCursor = CURSOR FOR
    SELECT tarih,otel,kort,zaman1,kayitno FROM tennis
    OPEN @MyCursor
    FETCH NEXT FROM @MyCursor INTO @tarih1,@otel1,@kort1,@zaman19,@kayitno1SELECT tarih,otel,kort,zaman1,kayitno FROM tennisWHILE (@@FETCH_STATUS = 0)
    BEGIN IF EXISTS (SELECT kayitno FROM tennis WHERE kayitno=@kayitno)
    begin
    while @basTarih < =>
    begin
    UPDATE tennis SET tarih=@bastarih,otel=@otel,kort=@kort,zaman1=@zaman1,kayitno=@kayitno
    WHERE kayitno=@kayitno
    set @basTarih = dateadd(day,1,@basTarih)
    end
    end
    else
    beginwhile @basTarih < =>
    begin INSERT INTO tennis(tarih,otel,kort,zaman1,kayitno)
    VALUES (@bastarih,@otel,@kort,@zaman1,@kayitno)
    set @basTarih = dateadd(day,1,@basTarih)
    end
    END FETCH NEXT FROM @MyCursor INTO @tarih1,@otel1,@kort1,@zaman19,@kayitno1
    ENDSELECT tarih,otel,kort,zaman1,kayitno FROM tennis
    CLOSE @MyCursor
    DEALLOCATE @MyCursor

    Cevapla
  2. Ahmet Kaymaz Yazar

    Yazdığın sorgu doğru çalışıyor fakat bir mantık hatası var.UPDATE tennis SET tarih=@bastarih,otel=@otel,kort=@kort,zaman1=@zaman1,kayitno=@kayitno
    WHERE kayitno=@kayitnobu ifade tabloyu o tarih aralığı kadar güncelliyor yani her defasında tarih alanını yeni @bastarih değeriyle güncelliyor ve döngüden çıkarken doğal olarak tablo ensonki tarih ile güncellenmiş olacak. tennis tablosunda her tarih için ayrı satırları güncellemesini bekliyorsun sanırım ama WHERE kayitno=@kayitno olduğu için her defasında bu koşula uyan tüm kayıtlar güncellenmiş olacak ve yeni tarih eski tarihi ezecektir. O tabloyu başka bir anahtar kolonla güncellemek lazım veya tablodaki tüm kayıtları silip yeniden insert yapmak lazım.

    Cevapla
  3. UNSAL OSMA

    anahtar kolon olarak rez_id kolonunu kullanıyorum dediğinizi yapınca iki tarih arası update etti yani tarih kısmında sorun çıkarmadı ama benim diğer kolonlarım olan zaman1,zaman2,zaman3,zaman4 bunlarda güncellerken ben zaman4 veri giriyorum ama bunu yaparken zaman1,zaman2 gibi diğer sütünlardaki verileri siliyor.
    mesala ;
    zaman1-zaman2-zaman3-zaman4
    AAAAA BBBB CCCC DDDD
    AAAAA VVVV DDDD ZZZZ Burda zaman4 deki (ZZZZ) değiştirip (TTTT) Yapınca aşağıdaki gibi diğer sutunları siliyorzaman1-zaman2-zaman3-zaman4
    AAAAA BBBB CCCC DDDD
    ? ? ? TTTT güncelleme sonrası soru işareti olan yerler böyle oluyor.neden yapabilir bunu.SP ve Cursor kullanımında acemi olduğum için çok zorlanıoyrum.

    Cevapla
  4. Ahmet Kaymaz Yazar

    Unsal tam olarak ne yapmak istediğin bilmediğim ve veriler eksik olduğu için buradan yardımcı olmakta zorlanıyorum. Stored Procedure veya Cursor yapıları SQL’de çok kolay yazılır ki bunları zaten biliyorsun. Bence sorun yapmak istediklerinde mantıksal hatalar var. Onları gözden geçirmelisin. Adım adım gitmende fayda vardır.

    Cevapla

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Time limit is exhausted. Please reload CAPTCHA.