SELECT - WINDOW - (Transact-SQL)

Articolo
05/23/2023

Si applica a: sql server 2022 (16.x) database SQL di Azure Istanza gestita di SQL di Azure

La definizione di finestra denominata nella clausola WINDOW determina il partizionamento e l'ordinamento di un set di righe prima che venga applicata la funzione finestra che usa la finestra nella clausola OVER.

Nota

La clausola WINDOW richiede il livello di compatibilità del database pari a 160 o superiore. Se il livello di compatibilità del database è inferiore a 160, SQL Server non potrà eseguire query con la clausola WINDOW.

È possibile controllare il livello di compatibilità nella vista sys.databases o nelle proprietà del database. È possibile modificare il livello di compatibilità di un database con il comando seguente:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160

Convenzioni della sintassi Transact-SQL (Transact-SQL)

Sintassi

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>

Argomenti

window_name

Nome della specifica della finestra definita. Questo nome verrà usato dalle funzioni finestra nella clausola OVER per fare riferimento alla specifica della finestra. I nomi di finestra devono essere conformi alle regole per gli identificatori.

reference_window_name

Nome della finestra a cui fa riferimento la finestra corrente. La finestra a cui si fa riferimento deve essere inclusa tra le finestre definite nella clausola WINDOW.

Gli altri argomenti sono:

PARTITION BY, che suddivide il set dei risultati della query in partizioni.
ORDER BY, che definisce l'ordine logico delle righe all'interno di ogni partizione del set di risultati.
ROWS/RANGE, che limita le righe all'interno della partizione specificando i punti iniziali e finali.

Per informazioni più specifiche sugli argomenti, vedere la Clausola OVER

Osservazioni generali

È possibile definire più finestre denominate nella clausola WINDOW.

È possibile aggiungere componenti aggiuntivi a una finestra denominata nella clausola OVER usando window_name seguito dalle specifiche aggiuntive. Le proprietà specificate nella clausola WINDOW non possono tuttavia essere ridefinite nella clausola OVER.

Quando una query usa più finestre, una finestra denominata può fare riferimento a un'altra finestra denominata usando window_name. In questo caso, l'argomento window_name a cui si fa riferimento deve essere specificato nella definizione della finestra di riferimento. Non è possibile ridefinire un componente finestra definito in una finestra facendo riferimento a un'altra finestra.

In base all'ordine in cui le finestre sono definite nella clausola WINDOW, sono consentiti riferimenti in avanti e all'indietro a finestre. In altre parole, una finestra può usare qualsiasi altra finestra definita in <window_expression> di cui fa parte, come reference_window_name, indipendentemente dall'ordine di cui sono definiti. I riferimenti ciclici e l'uso di più riferimenti di finestra in una singola finestra non sono consentiti.

L'ambito del nuovo argomento window_name di una finestra definita contenuta in <window_expression> è costituito da eventuali definizioni di finestra che fanno parte di <window_expression>, insieme alla clausola SELECT di <query_specification> o <SELECT statement> contenente la clausola WINDOW. Se <window_expression> è contenuto in <query_specification> che fa parte di <query_expression>, ovvero una semplice query di tabella, l'ambito del nuovo window_name include anche <order_by_expression>, se presente, di tale <query_expression>.

Le restrizioni per l'utilizzo delle specifiche delle finestre nella clausola OVER con le funzioni di aggregazione e analisi basate sulla rispettiva semantica sono applicabili alla clausola WINDOW.

Esempi

R. Specifica di una finestra definita nella clausola WINDOW

La query di esempio seguente mostra l'uso di una finestra denominata nella clausola 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

La query seguente è equivalente alla query precedente senza l'uso della clausola 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

Questo è il set di risultati.

Numero di riga	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. Specifica di una singola finestra in più clausole OVER

L'esempio seguente illustra la definizione di una specifica della finestra e l'uso ripetuto di tale finestra in una clausola 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

La query seguente è equivalente alla query precedente senza l'uso della clausola 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

Questo è il set di risultati.

Salesorderid	ProductID	Orderqty	Totale	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

C. Definizione della specifica comune nella clausola WINDOW

Questo esempio illustra la definizione di una specifica comune in una finestra e il rispettivo uso per definire specifiche aggiuntive nella clausola 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

La query seguente è equivalente alla query precedente senza l'uso della clausola 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

Questo è il set di risultati.

OrderNumber	ProductID	Orderqty	Totale	Avg	Count
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. Riferimenti in avanti e all'indietro alla finestra

Questo esempio illustra l'uso di finestre denominate come riferimenti in avanti e all'indietro durante la definizione di una nuova finestra nella clausola 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

La query seguente è equivalente alla query precedente senza l'uso della clausola 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                                                                                     |

Questo è il set di risultati.

OrderNumber	ProductID	Orderqty	Totale	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)

Sintassi

Argomenti

window_name

reference_window_name

Osservazioni generali

Esempi

R. Specifica di una finestra definita nella clausola WINDOW

B. Specifica di una singola finestra in più clausole OVER

C. Definizione della specifica comune nella clausola WINDOW

D. Riferimenti in avanti e all'indietro alla finestra

Vedi anche

Commenti e suggerimenti

Commenti e suggerimenti

Risorse aggiuntive