Window Fonksiyon Türleri – Bölüm 43

SQL Server’da window fonksiyonlarıyla bir satır kümesi üzerinde işlem yapılabilir. Window aggregation fonksiyonları kavramsal olarak aggregation fonksiyonlarına benzer. Window aggregation fonksiyonlarıyla sıralama, distribution  ve offset fonksiyonlarını da ayrıca kullanabilirsiniz.

Window Fonksiyonları Oluşturmak

İlk bölümlerde, GROUP BY tarafından tanımlanan satırlar üzerinde işlem yapan SUM, MIN ve MAX gibi aggregate fonksiyonlarını öğrenmiştik. Window fonksiyonlarında ise, OVER ifadesiyle tanımlanan window ile satırlar üzerinde çalışırken ekstradan bu işlevleri de kullanabilirsiniz.

SQL Server window fonksiyonları aşağıdaki kategorilere ayrılabilir:

• Bir window’da çalışan ve tek satır döndüren SUM gibi toplama işlevleri.
• Satır sırasını döndüren RANK gibi sıralama işlevleri.
• Window üzerinde dağılımı hesaplayan CUME_DIST gibi distribution işlevleri.
• Geçerli satırın konumuna göre diğer satırlardaki değerleri döndüren LEAD gibi offset işlevleri.

Bu işlevler window ile kullanıldıklarında, OVER ifadesi tarafından döndürülen sonuç kümesine veya yine OVER içinde tanımlanan partition ve frame gibi öğelere bağlı olarak çalışırlar.

Aşağıdaki örnekte, her satırın rank’ını birim fiyatına göre azalan yönde sıralamak için RANK işlevi kullanılmıştır. Window partition yapılmamıştır:

SELECT productid, productname, unitprice,
 RANK() OVER(ORDER BY unitprice DESC) AS pricerank
FROM Production.Products
ORDER BY pricerank; 

Dönen sonuç:

productid productname unitprice pricerank
----------- ------------- --------------------- ---------
38 Product QDOMO 263.50 1
29 Product VJXYN 123.79 2
9 Product AOZBW 97.00 3
20 Product QHFFP 81.00 4
18 Product CKEDC 62.50 5
59 Product UKXRI 55.00 6
51 Product APITJ 53.00 7
62 Product WUXYK 49.30 8
43 Product ZZZHR 46.00 9
28 Product OFBNT 45.60 10
27 Product SMIOH 43.90 11
63 Product ICKNK 43.90 11
8 Product WVJFP 40.00 13 

Karşılaştırma amaçlı aşağıdaki örnekte categoryid sütununa bir partition eklenmiştir (ve sondaki ORDER BY ifadesine de categoryid eklenmiştir). Ranking işlemi her partition için hesaplanır:

SELECT categoryid, productid, unitprice,
 RANK() OVER(PARTITION BY categoryid ORDER BY unitprice DESC) AS pricerank
FROM Production.Products
ORDER BY categoryid, pricerank;

Dönen sonuç:

categoryid productid unitprice pricerank
----------- ----------- --------- ---------
1 38 263.50 1
1 43 46.00 2
1 2 19.00 3
2 63 43.90 1
2 8 40.00 2
2 61 28.50 3
2 6 25.00 4
3 20 81.00 1
3 62 49.30 2
3 27 43.90 3
3 26 31.23 4 

Window fonksiyonu OVER tarafından sınırlandırılmış küme üzerinde partition eklenmesiyle daha ayrıntılı düzeyde çalışmaktadır.

Window Aggregate Fonksiyonları

Bir satır kümesi üzerinde aggregate çalıştırılarak tek değer döndülür. Ancak window bağlamında kullanıldığında, GROUP BY’dan dönen kümede değil, OVER’dan gelen küme üzerinde çalışırlar.

Window aggregate işlevleri, partition, sıralama ve frame gibi window ile gayet kullanışlı olmaktadır. Diğer window işlevlerinden farklı olarak, aggregate işlevinde bir frame belirtilmezse sıralama işlemi uygulamak gerekli değildir.

Aşağıdaki örnekte, yeni sütunda görüntülenen müşteri başına toplam satış miktarını çekmek için SUM işlevi kullanılmıştır:

SELECT custid,
 ordermonth,
 qty,
 SUM(qty) OVER ( PARTITION BY custid ) AS totalpercust
FROM Sales.CustOrders; 

Dönen sonuç:

custid ordermonth qty totalpercust
----------- ----------------------- ----------- ------------
1 2007-08-01 00:00:00.000 38 174
1 2007-10-01 00:00:00.000 41 174
1 2008-01-01 00:00:00.000 17 174
2 2006-09-01 00:00:00.000 6 63
2 2007-08-01 00:00:00.000 18 63
3 2006-11-01 00:00:00.000 24 359
3 2007-04-01 00:00:00.000 30 359
3 2007-05-01 00:00:00.000 80 359
4 2007-02-01 00:00:00.000 40 650
4 2007-06-01 00:00:00.000 96 650 

Toplama işleminin tekrarlanması işe yarar gözükmese de, window aggregation işleminin sonucu üzerinde  manipülasyon gerçekleştirebilirsiniz. Mesela bu yolla her satışın müşteriye oranı belirlenebilir:

SELECT custid, ordermonth, qty,
 SUM(qty) OVER ( PARTITION BY custid ) AS custtotal,
 CAST(100. * qty/SUM(qty) OVER ( PARTITION BY custid )AS NUMERIC(8,2)) AS
 OfTotal
FROM Sales.CustOrders; 

Dönen sonuç:

custid ordermonth qty custtotal OfTotal
------ ----------------------- --- ---------- -------
1 2007-08-01 00:00:00.000 38 174 21.84
1 2007-10-01 00:00:00.000 41 174 23.56
1 2008-01-01 00:00:00.000 17 174 9.77
1 2008-03-01 00:00:00.000 18 174 10.34
1 2008-04-01 00:00:00.000 60 174 34.48
2 2006-09-01 00:00:00.000 6 63 9.52
2 2007-08-01 00:00:00.000 18 63 28.57
2 2007-11-01 00:00:00.000 10 63 15.87
2 2008-03-01 00:00:00.000 29 63 46.03
3 2006-11-01 00:00:00.000 24 359 6.69
3 2007-04-01 00:00:00.000 30 359 8.36
3 2007-05-01 00:00:00.000 80 359 22.28
3 2007-06-01 00:00:00.000 83 359 23.12
3 2007-09-01 00:00:00.000 102 359 28.41
3 2008-01-01 00:00:00.000 40 359 11.14 

Window Ranking Fonksiyonları

Window ranking işlevleri, window’daki bir satırın sırasını döndürür. Her satırın konumunu belirlemek için OVER ifadesinde ORDER BY ifadesinin kullanılması gerekir.

Not: OVER’daki ORDER BY ifadesi yalnızca window işlevindeki satırlara etki eder. Sonucu sıralamak için SELECT ifadesinde ORDER BY ifadesi kullanmanız gereklidir.

RANK ve DENSE_RANK arasındaki temel fark satırların işlenme şeklidir.

Örneğin, aşağıdaki sorguda RANK’ın kullanılacağı satır değerlerinden sonra numaralandırmaya boşluk eklenirken DENSE_RANK’ta eklenmez:

SELECT CatID, CatName, ProdName, UnitPrice,
 RANK() OVER(PARTITION BY CatID ORDER BY UnitPrice DESC) AS PriceRank,
 DENSE_RANK() OVER(PARTITION BY CatID ORDER BY UnitPrice DESC) AS DensePriceRank
FROM Production.CategorizedProducts
ORDER BY CatID; 

Dönen sonuçta birim fiyatı 18.00 olan ürünleri takip eden sıraların sıra numaralandırmasına dikkat edin:

CatID CatName ProdName UnitPrice PriceRank DensePriceRank
----- --------- ------------- --------- --------- --------------
1 Beverages Product QDOMO 263.50 1 1
1 Beverages Product ZZZHR 46.00 2 2
1 Beverages Product RECZE 19.00 3 3
1 Beverages Product HHYDP 18.00 4 4
1 Beverages Product LSOFL 18.00 4 4
1 Beverages Product NEVTJ 18.00 4 4
1 Beverages Product JYGFE 18.00 4 4
1 Beverages Product TOONT 15.00 8 5
1 Beverages Product XLXQF 14.00 9 6
1 Beverages Product SWNJY 14.00 9 6
1 Beverages Product BWRLG 7.75 11 7
1 Beverages Product QOGNU 4.50 12 8 

Window Distribution Fonksiyonları

Window distribution işlevleriyle, window veya window partition’daki satırlar üzerinde istatistiksel analiz gerçekleştirebilirsiniz. Distribution işlevlerini kullanırken window partition oluşturmak size kalmış fakat sıralama işlemi uygulamak zorunludur.

Distribution işlevleri satır sırası döndürür ancak RANK, DENSE_RANK veya ROW_NUMBER fonksiyonları gibi ondalık sayıları kullanmak yerine 0 ile 1 arasında bir oran döndürür. SQL Server’da PERCENT_RANK ve CUME_DIST işlevleriyle dağılım hesaplamaları yapılabilir. PERCENTILE_CONT ve PERCENTILE_DISC fonksiyonları ile ters dağılım yapılır.

Window Offset Functions

Windows offset işlevlerini kullanarak geçerli satırdan diğer satırlardaki değerlere erişim sağlayabilirsiniz. Bu sayede self-join işlemine gerek kalmadan satırlar arasında karşılaştırma işlemi yapabilirsiniz.

Offset fonksiyonları geçerli satıra göre veya window frame’inin başlangıç veya bitiş değerlerine göre çalışır. LAG ve LEAD ifadeleri hedef satırı işaret eden offset üzerinde çalışır. FIRST_VALUE ve LAST_VALUE ise frame’deki bir offset üzerinde çalışır.

Not: FIRST_VALUE ve LAST_VALUE frame’deki offsetlerde çalıştığından, RANGE BETWEEN, UNBOUND PRECEDING, ve CURRENT ROW haricindeki frame ifadelerini belirtmek gereklidir.

Aşağıdaki örnekte, yıllık satışları karşılaştırmak için LEAD işlevi kullanılmıştır. Offset değeri 1’dir ve fonksiyon sonraki satırın değerini döndürür. Bir sonraki satırın değeri NULL ise fonksiyon 0 değerini döndürür:

SELECT employee, orderyear ,totalsales AS currsales,
 LEAD(totalsales, 1,0) OVER (PARTITION BY employee ORDER BY orderyear) AS nextsales
FROM Sales.OrdersByEmployeeYear
ORDER BY employee, orderyear; 

Dönen sonuç:

employee orderyear currsales nextsales
-------- --------- --------- ---------
1 2006 38789.00 97533.58
1 2007 97533.58 65821.13
1 2008 65821.13 0.00
2 2006 22834.70 74958.60
2 2007 74958.60 79955.96
2 2008 79955.96 0.00
3 2006 19231.80 111788.61
3 2007 111788.61 82030.89
3 2008 82030.89 0.00 

Kaynak:
Ranking Functions (Transact-SQL)
Analytic Functions (Transact-SQL)