Analiz için pencere işlevlerini uygulama
Analitik sorgular genellikle birden çok satıra yayılan hesaplamalar gerektirir, ancak yine de her bir satırın ayrıntılarını döndürür. Geleneksel toplama işlevleri satırları gruplar halinde daraltarak satır düzeyi bilgilerini kaybeder. Pencere işlevleri, sonuç kümesini daraltmadan geçerli satırla ilgili bir satır kümesinde hesaplamalar yaparak bu sınamayı çözer.
Pencere işlevinin söz dizimlerini anlama
Pencere işlevleri, yan cümle OVER tarafından tanımlanan 'pencere' içerisindeki satırların değerlerini hesaplar. Normal toplama işlevlerinin aksine, pencere işlevleri satırları tek bir çıkış satırında gruplandırmaz. Bunun yerine, sonuçtaki tüm özgün satırları korurken ilgili satırlar arasındaki değerleri hesaplar.
Pencere işlevinin genel söz dizimi şöyledir:
function_name(arguments) OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [ASC | DESC]]
[ROWS | RANGE frame_specification]
)
OVER madde bileşenleri pencerenin nasıl tanımlandığını kontrol eder.
- PARTITION BY: Hesaplama için satırları gruplara (partition) böler
- ORDER BY: Her bölüm içindeki satırların mantıksal sırasını belirler
- SATIRLAR/ARALIK: Geçerli satıra göre çerçeve sınırlarını tanımlar
Aşağıdaki sorgu, müşteri başına sipariş tutarlarının kümülatif toplamını hesaplayan basit bir pencere işlevini açıklar.
SELECT
CustomerID,
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
Uyarı
Yan tümcesinde çerçeve belirtimi olmadan ORDER BY belirttiğinizde, varsayılan çerçeve toplama işlevleri için RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'dir. Bu, birikmeli hesaplamalar oluşturur.
Derecelendirme işlevlerini kullanma
Sıralama işlevleri, bir bölüm içindeki konumlarına göre satırlara sıralı sayılar atar. SQL Server dört derecelendirme işlevi sağlar. Her işlev, bağlamaları farklı işler:
ROW_NUMBER() her satıra benzersiz bir sıralı sayı atar ve bağlı değerler için bile yineleme olmaz:
SELECT
ProductID,
Name,
ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
Sonuç kümesi şöyle görünür:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 2
751 Road-150 Red, 48 3578.27 3
771 Mountain-100 Silver, 38 3399.99 4
Bu sorgu, tüm ürünleri en yüksekten en düşük fiyata göre sıralar. Her ürün, birden çok ürünün aynı fiyatı paylaşıp paylaşmadığına bakılmaksızın benzersiz bir sayı alır.
RANK() bağlı değerlere aynı derecelendirmeyi atar ve ardından sayıları atlayıp bağlantıları hesaba bağlar:
SELECT
ProductID,
Name,
ListPrice,
RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
Sonuç kümesi şöyle görünür:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 4
İki ürün aynı fiyatlara sahip olduğunda, her ikisi de aynı dereceyi alır. Sonraki ürünün derecelendirmesi, daha yüksek dereceye giren toplam ürün sayısını yansıtır ve dizide boşluklar oluşturur.
DENSE_RANK() bağlı değerlere aynı derecelendirmeyi atar ancak sayıları atlamaz:
SELECT
ProductID,
Name,
ListPrice,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
Sonuç kümesi şöyle görünür:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 2
gibi RANK(), bağlı değerler de aynı dereceyi paylaşır. Ancak, DENSE_RANK() sonraki ardışık sayı ile devam eder, böylece bunu farklı fiyat düzeylerini saymak için kullanabilirsiniz.
NTILE(n) satırları belirtilen sayıda kabaca eşit gruba dağıtır:
SELECT
ProductID,
Name,
ListPrice,
NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;
Sonuç kümesi şöyle görünür:
ProductID Name ListPrice PriceQuartile
--------- --------------------------- --------- -------------
749 Road-150 Red, 62 3578.27 1
771 Mountain-100 Silver, 38 3399.99 1
722 LL Road Frame - Black, 58 337.22 2
859 Half-Finger Gloves, S 24.49 4
Bu sorgu, ürünleri fiyata göre dört gruba ayırır. En yüksek fiyatlı ürünler birinci çeyrekte, en düşük fiyatlı olanlar ise dördüncü çeyrektedir. Yüzdebirlik çözümleme veya işi eşit olarak dağıtma için kullanın NTILE() .
Derecelendirme işlevleriyle birleştirmek PARTITION BY , grup başına derecelendirmeleri etkinleştirir:
SELECT
pc.Name AS Category,
p.Name AS Product,
p.ListPrice,
ROW_NUMBER() OVER (
PARTITION BY p.ProductCategoryID
ORDER BY p.ListPrice DESC
) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;
Sonuç kümesi şöyle görünür:
Category Product ListPrice CategoryPriceRank
--------------- ------------------------- --------- -----------------
Road Bikes Road-150 Red, 62 3578.27 1
Road Bikes Road-150 Red, 44 3578.27 2
Mountain Bikes Mountain-100 Silver, 38 3399.99 1
Mountain Bikes Mountain-100 Black, 38 3374.99 2
Bu sorgu, her kategori içindeki ürünleri ayrı ayrı sıralar. Derecelendirme her kategori için 1'den yeniden başlatılır, böylece CategoryPriceRank = 1 filtreleyerek her kategorideki en pahalı ürünü tanımlayabilirsiniz.
Tavsiye
Derece başına tam olarak bir satıra ihtiyacınız olduğunda kullanın ROW_NUMBER() (grup başına ilk N'yi bulmak gibi).
RANK() Kravat bilgilerini korumanız gerektiğinde veya raporlama amacıyla kullanın DENSE_RANK().
Toplama penceresi işlevlerini uygulama
Standart toplama işlevleri olan SUM, AVG, COUNT, MIN ve MAX, OVER yan tümcesi eklenerek pencere işlevleri olarak kullanılabilir. Bu, tek tek satır ayrıntılarını korurken toplamları hesaplamanıza olanak tanır.
Aşağıdaki sorgu, çalışan toplamları ve toplu toplamları hesaplamayı gösterir:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;
Sonuç kümesi şöyle görünür:
SalesOrderID OrderDate TotalDue RunningTotal RunningAverage OrderNumber
------------ ---------- --------- ------------ -------------- -----------
71774 2008-06-01 972.785 972.785 972.785 1
71776 2008-06-01 87.083 1059.868 529.934 2
71780 2008-06-01 42452.65 43512.518 14504.172 3
71782 2008-06-01 43962.79 87475.308 21868.827 4
Önemli
OVER yan tümcesinde ORDER BY olmadan toplu pencere fonksiyonları kullanıldığında, fonksiyon tüm bölümü kapsayacak şekilde hesaplar.
ORDER BY eklemek, bölüm başlangıcından geçerli satıra kadar süregelen bir hesaplama oluşturur.
ROWS ve RANGE ile pencere çerçevelerini tanımlayın
Pencere çerçeveleri, geçerli satıra göre tam olarak hangi satırların hesaplamaya dahil edilmesi gerektiğini belirtmenize olanak verir.
ROWS yan tümcesi fiziksel satırları sayarkenRANGE, satırları eşit değerlerle gruplandırıyor.
Çerçeve sınırları şu şekilde belirtilebilir:
-
UNBOUNDED PRECEDING: Bölüm başlangıcından -
n PRECEDING:ngeçerli satırdan önceki satırlar -
CURRENT ROW: Geçerli satır -
n FOLLOWING:ngeçerli satırdan sonraki satırlar -
UNBOUNDED FOLLOWING: Bölüm sonuna
Aşağıdaki sorgu, son üç sipariş üzerindeki hareketli ortalamayı hesaplar:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
AVG(TotalDue) OVER (
ORDER BY OrderDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
Sonuç kümesi şöyle görünür:
SalesOrderID OrderDate TotalDue MovingAvg3Orders
------------ ---------- --------- ----------------
71774 2008-06-01 972.785 972.785
71776 2008-06-01 87.083 529.934
71780 2008-06-01 42452.65 14504.172
71782 2008-06-01 43962.79 28834.174
Bu sorgu, geçerli satırı ve önceki iki satırı ekleyerek 3 sıralı bir hareketli ortalama hesaplar. İlk satır için yalnızca bir değer kullanılabilir, dolayısıyla ortalama eşittir TotalDue. Üçüncü sıraya gelindiğinde, pencere tüm üç sırayı içerir.
Analitik işlevleri kullanma
Analitik işlevler, kendi kendine birleşimler veya alt sorgular kullanmadan diğer satırlardan verilere erişmenizi sağlar. Bu işlevler zaman serisi analizi, eğilim algılama ve geçerli değerleri geçmiş veya gelecekteki değerlerle karşılaştırmak için kullanışlıdır. Analiz işlevleri, özetleri hesaplayan toplama penceresi işlevlerinin aksine, penceredeki belirli satırlardan belirli değerleri alır.
LAG() ve LEAD() aşağıdaki gibi önceki veya sonraki satırlardan değerlere erişin:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
Sonuç kümesi şöyle görünür:
SalesOrderID OrderDate TotalDue PreviousOrderTotal NextOrderTotal ChangeFromPrevious
------------ ---------- --------- ------------------ -------------- ------------------
71774 2008-06-01 972.785 0 87.083 972.785
71776 2008-06-01 87.083 972.785 42452.65 -885.702
71780 2008-06-01 42452.65 87.083 43962.79 42365.567
71782 2008-06-01 43962.79 42452.65 0 1510.14
LAG() önceki bir satırdan bir değer alırken, LEAD() aşağıdaki satırdan alır. İkinci parametre, geriye veya ileriye bakılması gereken satır sayısını belirtir (varsayılan değer 1'dir) ve üçüncü parametre satır olmadığında (örneğin, ile LAG()ilk satır için) varsayılan bir değer sağlar. Dönem içindeki değişiklikleri hesaplamak, eğilimleri belirlemek veya sıralı verilerdeki anomalileri algılamak için bu işlevleri kullanın.
FIRST_VALUE() ve LAST_VALUE() çerçevedeki ilk veya son satırdaki değerleri döndürür:
SELECT
ProductID,
Name,
ListPrice,
ProductCategoryID,
FIRST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
) AS MostExpensiveInCategory,
LAST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;
Sonuç kümesi şöyle görünür:
ProductID Name ListPrice ProductCategoryID MostExpensiveInCategory LeastExpensiveInCategory
--------- ------------------------- --------- ----------------- ------------------------ ------------------------
749 Road-150 Red, 62 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
750 Road-150 Red, 44 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
722 LL Road Frame - Red, 58 337.22 5 Road-150 Red, 62 LL Road Frame - Red, 58
771 Mountain-100 Silver, 38 3399.99 6 Mountain-100 Silver, 38 Mountain-500 Black, 52
FIRST_VALUE() , sipariş edilen penceredeki ilk satırdaki değeri döndürür. Bu örnekte kategori başına en pahalı üründür.
LAST_VALUE() en düşük maliyetli değeri döndürür, ancak tüm satırları dahil etmek için açık bir çerçeve gerektirir. Bu işlevler, her satırı bir gruptaki en yüksek, en düşük veya temel değer gibi karşılaştırma değerleriyle karşılaştırmanıza yardımcı olur.
Uyarı
LAST_VALUE() geçerli satırdan sonraki satırları eklemek için açık bir çerçeve belirtimi gerektirir. Bu olmadan, varsayılan çerçeve yalnızca geçerli satıra kadar olan satırları içerir ve LAST_VALUE() geçerli satırın değerini döndürür.
PERCENT_RANK() ve CUME_DIST() bölümün içindeki göreli konumu hesaplar:
SELECT
Name,
ListPrice,
PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;
Sonuç kümesi şöyle görünür:
Name ListPrice PercentRank CumulativeDistribution
------------------------- --------- ----------- ----------------------
Patch Kit/8 Patches 2.29 0.0 0.0081
Road Tire Tube 3.99 0.0081 0.0162
Touring Tire Tube 4.99 0.0162 0.0243
Road-150 Red, 62 3578.27 0.9919 1.0
PERCENT_RANK() 0 ile 1 arasında, hangi satır yüzdesinin daha düşük değerlere sahip olduğunu belirten bir değer döndürür (0 en düşük, biri en yüksek anlamına gelir).
CUME_DIST() , geçerli satıra eşit veya daha küçük değerlere sahip satırların yüzdesini gösteren birikmeli dağılımı gösterir. Yüzdebirlik çözümleme, aykırı değerleri belirleme veya dağıtım raporları oluşturma için bu işlevleri kullanın.
Pencere işlevleri hakkında daha fazla bilgi için bkz. Pencere İşlevleri (Transact-SQL) ve Derecelendirme İşlevleri.