SELECT - KLAUSUL OVER (Transact-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)Titik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft Fabric
Menentukan pemartisian dan pengurutan set baris sebelum fungsi jendela terkait diterapkan. Artinya, klausa OVER menentukan kumpulan baris yang ditentukan jendela atau pengguna dalam kumpulan hasil kueri. Fungsi jendela kemudian menghitung nilai untuk setiap baris di jendela. Anda dapat menggunakan klausa OVER dengan fungsi untuk menghitung nilai agregat seperti rata-rata bergerak, agregat kumulatif, total yang berjalan, atau hasil N per grup teratas.
Sintaksis
-- Syntax for SQL Server, Azure SQL Database, and 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> }
-- Syntax for Parallel Data Warehouse
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
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/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 akan diterapkan pada seluruh tataan hasil.
select
object_id
, [min] = min(object_id) over()
, [max] = max(object_id) over()
from sys.objects
| object_id | min | 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 akan 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 klausa FROM. value_expression tidak dapat merujuk ke ekspresi atau alias dalam daftar pilih. value_expression dapat berupa ekspresi kolom, subkueri skalar, fungsi skalar, atau variabel yang ditentukan pengguna.
select
object_id, type
, [min] = min(object_id) over(partition by type)
, [max] = max(object_id) over(partition by type)
from sys.objects
| object_id | jenis | min | 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 akan menggunakan semua baris dalam partisi. - Jika ditentukan, dan ROWS/RANGE tidak ditentukan, maka default
RANGE UNBOUNDED PRECEDING AND CURRENT ROWdigunakan sebagai default untuk bingkai jendela oleh fungsi yang dapat menerima spesifikasi ROWS/RANGE opsional (misalnyaminataumax).
select
object_id, type
, [min] = min(object_id) over(partition by type order by object_id)
, [max] = max(object_id) over(partition by type order by object_id)
from sys.objects
| object_id | jenis | min | 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 klausa FROM. Bilangan bulat tidak dapat ditentukan untuk mewakili nama kolom atau alias.
MENYUSUN collation_name
Menentukan bahwa operasi ORDER BY 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 Kolate dan Unicode. COLLATE hanya 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 pengurutan default. Nilai kosong diperlakukan sebagai nilai serendah mungkin.
BARIS atau RENTANG
Berlaku untuk: SQL Server 2012 (11.x) dan yang lebih baru.
Selanjutnya membatasi baris dalam partisi dengan menentukan titik awal dan akhir dalam partisi. Ini dilakukan dengan menentukan rentang baris sehubungan dengan baris saat ini baik oleh asosiasi logis atau asosiasi fisik. Asosiasi fisik dicapai dengan menggunakan klausa ROWS.
Klausa ROWS membatasi baris dalam partisi dengan menentukan jumlah baris tetap sebelum atau setelah baris saat ini. Atau, klausa RANGE 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 klausul ORDER BY. Bingkai jendela "RANGE ... BARIS SAAT INI ..." menyertakan semua baris yang memiliki nilai yang sama dalam ekspresi ORDER BY 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 sebelumnya hingga dan menyertakan baris saat ini.
select
object_id
, [preceding] = count(*) over(order by object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
, [central] = count(*) over(order by object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING )
, [following] = count(*) over(order by object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED 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 |
Catatan
ROWS atau RANGE mengharuskan klausul ORDER BY ditentukan. Jika ORDER BY berisi beberapa ekspresi pesanan, CURRENT ROW FOR RANGE mempertimbangkan semua kolom dalam daftar ORDER BY saat menentukan baris saat ini.
UNBOUNDED PRECEDING
Berlaku untuk: SQL Server 2012 (11.x) dan yang lebih baru.
Menentukan bahwa jendela dimulai pada baris pertama partisi. PRECEDING TIDAK TERBATAS hanya dapat ditentukan sebagai titik awal jendela.
<spesifikasi> nilai yang tidak ditandatangani SEBELUMNYA
Ditentukan dengan <spesifikasi>nilai yang tidak ditandatangani untuk menunjukkan jumlah baris atau nilai untuk mendahului baris saat ini. Spesifikasi ini tidak diperbolehkan untuk RANGE.
BARIS SAAT INI
Berlaku untuk: SQL Server 2012 (11.x) dan yang lebih baru.
Menentukan bahwa jendela dimulai atau berakhir pada baris saat ini saat digunakan dengan BARIS atau nilai saat ini saat digunakan dengan RANGE. BARIS LANCAR dapat ditentukan sebagai titik awal dan titik akhir.
DIANTARA DAN
Berlaku untuk: SQL Server 2012 (11.x) dan yang lebih baru.
BETWEEN <window frame bound > AND <window frame bound >
Digunakan dengan BARIS atau RENTANG untuk menentukan titik batas bawah (awal) dan atas (akhir) jendela. <bingkai jendela terikat> mendefinisikan titik awal batas dan <bingkai jendela yang terikat> menentukan titik akhir batas. Batas atas tidak boleh lebih kecil dari batas bawah.
UNBOUNDED FOLLOWING
Berlaku untuk: SQL Server 2012 (11.x) dan yang lebih baru.
Menentukan bahwa jendela berakhir pada baris terakhir partisi. UNBOUNDED FOLLOWING hanya dapat ditentukan sebagai titik akhir jendela. Misalnya RENTANG ANTARA BARIS SAAT INI DAN UNBOUNDED FOLLOWING menentukan jendela yang dimulai dengan baris saat ini dan berakhir dengan baris terakhir partisi.
<spesifikasi> nilai yang tidak ditandatangani BERIKUT
Ditentukan dengan <spesifikasi> nilai yang tidak ditandatangani untuk menunjukkan jumlah baris atau nilai untuk mengikuti baris saat ini. Ketika <spesifikasi> nilai yang tidak ditandatangani BERIKUT ditentukan sebagai titik awal jendela, titik akhir harus tidak <ditandatangani spesifikasi>nilai BERIKUT. Misalnya, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING menentukan jendela yang dimulai dengan baris kedua yang mengikuti baris saat ini dan diakhir dengan baris kesepuluh yang mengikuti baris saat ini. Spesifikasi ini tidak diperbolehkan untuk RANGE.
bilangan bulat harfiah yang tidak ditandatangani
Berlaku untuk: SQL Server 2012 (11.x) dan yang lebih baru.
Adalah 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 klausa FROM. 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/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 "BARIS 5 SEBELUMNYA" sama dengan "BARIS ANTARA 5 BARIS SEBELUMNYA DAN BARIS SAAT INI".
Catatan
Jika ORDER BY tidak ditentukan seluruh partisi digunakan untuk bingkai jendela. Ini hanya berlaku untuk fungsi yang tidak memerlukan klausa ORDER BY. Jika ROWS/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 spesifikasi ROWS/RANGE opsional. Misalnya, fungsi peringkat tidak dapat menerima ROWS/RANGE, oleh karena itu bingkai jendela ini tidak diterapkan meskipun ORDER BY ada dan ROWS/RANGE tidak.
Batasan dan Pembatasan
Klausa OVER tidak dapat digunakan dengan agregasi DISTINCT.
RANGE tidak dapat digunakan dengan <spesifikasi> nilai yang tidak ditandatangani SEBELUMNYA atau <spesifikasi> nilai yang tidak ditandatangani MENGIKUTI.
Bergantung pada fungsi peringkat, agregat, atau analitik yang digunakan dengan klausa OVER, <klausa> ORDER BY dan/atau <klausul> ROWS dan RANGE mungkin tidak didukung.
Contoh
J. Menggunakan klausa OVER dengan fungsi ROW_NUMBER
Contoh berikut menunjukkan penggunaan klausa OVER dengan fungsi ROW_NUMBER untuk menampilkan nomor baris untuk setiap baris dalam partisi. Klausa ORDER BY yang ditentukan dalam klausa OVER mengurutkan baris di setiap partisi menurut kolom SalesYTD. Klausa ORDER BY dalam pernyataan SELECT menentukan urutan pengembalian seluruh kueri.
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 adalah hasil yang ditetapkan.
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 adalah hasil yang ditetapkan.
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 adalah hasil yang ditetapkan. Perhatikan bahwa agregat dihitung oleh SalesOrderID dan Percent by ProductID dihitung untuk setiap baris masing-masing 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
(20 row(s) affected)
C. Menghasilkan rata-rata bergerak dan total kumulatif
Contoh berikut menggunakan fungsi AVG dan SUM dengan klausul OVER untuk memberikan total penjualan tahunan rata-rata dan kumulatif yang bergerak untuk setiap wilayah dalam Sales.SalesPerson tabel. Data dipartisi oleh TerritoryID dan secara logis diurutkan oleh SalesYTD. Ini berarti bahwa fungsi AVG dihitung untuk setiap wilayah berdasarkan tahun penjualan. Perhatikan bahwa untuk TerritoryID 1, ada dua baris untuk penjualan tahun 2005 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 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 adalah hasil yang ditetapkan.
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
(10 row(s) affected)
Dalam contoh ini, klausa OVER tidak menyertakan PARTITION BY. Ini berarti bahwa fungsi akan diterapkan ke semua baris yang dikembalikan oleh kueri. Klausa ORDER BY yang ditentukan dalam klausul OVER menentukan urutan logis tempat fungsi AVG diterapkan. Kueri mengembalikan rata-rata penjualan bergerak menurut tahun untuk semua wilayah penjualan yang ditentukan dalam klausul WHERE. Klausa ORDER BY yang ditentukan dalam pernyataan SELECT 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 adalah hasil yang ditetapkan.
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
(10 row(s) affected)
D. Menentukan klausul ROWS
Berlaku untuk: SQL Server 2012 (11.x) dan 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 (1 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 adalah hasil yang ditetapkan.
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, klausul ROWS ditentukan dengan PRECEDING TIDAK TERBATAS. 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 adalah hasil yang ditetapkan.
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. Menggunakan klausa OVER dengan fungsi ROW_NUMBER
Contoh berikut mengembalikan ROW_NUMBER untuk perwakilan penjualan berdasarkan kuota penjualan yang ditetapkan.
-- Uses AdventureWorks
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 kumpulan hasil parsial.
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 klausa OVER dengan fungsi agregat. Dalam contoh ini, menggunakan klausa OVER lebih efisien daripada menggunakan subkueri.
-- Uses AdventureWorks
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 adalah hasil yang ditetapkan.
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 klausa OVER dengan fungsi agregat dalam nilai terhitung. Perhatikan bahwa agregat dihitung oleh SalesOrderNumber dan persentase dari total pesanan penjualan dihitung untuk setiap baris masing-masing SalesOrderNumber.
-- Uses AdventureWorksDW2022
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:
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
Lihat Juga
Fungsi Agregat (T-SQL)
Fungsi Analitik (Transact-SQL)
Posting blog yang sangat baik tentang fungsi jendela dan OVER, di sqlmag.com, oleh Itzik Ben-Gan
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk