SELECT - Klausa WINDOW (Transact-SQL)

Artikel
07/27/2024

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

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

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

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

ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 160;

Konvensi sintaks 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 digunakan oleh fungsi jendela dalam OVER klausul untuk merujuk ke 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 WINDOW klausa.

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 klausa OVER

Keterangan

Lebih dari satu jendela bernama dapat didefinisikan dalam WINDOW klausa.

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

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 ditentukan ulang oleh jendela lain yang mereferensikannya.

Berdasarkan urutan di mana jendela didefinisikan dalam klausa jendela, referensi jendela maju dan mundur diizinkan. Dengan kata lain, jendela mungkin menggunakan jendela lain yang ditentukan dalam ekspresi jendela yang merupakan bagian dari, seperti reference_window_name, terlepas dari urutan pendefinisiannya. Referensi siklik dan menggunakan beberapa referensi jendela dalam satu jendela tidak diizinkan.

Cakupan window_name baru jendela yang ditentukan yang terkandung dalam ekspresi jendela, terdiri dari definisi jendela apa pun yang merupakan bagian dari ekspresi jendela, bersama dengan SELECT klausul spesifikasi atau SELECT pernyataan kueri yang berisi klausa jendela. Jika ekspresi jendela terkandung dalam spesifikasi kueri yang merupakan bagian dari ekspresi kueri, yang merupakan kueri tabel dasar, maka cakupan window_name baru juga menyertakan ekspresi, jika ada, dari ekspresi kueri tersebutORDER BY.

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

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. Tentukan jendela yang ditentukan dalam klausa jendela

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

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 sebelumnya tanpa menggunakan WINDOW klausa.

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	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. Tentukan satu jendela dalam beberapa klausa OVER

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 sebelumnya tanpa menggunakan WINDOW klausa.

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. Tentukan spesifikasi umum dalam klausa jendela

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

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 sebelumnya tanpa menggunakan WINDOW klausa.

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	Jml	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 WINDOW klausa.

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 sebelumnya tanpa menggunakan WINDOW klausa.

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	Jml	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

Bagikan melalui

SELECT - Klausa WINDOW (Transact-SQL)

Sintaks

Argumen

window_name

reference_window_name

Keterangan

Contoh

J. Tentukan jendela yang ditentukan dalam klausa jendela

B. Tentukan satu jendela dalam beberapa klausa OVER

C. Tentukan spesifikasi umum dalam klausa jendela

D. Referensi jendela maju dan mundur

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

SELECT - Klausa WINDOW (Transact-SQL)

Sintaks

Argumen

window_name

reference_window_name

Keterangan

Contoh

J. Tentukan jendela yang ditentukan dalam klausa jendela

B. Tentukan satu jendela dalam beberapa klausa OVER

C. Tentukan spesifikasi umum dalam klausa jendela

D. Referensi jendela maju dan mundur

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: