Not
Bu sayfaya erişim yetkilendirme gerektiriyor. Oturum açmayı veya dizinleri değiştirmeyi deneyebilirsiniz.
Bu sayfaya erişim yetkilendirme gerektiriyor. Dizinleri değiştirmeyi deneyebilirsiniz.
Şunlar için geçerlidir:
SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiAzure Synapse AnalyticsAnaliz Platformu Sistemi (PDW)Microsoft Fabric'teki SQL analiz uç noktasıMicrosoft Fabric'teki ambarMicrosoft Fabric'teki SQL veritabanı
OVER yan tümcesi, ilişkili pencere işlevi uygulanmadan önce satır kümesinin bölümlemesi ve sırasını belirler. Diğer bir ifadeyle, OVER yan tümcesi bir sorgu sonuç kümesi içinde bir pencere veya kullanıcı tarafından belirtilen satır kümesini tanımlar. Ardından bir pencere işlevi, penceredeki her satır için bir değer hesaplar.
OVER yan tümcesini hareket ortalamaları, toplu toplamlar, çalışan toplamlar veya grup başına N en üst gibi toplu değerleri hesaplamak için işlevlerle birlikte kullanabilirsiniz.
- Sıralama Fonksiyonları
- Toplama İşlevleri
- Analitik fonksiyonlar
- için SONRAKI DEĞER
Transact-SQL söz dizimi kuralları
Syntax
SQL Server, Azure SQL Veritabanı ve Azure Synapse Analytics için söz dizimi.
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ , ...n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
<window frame extent> ::=
{ <window frame preceding>
| <window frame between>
}
<window frame between> ::=
BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::=
{ <window frame preceding>
| <window frame following>
}
<window frame preceding> ::=
{
UNBOUNDED PRECEDING
| <unsigned_value_specification> PRECEDING
| CURRENT ROW
}
<window frame following> ::=
{
UNBOUNDED FOLLOWING
| <unsigned_value_specification> FOLLOWING
| CURRENT ROW
}
<unsigned value specification> ::=
{ <unsigned integer literal> }
Paralel Veri Ambarı söz dizimi.
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Arguments
Pencere işlevlerinin OVER yan tümcesinde aşağıdaki bağımsız değişkenler olabilir:
Order BY , sonuç kümesinin her bölümü içindeki satırların mantıksal sırasını tanımlar.
SATıRLAR veya ARALIK bölümün içindeki satırları, bölümdeki başlangıç ve bitiş noktalarını belirterek sınırlar.
ORDER BYbağımsız değişkeni gerektirir veORDER BYbağımsız değişkeni belirtilirse varsayılan değer bölümün başından geçerli öğeye kadardır.
Herhangi bir bağımsız değişken belirtmezseniz, pencere işlevleri sonuç kümesinin tamamına uygulanır.
SELECT object_id,
MIN(object_id) OVER () AS [min],
MAX(object_id) OVER () AS [max]
FROM sys.objects;
| object_id | min | max |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
BÖLÜMLEME
Sorgu sonuç kümesini bölümlere ayırır. Pencere işlevi her bölüme ayrı ayrı uygulanır ve her bölüm için hesaplama yeniden başlatılır.
PARTITION BY <value_expression>
PARTITION BY belirtilmezse, işlev sorgu sonuç kümesinin tüm satırlarını tek bir bölüm olarak ele alır.
ORDER BY yan tümcesi belirtmezseniz işlev bölümdeki tüm satırlara uygulanır.
value_expression GÖRE BÖLÜMLEME
Satır kümesinin bölümlendiği sütunu belirtir.
value_expression yalnızca FROM yan tümcesi tarafından sağlanan sütunlara başvurabilir.
value_expression seçim listesindeki ifadelere veya diğer adlara başvuramaz.
value_expression bir sütun ifadesi, skaler alt sorgu, skaler işlev veya kullanıcı tanımlı değişken olabilir.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type) AS [min],
MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;
| object_id | type | min | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
SIRALAMA
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Sonuç kümesinin her bölümü içindeki satırların mantıksal sırasını tanımlar. Yani, pencere işlevi hesaplamasının gerçekleştirildiği mantıksal sırayı belirtir.
Belirtilmezse, varsayılan sıra
ASCve pencere işlevi bölümdeki tüm satırları kullanır.Belirtilirse ve
ROWSveyaRANGEbelirtilmezse, isteğe bağlı birRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWveyaROWSbelirtimini kabul eden işlevler (örneğin,RANGEveyamin) pencere çerçevesi için varsayılanmaxkullanılır.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;
| object_id | type | min | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 68195293 |
| 631673298 | PK | 68195293 | 631673298 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | |
| 3 | S | 3 | 3 |
| 5 | S | 3 | 5 |
| 6 | S | 3 | 6 |
| ... | ... | ... | |
| 97 | S | 3 | 97 |
| 98 | S | 3 | 98 |
| ... | ... | ... |
order_by_expression
Sıralanacak sütunu veya ifadeyi belirtir.
order_by_expression yalnızca FROM yan tümcesi tarafından sağlanan sütunlara başvurabilir. Bir sütun adını veya diğer adını temsil etmek için tamsayı belirtilemiyor.
HARMANLAMA collation_name
ORDER BY işleminin collation_nameiçinde belirtilen harmanlama göre gerçekleştirilmesi gerektiğini belirtir.
collation_name bir Windows harmanlama adı veya SQL harmanlama adı olabilir. Daha fazla bilgi için bkz. Harmanlama ve Unicode desteği.
COLLATE yalnızca char, varchar, ncharve nvarchartüründe sütunlar için geçerlidir.
ASC | DESC
Belirtilen sütundaki değerlerin artan veya azalan düzende sıralanması gerektiğini belirtir.
ASC varsayılan sıralama düzenidir. Null değerler, mümkün olan en düşük değerler olarak kabul edilir.
SATIRLAR veya ARALIK
için geçerlidir: SQL Server 2012 (11.x) ve sonraki sürümler.
Bölüm içindeki başlangıç ve bitiş noktalarını belirterek bölüm içindeki satırları da sınırlar. Mantıksal ilişkilendirmeye veya fiziksel ilişkilendirmeye göre geçerli satıra göre bir satır aralığı belirtir. Fiziksel ilişkilendirme, ROWS yan tümcesi kullanılarak elde edilir.
ROWS yan tümcesi, geçerli satırın önünde veya sonrasında sabit sayıda satır belirterek bölüm içindeki satırları sınırlar. Alternatif olarak, RANGE yan tümcesi geçerli satırdaki değere göre bir değer aralığı belirterek bölüm içindeki satırları mantıksal olarak sınırlar. Önceki ve sonraki satırlar ORDER BY yan tümcesindeki sıralamaya göre tanımlanır. Pencere çerçevesi RANGE ... CURRENT ROW ..., ORDER BY ifadesinde geçerli satırla aynı değerlere sahip tüm satırları içerir. Örneğin, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW işlevin üzerinde çalıştığı satır penceresinin, geçerli satıra kadar ve geçerli satırın dahil olduğu 2 satırdan başlayarak üç satır boyutunda olduğu anlamına gelir.
SELECT object_id,
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;
| object_id | preceding | central | following |
|---|---|---|---|
| 3 | 1 | 3 | 156 |
| 5 | 2 | 4 | 155 |
| 6 | 3 | 5 | 154 |
| 7 | 4 | 5 | 153 |
| 8 | 5 | 5 | 152 |
| ... | ... | ... | ... |
| 2112726579 | 153 | 5 | 4 |
| 2119678599 | 154 | 5 | 3 |
| 2123154609 | 155 | 4 | 2 |
| 2139154666 | 156 | 3 | 1 |
ROWS veya RANGE, ORDER BY yan tümcesini belirtmenizi gerektirir.
ORDER BY birden çok sipariş ifadesi içeriyorsa, CURRENT ROW FOR RANGE geçerli satırı belirlerken ORDER BY listesindeki tüm sütunları dikkate alır.
SINIRSIZ KORUMA
için geçerlidir: SQL Server 2012 (11.x) ve sonraki sürümler.
Pencerenin bölümün ilk satırında başlatıldığını belirtir.
UNBOUNDED PRECEDING yalnızca pencere başlangıç noktası olarak belirtilebilir.
<ÖNCEKİ> imzalanmamış değer belirtimi
Geçerli satırdan önce gelen satır veya değer sayısını belirtmek için <unsigned value specification> ile belirtilir. bu belirtim RANGEiçin izin verilmez.
GÜNCEL SIRA
için geçerlidir: SQL Server 2012 (11.x) ve sonraki sürümler.
pencerenin ROWS ile kullanıldığında geçerli satırda veya RANGEile kullanıldığında geçerli değerle başladığını veya sona erdiğini belirtir.
CURRENT ROW hem başlangıç hem de bitiş noktası olarak belirtilebilir.
ARASINDA VE
için geçerlidir: SQL Server 2012 (11.x) ve sonraki sürümler.
BETWEEN <window frame bound> AND <window frame bound>
Pencerenin alt (başlangıç) ve üst (bitiş) sınır noktalarını belirtmek için ROWS veya RANGE ile kullanılır.
<window frame bound> sınır başlangıç noktasını ve <window frame bound> sınır uç noktasını tanımlar. Üst sınır alt sınırdan küçük olamaz.
SINIRSIZ TAKIPÇI
için geçerlidir: SQL Server 2012 (11.x) ve sonraki sürümler.
Pencerenin bölümün son satırında sona erdiğini belirtir.
UNBOUNDED FOLLOWING yalnızca pencere uç noktası olarak belirtilebilir. Örneğin, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING geçerli satırla başlayan ve bölümün son satırıyla biten bir pencere tanımlar.
<imzalanmamış değer belirtimi> TAKİP EDİLEN
Geçerli satırı takip etmek üzere satır veya değer sayısını belirtmek için <unsigned value specification> ile belirtilir. pencere başlangıç noktası olarak <unsigned value specification> FOLLOWING belirtildiğinde, bitiş noktası <unsigned value specification> FOLLOWINGolmalıdır. Örneğin, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING geçerli satırı izleyen ikinci satırla başlayan ve geçerli satırı izleyen onuncu satırla biten bir pencere tanımlar. bu belirtim RANGEiçin izin verilmez.
İmzasız tamsayı değişmez<>
için geçerlidir: SQL Server 2012 (11.x) ve sonraki sürümler.
Geçerli satır veya değerin önüne eklenecek veya izleyebileceğiniz satır veya değer sayısını belirten pozitif bir tamsayı değişmez değeri (0dahil). Bu belirtim yalnızca ROWSiçin geçerlidir.
Remarks
Tek bir FROM yan tümcesiyle tek bir sorguda birden fazla pencere işlevi kullanılabilir. Her işlevin OVER yan tümcesi bölümleme ve sıralama açısından farklılık gösterebilir.
PARTITION BY belirtilmezse, işlev sorgu sonuç kümesinin tüm satırlarını tek bir grup olarak işler.
Important
ROWS veya RANGE belirtilirse ve <window frame preceding> (kısa söz dizimi) için <window frame extent> kullanılırsa, bu belirtim pencere çerçevesi sınırı başlangıç noktası için kullanılır ve sınır bitiş noktası için CURRENT ROW kullanılır. Örneğin, ROWS 5 PRECEDINGROWS BETWEEN 5 PRECEDING AND CURRENT ROWeşittir.
ORDER BY belirtilmezse, tüm bölüm bir pencere çerçevesi için kullanılır. Bu yalnızca ORDER BY yan tümcesi gerektirmeyen işlevler için geçerlidir.
ROWS veya RANGE belirtilmezse ancak ORDER BY belirtilirse, pencere çerçevesi için varsayılan olarak RANGE UNBOUNDED PRECEDING AND CURRENT ROW kullanılır. Bu yalnızca isteğe bağlı ROWS veya RANGE belirtimlerini kabul edebilen işlevler için geçerlidir. Örneğin, derecelendirme işlevleri ROWS veya RANGEkabul edemediğinden, ORDER BY mevcut olsa ve ROWS veya RANGE olmasa bile bu pencere çerçevesi uygulanmaz.
Limitations
OVER yan tümcesi DISTINCT toplamalarıyla kullanılamaz.
RANGE
<unsigned value specification> PRECEDING veya <unsigned value specification> FOLLOWINGile kullanılamaz.
OVER yan tümcesiyle kullanılan derecelendirme, toplama veya analiz işlevine bağlı olarak <ORDER BY clause> ve/veya <ROWS and RANGE clause> desteklenmeyebilir.
Examples
Bu makaledeki kod örnekleri, AdventureWorks2025 giriş sayfasından indirebileceğiniz AdventureWorksDW2025 veya örnek veritabanını kullanır.
A. over yan tümcesini ROW_NUMBER işleviyle kullanma
Aşağıdaki örnekte, bir bölümdeki her satır için bir satır numarası görüntülemek üzere OVER işleviyle ROW_NUMBER yan tümcesinin kullanılması gösterilmektedir.
ORDER BY yan tümcesinde belirtilen OVER yan tümcesi, her bölümdeki satırları SalesYTDsütununa göre sıralar.
ORDER BY deyimindeki SELECT yan tümcesi, sorgu sonuç kümesinin tamamının döndürüleceği sırayı belirler.
USE AdventureWorks2022;
GO
SELECT ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName,
s.SalesYTD,
a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode;
GO
Sonuç kümesi aşağıdadır.
Row Number LastName SalesYTD PostalCode
--------------- ----------------------- --------------------- ----------
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116871.2277 98055
2 Varkey Chudukatil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
B. OVER yan tümcesini toplama işlevleriyle kullanma
Aşağıdaki örnek, sorgu tarafından döndürülen tüm satırlar üzerinde toplama işlevleriyle OVER yan tümcesini kullanır. Bu örnekte, OVER yan tümcesini kullanmak, toplama değerlerini türetmek için alt sorguları kullanmaktan daha verimlidir.
USE AdventureWorks2022;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg",
COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Count",
MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min",
MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
Sonuç kümesi aşağıdadır.
SalesOrderID ProductID OrderQty Total Avg Count Min Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659 776 1 26 2 12 1 6
43659 777 3 26 2 12 1 6
43659 778 1 26 2 12 1 6
43659 771 1 26 2 12 1 6
43659 772 1 26 2 12 1 6
43659 773 2 26 2 12 1 6
43659 774 1 26 2 12 1 6
43659 714 3 26 2 12 1 6
43659 716 1 26 2 12 1 6
43659 709 6 26 2 12 1 6
43659 712 2 26 2 12 1 6
43659 711 4 26 2 12 1 6
43664 772 1 14 1 8 1 4
43664 775 4 14 1 8 1 4
43664 714 1 14 1 8 1 4
43664 716 1 14 1 8 1 4
43664 777 2 14 1 8 1 4
43664 771 3 14 1 8 1 4
43664 773 1 14 1 8 1 4
43664 778 1 14 1 8 1 4
Aşağıdaki örnekte, hesaplanan değerde bir toplama işleviyle OVER yan tümcesinin kullanılması gösterilmektedir.
USE AdventureWorks2022;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
CAST (1. * OrderQty / SUM(OrderQty) OVER (PARTITION BY SalesOrderID) * 100 AS DECIMAL (5, 2)) AS [Percent by ProductID]
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
Sonuç kümesi aşağıdadır. Toplamalar SalesOrderID ve Percent by ProductID her SalesOrderIDher satırı için hesaplanır.
SalesOrderID ProductID OrderQty Total Percent by ProductID
------------ ----------- -------- ----------- ---------------------------------------
43659 776 1 26 3.85
43659 777 3 26 11.54
43659 778 1 26 3.85
43659 771 1 26 3.85
43659 772 1 26 3.85
43659 773 2 26 7.69
43659 774 1 26 3.85
43659 714 3 26 11.54
43659 716 1 26 3.85
43659 709 6 26 23.08
43659 712 2 26 7.69
43659 711 4 26 15.38
43664 772 1 14 7.14
43664 775 4 14 28.57
43664 714 1 14 7.14
43664 716 1 14 7.14
43664 777 2 14 14.29
43664 771 3 14 21.4
43664 773 1 14 7.14
43664 778 1 14 7.14
C. Hareketli ortalama ve kümülatif toplam oluşturma
Aşağıdaki örnek, AVG ve SUM işlevlerini OVER yan tümcesiyle kullanarak Sales.SalesPerson tablosundaki her bölge için yıllık satışların hareketli ortalamasını ve kümülatif toplamını sağlar. Veriler TerritoryID tarafından bölümlenmiş ve mantıksal olarak SalesYTDsıralanmış. Bu, AVG işlevinin satış yılına göre her bölge için hesaplandığını gösterir. 1'in TerritoryID için, satış yılı için iki satır vardır 2005 bu yıl satışları olan iki satış kişisini temsil eden. Bu iki satırın ortalama satışları hesaplanır ve ardından hesaplamaya 2006 yılın satışlarını temsil eden üçüncü satır eklenir.
USE AdventureWorks2022;
GO
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY TerritoryID, SalesYear;
Sonuç kümesi aşağıdadır.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17
Bu örnekte, OVER yan tümcesi PARTITION BYiçermez. Bu, işlevin sorgu tarafından döndürülen tüm satırlara uygulandığı anlamına gelir.
ORDER BY yan tümcesinde belirtilen OVER yan tümcesi, AVG işlevinin uygulandığı mantıksal sırayı belirler. Sorgu, WHERE yan tümcesinde belirtilen tüm satış bölgeleri için yıla göre hareketli bir satış ortalaması döndürür.
ORDER BY deyiminde belirtilen SELECT yan tümcesi, sorgu satırlarının görüntülenme sırasını belirler.
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY SalesYear;
Sonuç kümesi aşağıdadır.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35
275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93
D. SATIRSAY yan tümcesini belirtin
için geçerlidir: SQL Server 2012 (11.x) ve sonraki sürümler.
Aşağıdaki örnekte, satırların geçerli satır olarak hesaplandığı bir pencere ve izleyen satır sayısı ROWS (bu örnekte bir satır) tanımlamak için yan tümcesi kullanılır.
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Sonuç kümesi aşağıdadır.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
283 1 1,573,012.94 2005 2,925,590.07
280 1 1,352,577.13 2005 2,929,139.33
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 2,458,535.62
Aşağıdaki örnekte, ROWS yan tümcesi UNBOUNDED PRECEDINGile belirtilir. Sonuç, pencerenin bölümün ilk satırında başlamasıdır.
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS UNBOUNDED PRECEDING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Sonuç kümesi aşağıdadır.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 559,697.56
287 NULL 519,905.93 2006 1,079,603.50
285 NULL 172,524.45 2007 1,252,127.95
283 1 1,573,012.94 2005 1,573,012.94
280 1 1,352,577.13 2005 2,925,590.07
284 1 1,576,562.20 2006 4,502,152.27
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 4,251,368.55
281 4 2,458,535.62 2005 6,709,904.17
Örnekler: Analiz Platformu Sistemi (PDW)
E. over yan tümcesini ROW_NUMBER işleviyle kullanma
Aşağıdaki örnek, atanan satış kotalarına göre satış temsilcileri için ROW_NUMBER döndürür.
SELECT ROW_NUMBER() OVER (ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName,
LastName,
CONVERT (VARCHAR (13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Kısmi bir sonuç kümesi aşağıdadır.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
F. OVER yan tümcesini toplama işlevleriyle kullanma
Aşağıdaki örneklerde toplama işlevleriyle OVER yan tümcesinin kullanılması gösterilmektedir. Bu örnekte, OVER yan tümcesini kullanmak alt sorgulardan daha verimlidir.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
AVG(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS AVG,
COUNT(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS COUNT,
MIN(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MIN,
MAX(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MAX
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Sonuç kümesi aşağıdadır.
OrderNumber Product Qty Total Avg Count Min Max
----------- ------- --- ----- --- ----- --- ---
SO43659 218 6 16 3 5 1 6
SO43659 220 4 16 3 5 1 6
SO43659 223 2 16 3 5 1 6
SO43659 229 3 16 3 5 1 6
SO43659 235 1 16 3 5 1 6
SO43664 229 1 2 1 2 1 1
SO43664 235 1 2 1 2 1 1
Aşağıdaki örnekte, hesaplanan değerde bir toplama işleviyle OVER yan tümcesinin kullanılması gösterilmektedir. Toplamlar SalesOrderNumber tarafından hesaplanır ve her SalesOrderNumberher satırı için toplam satış siparişinin yüzdesi hesaplanır.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey AS Product,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
CAST (1. * OrderQuantity / SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) * 100 AS DECIMAL (5, 2)) AS PctByProduct
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Bu sonuç kümesinin ilk başlangıcı aşağıdaki gibidir:
OrderNumber Product Qty Total PctByProduct
----------- ------- --- ----- ------------
SO43659 218 6 16 37.50
SO43659 220 4 16 25.00
SO43659 223 2 16 12.50
SO43659 229 2 16 18.75