+90 850 305 14 95 [email protected]

SQL Server üzerinde iş zekası projelerinde danışmanlık ve eğitim hizmeti veren bir kişi olarak benim ve danışanlarımın uzun süredir beklediği, diğer bazı veritabanı ürünlerinde bulunan analitik fonksiyonlar hayatımıza 2012 versiyoni ile beraber girdi. SQL Server 2012 ile beraber karşımıza çıkan bu 8 yeni analitik fonksiyondan ilk olarak ikisini inceleyeceğiz: LEAD ve LAG. Takip eden yazılarımda diğerlerinede değineceğim.

LEAD()

 

Lead fonksiyonu hedeflenen sonuç kümesi (result set) içerisinde o anki satırdan (current row) ileri yönlü hareket etme imkanı sağlamaktadır. Bu hareket sonuç kümesinin tamamı üzerinde olabileceği gibi belirlenen bir alan üzerinde bölgesel hareket şeklinde de olabilir.

Şimdi temel yazım şablonuna bakalım.

 

LEAD ( scalar_expression [ ,offset ] , [ default ] )

 

    OVER ([ partition_by_clauseorder_by_clause)

 

Lead fonksiyonu temel iki bölgeden oluşmaktadır. İlk bölümde ifade alanı, hareket miktarı ve olası bakılan noktada veri yoksa aktarılması istenen varsayılan değer bulunmaktadır. Bu noktada dikkat edilmesi gereken bir kaç husus var. İfade alanı ( scalar_expression ) bölgesi içerisine yazılan fonksiyonun kesinlikle geriye tek hücre bir veri döndürmesi gerekmektedir. Bu alanla ilgili bir diğer konu ise bu ifade alanında başka bir analitik fonksiyon kullanılmamalıdır. Lead fonksiyonunun ikinci bölümüne baktığımızda ise Row_Number() gibi fonksiyonlardan alışık olduğumuz OVER() yapısının karşımıza çıktığını görmekteyiz. Bu alanda sonuç için sıralama işleminin hangi alana göre yapılacağı ve bir alana göre gruplama yapılıp yapılmayacağına karar verilmektedir. Şimdi çeşitli örneklerle konuyu aydınlatalım.

Örneklerimizde SQL Server 2012 için üretilmiş AdventureWorks database kullanılmıştır.

 

Örnek 1

1 USE AdventureWorks2008R2;
2  GO
3  SELECT BusinessEntityID,YEAR(QuotaDate) AS SalesYear,Month(QuotaDate)/3+1 AS SalesQuarter,           SalesQuota AS CurrentQuota,
4
5 LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
6
7 FROM Sales.SalesPersonQuotaHistory
8
9 WHERE BusinessEntityID = 279 AND YEAR(QuotaDate) = 2006;

BusinessEntityID SalesYear   SalesQuarter CurrentQuota          NextQuota —————-    ———– ———— ——————— ——————— 279              2006        1            780000,00             802000,00 279              2006        2            802000,00             734000,00 279              2006        3            734000,00             647000,00 279              2006        4            647000,00             0,00

(4 row(s) affected)

Yukarıdaki sorguyu çalıştırdığımda sonuçtada görmüş olduğunuz gibi veritabanında yılın çeyrekleri bazında girilmiş satış kotası verisinde her satıra bir sonraki dönem satış kotası verisini getirmiş bulunmaktayız. Burda önemli noktalardan biri bu işlemin sonuç seti üzerinde olması nedeniyle 2006 yılı 4. Çeyreğine ait sonraki dönem kaydı gelmemiştir. Bu kaydın gelmeme sebebi tabloda olmaması değil sonuç kümesinde bulunmamasıdır. Örneğimizde eğer Lead fonksyionu içinde

LEAD(SalesQuota+10, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

 

Gibi bir değişiklik yaparsanız herhangi bir hata vermediğini işlemi başarı ile gerçekleştirdiğini görebilirsiniz. Bu durum niçin scalar_expression dendiğini de anlatmaktadır. Şimdi Lead fonsiyonundaki OVER alanına değinelim.

Örnek 2

01 SELECT BusinessEntityID,YEAR(QuotaDate) AS SalesYear,Month(QuotaDate)/3+1 AS SalesQuarter,
02
03             SalesQuota AS CurrentQuota,
04
05     LEAD(SalesQuota+5, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
06
07 FROM Sales.SalesPersonQuotaHistory
08
09 WHERE BusinessEntityID = 279 AND YEAR(QuotaDate) = 2006
10
11 ORDER BY SalesQuarter DESC

BusinessEntityID SalesYear   SalesQuarter CurrentQuota          NextQuota

—————- ———– ———— ——————— ———————

279              2006        4            647000,00             0,00

279              2006        3            734000,00             647005,00

279              2006        2            802000,00             734005,00

279              2006        1            780000,00             802005,00

(4 row(s) affected)

Sorgumuzu ve sonuçları incelediğimizde OVER içerisinde bulunan Order BY işleminin çümle sonundaki Order By daha önce devreye girdiğini ve bu sıralamaya göre LEAD fonksiyonun işlediğini ardından cümle sonundaki sıralamanın devreye girdiğini görmekteyiz.

Şimdi de Partition By ifadesini inceleyelim.

 

Örnek 3

1 SELECT BusinessEntityID,YEAR(QuotaDate) AS SalesYear,Month(QuotaDate)/3+1 AS SalesQuarter,
2
3             SalesQuota AS CurrentQuota,
4
5     LEAD(SalesQuota+5, 1,0) OVER (PARTITION BY BusinessEntityID ORDER BY YEAR(QuotaDate)) AS NextQuota
6
7 FROM Sales.SalesPersonQuotaHistory
8
9 WHERE YEAR(QuotaDate) = 2006 AND BusinessEntityID in (274,275)

BusinessEntityID SalesYear   SalesQuarter CurrentQuota          NextQuota

—————- ———– ———— ——————— ———————

274              2006        1            91000,00              140005,00

274              2006        2            140000,00             70005,00

274              2006        3            70000,00              154005,00

274              2006        4            154000,00             0,00

275              2006        1            502000,00             550005,00

275              2006        2            550000,00             1429005,00

275              2006        3            1429000,00            1324005,00

275              2006        4            1324000,00            0,00

Sorgumuzu ve sonucumuzu incelediğimizde PARTITION BY BusinessEntityID eklentisinin yapılan işlemi BusinessEntityID alanı bazında bölgeselleştirdiğini görmekteyiz. Şimdi de LAG fonksiyonunu inceleyelim.

LAG()

 

Lag fonksiyonu LEAD fonksiyonunun aksine geri yönde bir hareket imkanı vermektedir. LAG fonksiyonu hedeflenen sonuç kümesi (result set) içerisinde o anki satırdan (current row) geri yönlü hareket etmektedir. Bu hareket sonuç kümesinin tamamı üzerinde olabileceği gibi belirlenen bir alan üzerinde bölgesel hareket şeklindede olabilir.

Şimdi temel yazım şablonuna bakalım.

 

LAG ( scalar_expression [ ,offset ] , [ default ] )

 

    OVER ([ partition_by_clauseorder_by_clause)

 

LEAD fonksiyonundaki kurallar burdada geçerlidir. Lead fonksiyonundaki örneklerde LEAD yerine LAG ve NextQuota yerine PreviousQuota yazmak yeterlidir.

Örnek 4

1 SELECT BusinessEntityID,YEAR(QuotaDate) AS SalesYear,Month(QuotaDate)/3+1 AS SalesQuarter,
2
3             SalesQuota AS CurrentQuota,
4
5     LAG(SalesQuota+5, 1,0) OVER (PARTITION BY BusinessEntityID ORDER BY YEAR(QuotaDate)) AS PreviousQuota
6
7 FROM Sales.SalesPersonQuotaHistory
8
9 WHERE YEAR(QuotaDate) = 2006 AND BusinessEntityID in (274,275)

  

BusinessEntityID SalesYear   SalesQuarter CurrentQuota          PreviousQuota

—————- ———– ———— ——————— ———————

274              2006        1            91000,00              0,00

274              2006        2            140000,00             91005,00

274              2006        3            70000,00              140005,00

274              2006        4            154000,00             70005,00

275              2006        1            502000,00             0,00

275              2006        2            550000,00             502005,00

275              2006        3            1429000,00            550005,00

275              2006        4            1324000,00            1429005,00

Şimdilik bu kadar. Bir sonraki yazımda FirstValue ve LastValue fonksiyonları ile devam edeceğiz.