SELECT - Klausa OVER (Transact-SQL)
Berlaku untuk:
Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse di Microsoft Fabric
Klausa OVER menentukan pemartisian dan pengurutan set baris sebelum fungsi jendela terkait diterapkan. Artinya, OVER klausa menentukan jendela atau kumpulan baris yang ditentukan pengguna dalam kumpulan hasil kueri. Fungsi jendela kemudian menghitung nilai untuk setiap baris di jendela. Anda dapat menggunakan OVER klausul dengan fungsi untuk menghitung nilai agregat seperti rata-rata bergerak, agregat kumulatif, total berjalan, atau N teratas per hasil grup.
Sintaks
Sintaks untuk SQL Server, Azure SQL Database, dan Azure Synapse Analytics.
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> }
Sintaks untuk Gudang Data Paralel.
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Argumen
Fungsi jendela mungkin memiliki argumen berikut dalam klausulnya OVER :
PARTITION BY yang membagi hasil kueri yang diatur menjadi partisi.
ORDER BY yang menentukan urutan logis baris dalam setiap partisi tataan hasil.
ROWS atau RANGE yang membatasi baris dalam partisi dengan menentukan titik awal dan akhir dalam partisi. Ini memerlukan
ORDER BYargumen dan nilai default adalah dari awal partisi ke elemen saat ini jikaORDER BYargumen ditentukan.
Jika Anda tidak menentukan argumen apa pun, fungsi jendela diterapkan pada seluruh tataan hasil.
SELECT object_id,
MIN(object_id) OVER () AS [min],
MAX(object_id) OVER () AS [max]
FROM sys.objects;
| object_id | mnt | maks |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
PARTISI BERDASARKAN
Membagi hasil kueri yang diatur menjadi partisi. Fungsi jendela diterapkan ke setiap partisi secara terpisah dan komputasi dimulai ulang untuk setiap partisi.
PARTITION BY <value_expression>
Jika PARTITION BY tidak ditentukan, fungsi memperlakukan semua baris hasil kueri yang ditetapkan sebagai partisi tunggal.
Fungsi ini diterapkan pada semua baris dalam partisi jika Anda tidak menentukan ORDER BY klausa.
PARTISI MENURUT value_expression
Menentukan kolom tempat set baris dipartisi. value_expression hanya dapat merujuk ke kolom yang disediakan oleh FROM klausa. value_expression tidak dapat merujuk ke ekspresi atau alias dalam daftar pemilihan. value_expression dapat berupa ekspresi kolom, subkueri skalar, fungsi skalar, atau variabel yang ditentukan pengguna.
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 | jenis | mnt | maks |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
ORDER BY
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Menentukan urutan logis baris dalam setiap partisi tataan hasil. Artinya, ini menentukan urutan logis tempat penghitungan fungsi jendela dilakukan.
Jika tidak ditentukan, urutan defaultnya adalah
ASCdan fungsi jendela menggunakan semua baris dalam partisi.Jika ditentukan, dan
ROWSatauRANGEtidak ditentukan, maka default digunakan sebagai defaultRANGE UNBOUNDED PRECEDING AND CURRENT ROWuntuk bingkai jendela, oleh fungsi yang dapat menerima opsionalROWSatauRANGEspesifikasi (misalnya,minataumax).
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 | jenis | mnt | maks |
|---|---|---|---|
| 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
Menentukan kolom atau ekspresi yang akan diurutkan. order_by_expression hanya dapat merujuk ke kolom yang disediakan oleh FROM klausa. Bilangan bulat tidak dapat ditentukan untuk mewakili nama kolom atau alias.
MENYUSUN collation_name
Menentukan bahwa ORDER BY operasi harus dilakukan sesuai dengan kolase yang ditentukan dalam collation_name. collation_name bisa berupa nama kolase Windows atau nama kolase SQL. Untuk informasi selengkapnya, lihat Dukungan Collation dan Unicode. COLLATEhanya berlaku untuk kolom jenis karakter, varchar, nchar, dan nvarchar.
ASC | DESC
Menentukan bahwa nilai dalam kolom tertentu harus diurutkan dalam urutan naik atau turun. ASC adalah urutan sortir default. Nilai kosong diperlakukan sebagai nilai serendah mungkin.
BARIS atau RENTANG
Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru.
Selanjutnya membatasi baris dalam partisi dengan menentukan titik awal dan akhir dalam partisi. Ini menentukan rentang baris sehubungan dengan baris saat ini baik oleh asosiasi logis atau asosiasi fisik. Asosiasi fisik dicapai dengan menggunakan ROWS klausul .
Klausa ROWS membatasi baris dalam partisi dengan menentukan jumlah baris tetap sebelumnya atau mengikuti baris saat ini. Atau, RANGE klausa secara logis membatasi baris dalam partisi dengan menentukan rentang nilai sehubungan dengan nilai di baris saat ini. Baris sebelumnya dan berikut ditentukan berdasarkan urutan dalam ORDER BY klausa. Bingkai RANGE ... CURRENT ROW ... jendela menyertakan semua baris yang memiliki nilai yang sama dalam ORDER BY ekspresi sebagai baris saat ini. Misalnya, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW berarti bahwa jendela baris yang dioperasikan fungsi berukuran tiga baris, dimulai dengan 2 baris sebelum dan termasuk baris saat ini.
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 | sebelumnya | pusat | mengikuti |
|---|---|---|---|
| 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 atau RANGE mengharuskan Anda menentukan ORDER BY klausa. Jika ORDER BY berisi beberapa ekspresi pesanan, CURRENT ROW FOR RANGE pertimbangkan semua kolom dalam ORDER BY daftar saat menentukan baris saat ini.
UNBOUNDED PRECEDING
Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru.
Menentukan bahwa jendela dimulai pada baris pertama partisi. UNBOUNDED PRECEDING hanya dapat ditentukan sebagai titik awal jendela.
<spesifikasi> nilai yang tidak ditandatangani SEBELUMNYA
Ditentukan dengan <unsigned value specification> untuk menunjukkan jumlah baris atau nilai untuk mendahului baris saat ini. Spesifikasi ini tidak diizinkan untuk RANGE.
BARIS SAAT INI
Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru.
Menentukan bahwa jendela dimulai atau berakhir pada baris saat ini saat digunakan dengan ROWS atau nilai saat ini saat digunakan dengan RANGE. CURRENT ROW dapat ditentukan sebagai titik awal dan akhir.
DIANTARA DAN
Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru.
BETWEEN <window frame bound> AND <window frame bound>
Digunakan dengan atau ROWS RANGE untuk menentukan titik batas bawah (mulai) dan atas (akhir) jendela. <window frame bound> menentukan titik awal batas dan <window frame bound> menentukan titik akhir batas. Batas atas tidak boleh lebih kecil dari batas bawah.
UNBOUNDED FOLLOWING
Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru.
Menentukan bahwa jendela berakhir pada baris terakhir partisi. UNBOUNDED FOLLOWING hanya dapat ditentukan sebagai titik akhir jendela. Misalnya, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING mendefinisikan jendela yang dimulai dengan baris saat ini dan berakhir dengan baris terakhir partisi.
<spesifikasi> nilai yang tidak ditandatangani BERIKUT
Ditentukan dengan <unsigned value specification> untuk menunjukkan jumlah baris atau nilai untuk mengikuti baris saat ini. Ketika <unsigned value specification> FOLLOWING ditentukan sebagai titik awal jendela, titik akhir harus <unsigned value specification> FOLLOWING. Misalnya, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING menentukan jendela yang dimulai dengan baris kedua yang mengikuti baris saat ini dan berakhir dengan baris kesepuluh yang mengikuti baris saat ini. Spesifikasi ini tidak diizinkan untuk RANGE.
<bilangan bulat harfiah yang tidak ditandatangani>
Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru.
Bilangan bulat positif harfiah (termasuk 0) yang menentukan jumlah baris atau nilai yang akan didahului atau mengikuti baris atau nilai saat ini. Spesifikasi ini hanya berlaku untuk ROWS.
Keterangan
Lebih dari satu fungsi jendela dapat digunakan dalam satu kueri dengan satu FROM klausa. Klausa OVER untuk setiap fungsi dapat berbeda dalam pemartisian dan pengurutan.
Jika PARTITION BY tidak ditentukan, fungsi memperlakukan semua baris hasil kueri yang ditetapkan sebagai grup tunggal.
Penting
Jika ROWS atau RANGE ditentukan dan <window frame preceding> digunakan untuk <window frame extent> (sintaks pendek) maka spesifikasi ini digunakan untuk titik awal batas bingkai jendela dan CURRENT ROW digunakan untuk titik akhir batas. Misalnya, ROWS 5 PRECEDING sama dengan ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Jika ORDER BY tidak ditentukan, seluruh partisi digunakan untuk bingkai jendela. Ini hanya berlaku untuk fungsi yang tidak memerlukan ORDER BY klausa. Jika ROWS atau RANGE tidak ditentukan tetapi ORDER BY ditentukan, RANGE UNBOUNDED PRECEDING AND CURRENT ROW digunakan sebagai default untuk bingkai jendela. Ini hanya berlaku untuk fungsi yang memiliki dapat menerima opsional ROWS atau RANGE spesifikasi. Misalnya, fungsi peringkat tidak dapat menerima ROWS atau RANGE, oleh karena itu bingkai jendela ini tidak diterapkan meskipun ORDER BY ada dan ROWS atau RANGE tidak.
Batasan
Klausa OVER tidak dapat digunakan dengan DISTINCT agregasi.
RANGE tidak dapat digunakan dengan <unsigned value specification> PRECEDING atau <unsigned value specification> FOLLOWING.
Bergantung pada fungsi peringkat, agregat, atau analitik yang <ROWS and RANGE clause> digunakan dengan OVER klausa, <ORDER BY clause> dan/atau mungkin tidak didukung.
Contoh
Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.
J. Gunakan klausa OVER dengan fungsi ROW_NUMBER
Contoh berikut menunjukkan penggunaan OVER klausa dengan ROW_NUMBER fungsi untuk menampilkan nomor baris untuk setiap baris dalam partisi. Klausa ORDER BY yang ditentukan dalam OVER klausa mengurutkan baris di setiap partisi menurut kolom SalesYTD. Klausa ORDER BY dalam SELECT pernyataan menentukan urutan di mana seluruh tataan hasil kueri dikembalikan.
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
Berikut set hasilnya.
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. Menggunakan klausa OVER dengan fungsi agregat
Contoh berikut menggunakan klausa OVER dengan fungsi agregat di semua baris yang dikembalikan oleh kueri. Dalam contoh ini, menggunakan OVER klausa lebih efisien daripada menggunakan subkueri untuk mendapatkan nilai agregat.
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
Berikut set hasilnya.
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
Contoh berikut menunjukkan penggunaan OVER klausa dengan fungsi agregat dalam nilai terhitung.
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
Berikut set hasilnya. Agregat dihitung dengan SalesOrderID dan Percent by ProductID dihitung untuk setiap baris dari setiap SalesOrderID.
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. Menghasilkan rata-rata bergerak dan total kumulatif
Contoh berikut menggunakan AVG fungsi dan SUM dengan OVER klausul untuk memberikan rata-rata bergerak dan total kumulatif penjualan tahunan untuk setiap wilayah dalam Sales.SalesPerson tabel. Data dipartisi oleh TerritoryID dan secara logis diurutkan oleh SalesYTD. Ini berarti bahwa AVG fungsi dihitung untuk setiap wilayah berdasarkan tahun penjualan. Untuk TerritoryID dari 1, ada dua baris untuk tahun 2005 penjualan yang mewakili dua orang penjualan dengan penjualan tahun itu. Penjualan rata-rata untuk kedua baris ini dihitung, dan kemudian baris ketiga yang mewakili penjualan untuk tahun 2006 ini termasuk dalam komputasi.
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;
Berikut set hasilnya.
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
Dalam contoh ini, OVER klausul tidak menyertakan PARTITION BY. Ini berarti bahwa fungsi diterapkan ke semua baris yang dikembalikan oleh kueri. Klausa ORDER BY yang ditentukan dalam OVER klausul menentukan urutan logis tempat AVG fungsi diterapkan. Kueri mengembalikan rata-rata penjualan bergerak menurut tahun untuk semua wilayah penjualan yang ditentukan dalam WHERE klausul. Klausa ORDER BY yang ditentukan dalam SELECT pernyataan menentukan urutan di mana baris kueri ditampilkan.
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;
Berikut set hasilnya.
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. Tentukan klausa ROWS
Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru.
Contoh berikut menggunakan klausul ROWS untuk menentukan jendela di mana baris dihitung sebagai baris saat ini dan jumlah baris N yang mengikuti (satu baris dalam contoh ini).
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;
Berikut set hasilnya.
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
Dalam contoh berikut, ROWS klausul ditentukan dengan UNBOUNDED PRECEDING. Hasilnya adalah bahwa jendela dimulai pada baris pertama partisi.
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;
Berikut set hasilnya.
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
Contoh: Analytics Platform System (PDW)
E. Gunakan klausa OVER dengan fungsi ROW_NUMBER
Contoh berikut mengembalikan ROW_NUMBER untuk perwakilan penjualan berdasarkan kuota penjualan yang ditetapkan.
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;
Berikut adalah hasil parsial yang ditetapkan.
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. Menggunakan klausa OVER dengan fungsi agregat
Contoh berikut menunjukkan penggunaan OVER klausa dengan fungsi agregat. Dalam contoh ini, menggunakan OVER klausa lebih efisien daripada menggunakan subkueri.
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;
Berikut set hasilnya.
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
Contoh berikut menunjukkan penggunaan OVER klausa dengan fungsi agregat dalam nilai terhitung. Agregat dihitung dengan SalesOrderNumber dan persentase dari total pesanan penjualan dihitung untuk setiap baris masing-masing SalesOrderNumber.
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;
Awal pertama dari kumpulan hasil ini adalah sebagai berikut:
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