Window Fonksiyonları – Bölüm 42
SQL Server’da window fonksiyonları kullanarak sıralama, aggregation ve satırları offset kullanarak karşılaştırma işlemleri gerçekleştirebilirsiniz. Sorguda bu fonksiyonlarla bir küme, window ya da belirli aralıktaki satırlar kullanılır. Kümeleri tanımlarken OVER ifadesi kullanılır.
SQL Windowing
SQL Server’da kümelerle çalışmanın başka bir yöntemi de windowing işlemidir. Bu teknik T-SQL sorguları yazarken karşılaşılan birçok soruna çözüm getirmektedir. Örneğin sonuç tablosunda kolayca satır numarası oluşturulabilir ve toplam çalışanlar hesaplanabilir. Ayrıca window kullanarak self-join işlemine gerek kalmadan bir satırdaki değeri başka bir satırdaki değerle kolaylıkla karşılaştırabilirsiniz.
Window kullanırken birkaç şeye dikkat etmek gerekiyor:
1. Dönen sonuçtaki sıralamayı etkilemeyecek şekilde window fonksiyonuna sıralanmış şekilde input verilebilir.
2. Partitioning özelliğiyle fonksiyonun sadece geçerli satırla aynı değere sahip satırlar üzerinde işlem yapması sağlanabilir.
3. Window fonksiyonu ayrıca bir frame opsiyonu da sunmaktadır. Frame’de alt ve üst sınırlar belirlenerek window’da başka alt kümeleri oluşturarak kullanabilir.
Aşağıdaki örnekte aggregate window fonksiyonuyla toplam çalışan sayısı hesaplanmıştır:
SELECT Category, Qty, Orderyear,
SUM(Qty) OVER (PARTITION BY Category ORDER BY Orderyear
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningQty
FROM Sales.CategoryQtyYear; Dönen sonuç:
Category Qty Orderyear RunningQty
--------------- ----- ---------- -----------
Beverages 1842 2006 1842
Beverages 3996 2007 5838
Beverages 3694 2008 9532
Condiments 962 2006 962
Condiments 2895 2007 3857
Condiments 1441 2008 5298
Confections 1357 2006 1357
Confections 4137 2007 5494
Confections 2412 2008 7906
Dairy Products 2086 2006 2086
Dairy Products 4374 2007 6460
Dairy Products 2689 2008 9149 Windowing Bileşenleri
T-SQL’de window’ları ve window fonksiyonları kullanırken her zaman OVER deyimi kullanılır. Ayrıca, PARTITION BY seçeneğiyle kayıtları bölümlere ayırabilir ve üstüne framing ile fonksiyonun hangi satırları kullanacağını da belirleyebilirsiniz. Bu bileşenler arasındaki ilişkiyi anlamak çok önemlidir.
Bu ilişkide bir hiyerarşi vardır. Bir eleman önceki elemanın satır çıktısı üzerinde önceki elemandan daha fazla veri işlemi gerçekleştirir:
- OVER ile window işlevinde kullanılacak aralık belirlenir. Partition tanımlanarak OVER ifadesine sınırlar koyulur. Tüm satırlar fonksiyona input olarak verilir.
- PARTITION BY ifadesiyle sonuç tablosu, geçerli satırdan başlayarak partition içerisinde aynı sütun değerlerine sahip olan satırlarla sınırlanır. Örneğin “PARTITION BY custid” ifadesiyle window, geçerli satırla aynı custid değerine sahip olan satırlar ile sınırlanır. PARTITION BY ifadesi, OVER olmadan kullanılamaz. (Window partition ifadesi olmayan OVER ifadesi, 1 partition olarak kabul edilir).
- ROW ve RANGE ifadeleriyle partition için alt ve üst sınır belirlenerek bir frame oluşturulur. Frame oluştururken OVER ifadesinde ORDER BY kullanılması gerekir.
Aşağıdaki örnekte Qty sütunu OVER ile category sütununda partition yapılarak tanımlanan window kullanarak aggregate edilmiş ayrıca ilk satırdan mevcut satıra kadar frame oluşturulmuş ve orderyear sütunu da sort edilmiştir. Böylelikle hareketli frame oluşturmuş olduk:
SELECT Category, Qty, Orderyear,
SUM(Qty) OVER (PARTITION BY category ORDER BY Orderyear
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningQty
FROM Sales.CategoryQtyYear; Her bir bileşenin ayrıntılarını sonraki bölümlerde ele alacağız.
Not: Bir sorguda aynı anda her biri kendi OVER ifadesiyle olan window fonksiyonu kullanılabilir. Her ifade kendi sıralama düzenini, partition ve frame’ini belirler.
OVER Kullanımı
OVER ifadesi, bir sonraki bölümde ele alacağımız bir window fonksiyonu tarafından çalıştırılan satır kümesini tanımlar. OVER deyimi, her birinin geçerli olduğu yerlerde bölümleme, sıralama ve çerçeveleme içerir.
OVER ifadesi tek başına kullanıldığında, window fonksiyonuna iletilen sonuç kümesine kısıtlama getirmez. PARTITION BY ile kullanıldığında kümeyi partition elemanlarıyla aynı değerlere sahip satırlarla sınırlar.
Aşağıdaki örnekte, ROW_NUMBER fonksiyonunun işlem yapacağı kullanılacak frame’i olmayan kısıtlanmamış window kümesi tanımlamak için OVER kullanılmıştır. Tüm satırlar, ROW_NUMBER için gerekli olan ORDER BY deyimi kullanılarak numaralandırılır. Satır numaraları Running adlı yeni bir sütunda görüntülenir:
SELECT Category, Qty, Orderyear,
ROW_NUMBER() OVER (ORDER BY Qty DESC) AS Running
FROM Sales.CategoryQtyYear
ORDER BY Running; Gösterme amaçlı sıralama işlemi yaptığımız sonuç:
Category Qty Orderyear Running
--------------- ----------- ----------- --
Dairy Products 4374 2007 1
Confections 4137 2007 2
Beverages 3996 2007 3
Beverages 3694 2008 4
Seafood 3679 2007 5
Condiments 2895 2007 6
Seafood 2716 2008 7
Dairy Products 2689 2008 8
Grains/Cereals 2636 2007 9 Window Üzerinde Partition İşlemi
Bir window’da partition yapılırken işlem kümesi partition sütunuyla aynı değere sahip satırlarla sınırlandırılır.
Örneğin, aşağıdaki kodda PARTITION BY ile kategoriye göre bir window partition oluşturulmuştur. Bu örnekte partition yalnızca bir içecek veya bir şekerleme kategorisindeki satırlar içerir:
<function_name>() OVER(PARTITION BY Category) Belirttiğimiz gibi, hiç partition tanımlanmamışsa, OVER() ile arkaplandaki sorgudan dönen sonuç kümesindeki tüm satırlar window işlevine verilir.
Aşağıdaki örnekte OVER ifadesine PARTITION BY eklenerek Category değerleriyle eşleşen satırlar yardımıyla bir window oluşturulmuştur. Böylece ROW_NUMBER işleviyle kategori başına her yıl ayrı ayrı numaralandırılır:
SELECT Category, Qty, Orderyear,
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Qty DESC) AS Running
FROM Sales.CategoryQtyYear
ORDER BY Category; Dönen sonuç:
Category Qty Orderyear Running
--------------- ----------- ----------- ---
Beverages 3996 2007 1
Beverages 3694 2008 2
Beverages 1842 2006 3
Condiments 2895 2007 1
Condiments 1441 2008 2
Condiments 962 2006 3
Confections 4137 2007 1
Confections 2412 2008 2
Confections 1357 2006 3 Sıralama ve Frame işlemi
Öğrendiğimiz gibi, OVER ile tanımlanan outer window içerisinde alt satır kümesi oluşturmak için partition kullanılır. Benzer bir yaklaşım da, window frame ile window işlevinde kullanılacak satırlar daha da kısıtlanabilir. Bir frame’i, belirttiğimiz sınırlarda vuku bulan bir window olarak da düşünebilirsiniz.
Window frame için başlangıç ve bitişi tanımlarken ROW veya RANGE kullanılır. Örneğin partition’ın ilk satırından belli bir satıra kadar tanımlanan frame oluşturmak için aşağıdaki sıra izlenir:
1. PARTITION BY kullanarak OVER ifadesi tanımlanır.
2. OVER içerisinde ORDER BY tanımlanır. Bu ilk satırı belirlerken mantıklı davranmamızı sağlayacaktır.
3. UNBOUNDED PRECEDING ile başlangıç sınırı ve ROWS BETWEEN ile aralık belirlenir. Burda UNBOUNDED ifadesi, PRECEDING’le belirlenen yönde bitişe kadar gitmek anlamına gelir. Bitiş sınırının hesaplanan satır olduğunu belirtmek için CURRENT ROW kullanın.
Not: OVER bir kümeyi döndürdüğünden ve bir kümenin de teorik olarak sıralamaya sahip olmadığından, frame işleminin kullanışlık olması için ORDER BY kullanılır. Burdaki Order By ifadesi, sonuç kümesindeki sıralamayı sağlayan ORDER BY’dan farklı olabilir.
Aşağıdaki örnekte, bahsettiğimiz hareketli window için frame kullanılmıştır. Her window frame’inde SUM fonksiyonu çalıştırılmıştır:
SELECT Category, Qty, Orderyear,
SUM(Qty) OVER (PARTITION BY Category ORDER BY Orderyear
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS RunningQty
FROM Sales.CategoryQtyYear; Dönen sonuç:
Category Qty Orderyear RunningQty
--------------- ----------- ----------- -----------
Beverages 1842 2006 1842
Beverages 3996 2007 5838
Beverages 3694 2008 9532
Condiments 962 2006 962
Condiments 2895 2007 3857
Condiments 1441 2008 5298
Confections 1357 2006 1357
Confections 4137 2007 5494
Confections 2412 2008 7906
Dairy Products 2086 2006 2086
Dairy Products 4374 2007 6460 Kaynak:
OVER Clause (Transact-SQL)
SQL Server Window Functions
Introduction to T-SQL Window Functions