SELECT - WINDOW - (Transact-SQL)

Artikel
10/20/2023

Berlaku untuk: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

Definisi jendela bernama dalam klausul WINDOW menentukan pemartisian dan pengurutan set baris sebelum fungsi jendela yang menggunakan jendela dalam klausa OVER diterapkan.

Catatan

Klausa WINDOW memerlukan tingkat kompatibilitas database 160 atau lebih tinggi. Jika tingkat kompatibilitas database Anda lebih rendah dari 160, SQL Server tidak dapat menjalankan kueri dengan klausa WINDOW.

Anda dapat memeriksa tingkat kompatibilitas dalam tampilan sys.databases atau di properti database. Anda bisa mengubah tingkat kompatibilitas database dengan perintah berikut:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160

Konvensi Sintaks Transact-SQL (Transact-SQL)

Sintaks

WINDOW window_name AS (
       [ reference_window_name ]   
       [ <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>

Argumen

window_name

Nama spesifikasi jendela yang ditentukan. Nama ini akan digunakan oleh fungsi jendela dalam klausa OVER untuk merujuk spesifikasi jendela. Nama jendela harus mengikuti aturan untuk pengidentifikasi.

reference_window_name

Nama jendela yang sedang dirujuk oleh jendela saat ini. Jendela yang dirujuk harus berada di antara jendela yang ditentukan dalam klausa WINDOW.

Argumen lainnya adalah:

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.

Untuk detail lebih spesifik tentang argumen, lihat Klausul OVER

Pernyataan umum

Lebih dari satu jendela bernama dapat didefinisikan dalam klausa WINDOW.

Komponen tambahan dapat ditambahkan ke jendela bernama dalam klausa OVER dengan menggunakan window_name diikuti dengan spesifikasi tambahan. Namun, properti yang ditentukan dalam klausa WINDOW tidak dapat ditentukan ulang dalam klausa OVER.

Saat kueri menggunakan beberapa jendela, satu jendela bernama dapat mereferensikan jendela bernama lainnya menggunakan window_name. Dalam hal ini, window_name yang dirujuk harus ditentukan dalam definisi jendela jendela referensi. Komponen jendela yang ditentukan dalam satu jendela tidak dapat didefinisikan ulang oleh jendela lain yang merujuknya.

Berdasarkan urutan di mana jendela didefinisikan dalam klausa jendela, referensi jendela maju dan mundur diizinkan. Dengan kata lain, jendela dapat menggunakan jendela lain yang didefinisikan dalam bahwa jendela tersebut <window_expression> adalah bagian dari, sebagai reference_window_name, terlepas dari urutan yang ditentukan. Referensi siklik dan menggunakan beberapa referensi jendela dalam satu jendela tidak diizinkan.

Cakupan window_name baru dari jendela yang ditentukan yang terkandung dalam <window_expression> terdiri dari definisi jendela apa pun yang merupakan bagian <window_expression>dari , bersama dengan klausul SELECT dari <query_specification> atau <SELECT statement> yang berisi klausa jendela. <window_expression> Jika terkandung dalam <query_specification> yang merupakan bagian dari <query_expression> yang merupakan kueri tabel sederhana, maka cakupan window_name baru juga menyertakan <order_by_expression>, jika ada, dari itu <query_expression>.

Pembatasan penggunaan spesifikasi jendela dalam klausul OVER dengan fungsi agregat dan analitik berdasarkan semantiknya berlaku untuk klausul WINDOW.

Contoh

J. Menentukan jendela yang ditentukan dalam klausa jendela

Contoh kueri berikut ini memperlihatkan menggunakan jendela bernama dalam klausa OVER.

ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO

USE AdventureWorks2022;
GO

SELECT ROW_NUMBER() OVER win 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
WINDOW win AS (PARTITION BY PostalCode ORDER BY SalesYTD DESC)
ORDER BY PostalCode;
GO

Kueri berikut setara dengan kueri di atas tanpa menggunakan klausa WINDOW.

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.

Nomor Baris	NamaBelakang	SalesYTD	Kode Pos
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. Menentukan satu jendela dalam beberapa klausa

Contoh berikut menunjukkan menentukan spesifikasi jendela dan menggunakannya beberapa kali dalam klausa OVER.

ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER win AS Total
    ,AVG(OrderQty) OVER win AS "Avg"
    ,COUNT(OrderQty) OVER win AS "Count"
    ,MIN(OrderQty) OVER win AS "Min"
    ,MAX(OrderQty) OVER win AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664)
WINDOW win AS (PARTITION BY SalesOrderID);
GO

Kueri berikut setara dengan kueri di atas tanpa menggunakan klausa WINDOW.

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	Hitung	Min	Maks
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

C. Menentukan spesifikasi umum dalam klausa jendela

Contoh ini menunjukkan mendefinisikan spesifikasi umum di jendela dan menggunakannya untuk menentukan spesifikasi tambahan dalam klausa OVER.

ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER win AS Total,
    AVG(OrderQty) OVER(win PARTITION BY SalesOrderID) AS Avg,
    COUNT(OrderQty) OVER(win ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%'
WINDOW win AS (ORDER BY SalesOrderID, ProductID);
GO

Kueri berikut setara dengan kueri di atas tanpa menggunakan klausa WINDOW.

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
    AVG(OrderQty) OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg,
    COUNT(OrderQty) OVER(ORDER BY SalesOrderID, ProductID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%';
GO

Berikut adalah hasil yang ditetapkan.

OrderNumber	ProductID	OrderQty	Total	Avg	Hitung
43659	711	4	4	4	2
43659	712	2	6	3	3
43659	714	3	9	3	4
43659	716	1	10	2	5
43664	714	1	11	1	6
43664	716	1	12	1	6

D. Referensi jendela maju dan mundur

Contoh ini menunjukkan penggunaan jendela bernama sebagai referensi maju dan mundur saat menentukan jendela baru dalam klausa WINDOW.

ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER win2 AS Total,
    AVG(OrderQty) OVER win1 AS Avg
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%'
WINDOW win1 AS (win3),
    win2 AS (ORDER BY SalesOrderID, ProductID),
    win3 AS (win2 PARTITION BY SalesOrderID);
GO

Kueri berikut setara dengan kueri di atas tanpa menggunakan klausa WINDOW.

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
    AVG(OrderQty) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%';
GO                                                                                     |

Berikut adalah hasil yang ditetapkan.

OrderNumber	ProductID	OrderQty	Total	Avg
43659	711	4	4	4
43659	712	2	6	3
43659	714	3	9	3
43659	716	1	10	2
43664	714	1	11	1
43664	716	1	12	1

SELECT - WINDOW - (Transact-SQL)

Sintaks

Argumen

window_name

reference_window_name

Pernyataan umum

Contoh

J. Menentukan jendela yang ditentukan dalam klausa jendela

B. Menentukan satu jendela dalam beberapa klausa

C. Menentukan spesifikasi umum dalam klausa jendela

D. Referensi jendela maju dan mundur

Baca juga

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: