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> }
Analiz Platformu Sistemi (PDW) 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>
belirtmezseniz PARTITION BY, işlev sorgu sonuç kümesinin tüm satırlarını tek bir bölüm olarak ele alır.
Yan ORDER BY tümce 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.
Bir sipariş belirtmezseniz, varsayılan düzendir
ASCve pencere işlevi bölümdeki tüm satırları kullanır.Bir sipariş belirtirseniz ancak veya belirtmezseniz
ROWS, isteğe bağlıROWSveyaRANGEbelirtimi kabul edebilen işlevler (örneğin,MINveyaMAX) varsayılan pencere çerçevesi olarak kullanılırRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.RANGE
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. Sütun adını veya diğer adını temsil etmek için tamsayı belirtemezsiniz.
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ğerlerdir.
SATIRLAR veya ARALIK
için geçerlidir: SQL Server 2012 (11.x) ve sonraki sürümler.
Bu seçenekler, bölümün içindeki başlangıç ve bitiş noktalarını belirterek bölüm içindeki satırları daha da sınırlar. Mantıksal ilişkilendirmeye veya fiziksel ilişkilendirmeye göre geçerli satıra göre bir satır aralığı belirtirsiniz. Yan tümcesini kullanarak fiziksel ilişkilendirme elde ROWS edebilirsiniz.
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ırın önüne ve dahil olduğu iki 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. Yalnızca pencere başlangıç noktası olarak belirtebilirsiniz UNBOUNDED PRECEDING .
<ÖNCEKİ> imzalanmamış değer belirtimi
Geçerli satırın önüne eklenecek satır veya değer sayısını belirtmek için ile <unsigned value specification> belirtin. 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. Hem başlangıç hem de bitiş noktası olarak belirtebilirsiniz CURRENT ROW .
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. Yalnızca pencere uç noktası olarak belirtebilirsiniz UNBOUNDED FOLLOWING . Ö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 ile <unsigned value specification> belirtin. Pencere başlangıç noktası olarak belirttiğinizde <unsigned value specification> FOLLOWING , bitiş noktası olmalıdır <unsigned value specification> FOLLOWING. Ö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 yan tümcesi olan FROM tek bir sorguda birden fazla pencere işlevi kullanabilirsiniz. Her işlevin OVER yan tümcesi bölümleme ve sıralama açısından farklılık gösterebilir.
belirtmezseniz PARTITION BY, işlev sorgu sonuç kümesinin tüm satırlarını tek bir grup olarak ele alır.
Important
için veya RANGE belirtir ROWS ve kullanırsanız <window frame preceding><window frame extent> (kısa söz dizimi), sorgu bu belirtimi pencere çerçevesi sınırı başlangıç noktası ve CURRENT ROW sınır bitiş noktası için kullanır. Örneğin, ROWS 5 PRECEDINGROWS BETWEEN 5 PRECEDING AND CURRENT ROWeşittir.
belirtmezseniz ORDER BY, tüm bölüm bir pencere çerçevesi için kullanılır. Bu kural yalnızca yan ORDER BY tümce gerektirmeyen işlevler için geçerlidir. belirtmezseniz veya RANGE belirtmezseniz ROWS ancak belirtirsenizORDER BYRANGE UNBOUNDED PRECEDING AND CURRENT ROW, pencere çerçevesi için varsayılan olarak kullanılır. Bu kural yalnızca isteğe bağlı ROWS veya RANGE belirtimi kabul eden işlevler için geçerlidir. Örneğin, derecelendirme işlevleri veya kabul ROWS edemediğinden, bu pencere çerçevesi mevcut olsa ORDER BY ve ROWS olmasa bile RANGE uygulanmaz.RANGE
Limitations
Yan tümcesini OVER toplamalarla DISTINCT kullanamazsınız.
veya <unsigned value specification> FOLLOWINGile <unsigned value specification> PRECEDING kullanamazsınızRANGE.
Yan tümcesi ve ROWS ve RANGE yan tümceleri için ORDER BY destek, yan tümcesiyle kullandığınız derecelendirme, toplama veya analiz işlevine OVER bağlıdır.
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 OVER her satır için satır numarasını görüntülemek üzere işleviyle yan tümcesinin ROW_NUMBER nasıl kullanılacağı 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 AdventureWorks2025;
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 AdventureWorks2025;
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, yan tümcesinin OVER hesaplanan bir değerde bir toplama işleviyle nasıl kullanılacağı gösterilmektedir.
USE AdventureWorks2025;
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. Sorgu verileri ölçütüne göre TerritoryID bölümler ve mantıksal olarak tarafından sıralar SalesYTD. Bu, AVG işlevinin satış yılına göre her bölge için hesaplandığını gösterir. için TerritoryID1, satış yılı için, o yılki 2022satışları olan iki satış elemanını temsil eden iki satır vardır. Bu iki satırın ortalama satışları hesaplanır ve ardından hesaplamaya 2023 yılın satışlarını temsil eden üçüncü satır eklenir.
USE AdventureWorks2025;
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 2021 559,697.56 559,697.56 559,697.56
287 NULL 2023 519,905.93 539,801.75 1,079,603.50
285 NULL 2024 172,524.45 417,375.98 1,252,127.95
283 1 2022 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2022 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2023 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2022 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2022 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2022 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2022 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 2021 559,697.56 559,697.56 559,697.56
275 2 2022 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2022 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2022 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2022 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2022 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2022 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2023 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2023 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2024 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 2021 1,079,603.50
287 NULL 519,905.93 2023 692,430.38
285 NULL 172,524.45 2024 172,524.45
283 1 1,573,012.94 2022 2,925,590.07
280 1 1,352,577.13 2022 2,929,139.33
284 1 1,576,562.20 2023 1,576,562.20
275 2 3,763,178.18 2022 3,763,178.18
277 3 3,189,418.37 2022 3,189,418.37
276 4 4,251,368.55 2022 6,709,904.17
281 4 2,458,535.62 2022 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 2021 559,697.56
287 NULL 519,905.93 2023 1,079,603.50
285 NULL 172,524.45 2024 1,252,127.95
283 1 1,573,012.94 2022 1,573,012.94
280 1 1,352,577.13 2022 2,925,590.07
284 1 1,576,562.20 2023 4,502,152.27
275 2 3,763,178.18 2022 3,763,178.18
277 3 3,189,418.37 2022 3,189,418.37
276 4 4,251,368.55 2022 4,251,368.55
281 4 2,458,535.62 2022 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