SQL Server 2012 ile gelen özelliklerden bir tanesi de Analitik fonksiyonlardır. Bazı anlarda hayatı oldukça kolaylaştıran bu fonksiyonların, gündelik hayatta en çok kullanılanlarını bu yazıda sizlere anlatmaya çalışacağız.
Fonksiyonları açıklarken aşağıdaki veri setini ele alacağız ve tüm fonksiyonlar için bu veri setini kullanacağız.
Örneğin bir çağrı sisteminden çekilmiş, aşağıdaki gibi bir veri setimiz olsun. Bu veri setinde aşağıdaki iş kuralları işlesin;
- Her Çağrı IncidentId ile takip edilir
- Her çağrının içinde aktiviteler vardır ve ActivityId ile takip edilir
- Her aktivite mutlaka bir çağrıya aittir ama bir çağrının birden çok aktivitesi bulunabilir
- Her aktivitenin tamamlanma tarihi kendisinden sonra gelen aktivitenin başlama tarihidir
LEAD Fonksiyonu
LAG fonksiyonu çekmiş olduğunuz bir veri seti içerisinde bir önceki satırdaki herhangi bir alanın değerine ulaşmamızı sağlar. LEAD fonksiyonu ise bir sonraki satırdaki herhangi bir alanın değerine ulaşmamızı sağlar.
Bir aktivitenin kapanış tarihine aşağıdaki şekilde ulaşabiliriz.
SELECT ActivityId, IncidentId, CreatedOn,
LEAD(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS ActivityClosedOn
FROM @Activities
ORDER BY IncidentId, CreatedOn ASC
LAG Fonksiyonu
LAG fonksiyonu çekmiş olduğunuz bir veri seti içerisinde bir önceki satırdaki herhangi bir alanın değerine ulaşmamızı sağlar.
Bir aktivitenin kendisinden önceki aktivitesinin tarihine aşağıdaki şekilde ulaşabiliriz.
SELECT ActivityId, IncidentId, CreatedOn,
LEAD(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS ActivityClosedOn,
LAG(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS PreviousActivityCreatedOn
FROM @Activities
ORDER BY IncidentId, CreatedOn ASC
FIRST_VALUE Fonksiyonu
FIRST_VALUE fonksiyonu bir veri setinde belirtilen sıralamadaki ilk satırın üzerindeki bir alanın değerine ulaşmamızı sağlar.
Örnek veri setimizde ilk aktivite tarihi çağrının açılış tarihi olarak kabul edilecek olursa buna aşağıdaki şekilde ulaşabiliriz.
SELECT ActivityId, IncidentId, CreatedOn,
LEAD(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS ActivityClosedOn,
LAG(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS PreviousActivityCreatedOn,
FIRST_VALUE(CreatedOn) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS IncidentCreatedOn
FROM @Activities
ORDER BY IncidentId, CreatedOn ASC
LAST_VALUE Fonksiyonu
LAST_VALUE fonksiyonu bir veri setinde belirtilen sıralamadaki son satırın üzerindeki bir alanın değerine ulaşmamızı sağlar.
Örnek veri setimizde çağrı içerisindeki son aktivitenin açılış tarihine ulaşılmak istenilirse aşağıdaki şekilde ulaşabiliriz.
SELECT ActivityId, IncidentId, CreatedOn,
LEAD(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS ActivityClosedOn,
LAG(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS PreviousActivityCreatedOn,
FIRST_VALUE(CreatedOn) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS IncidentCreatedOn,
LAST_VALUE(CreatedOn) OVER (PARTITION BY IncidentId ORDER BY CreatedOn RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastIncidentCreatedOn
FROM @Activities
ORDER BY IncidentId, CreatedOn ASC
Sonuç
Günlük hayatta hepimizden bazı raporlar talep edilmektedir. Raporlarımızı geliştirirken bazen “bu kadar da karmaşık bir talep olmamalı” şeklinde düşündüğümüz fakat iş tarafı için hakikaten değer ifade eden bazı rapor talepleriyle çokça karşılaşmışızdır. Doğru fonksiyonlar veya yaklaşımlarla iş tarafını memnun etmek bazen çok basit olmakla birlikte bazen dakikalarca veya saatlerce çalışan raporlarımız olabilmektedir. Uzun çalışan bu raporların başlıca sebeplerinden biri yukarıda bahsedildiği gibi analitik ihtiyaçlardır. Bu fonksiyonlar olmadan da raporlarımızı geliştirebiliriz fakat rapor performansımız anormal şekilde düşük olabilir.
Not: Yukarıdaki fonksiyonlar SQL Server 2012’den itibaren geçerli olup tüm analitik fonksiyonları kapsamamaktadır. Tüm analitik fonksiyonlar için aşağıdaki linki takip edebilirsiniz.
http://msdn.microsoft.com/en-us/library/hh213234.aspx
Kaynak Kodlar
DECLARE @Activities TABLE (ActivityId INT, IncidentId INT, CreatedOn DATE)
INSERT INTO @Activities
Values
(126, 2005, ‘20140101’),
(115, 2005, ‘20140115’),
(142, 2006, ‘20140301’),
(189, 2006, ‘20140308’),
(275, 2006, ‘20140401’),
(236, 2006, ‘20140510’)
SELECT * FROM @Activities
SELECT ActivityId, IncidentId, CreatedOn,
LEAD(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS ActivityClosedOn,
LAG(CreatedOn, 1, NULL) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS PreviousActivityCreatedOn,
FIRST_VALUE(CreatedOn) OVER (PARTITION BY IncidentId ORDER BY CreatedOn) AS IncidentCreatedOn,
LAST_VALUE(CreatedOn) OVER (PARTITION BY IncidentId ORDER BY CreatedOn RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NextIncidentCreatedOn
FROM @Activities
ORDER BY IncidentId, CreatedOn ASC
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
