Condividi tramite

Clausola SELECT - WINDOW (Transact-SQL)

  • Articolo

Si applica a: SQL Server 2022 (16.x) Database Azure SQL Istanza gestita di SQL di Azure

La definizione di finestra denominata nella WINDOW clausola determina il partizionamento e l'ordinamento di un set di righe prima della funzione window, che usa la finestra in una OVER clausola .

La WINDOW clausola richiede il livello 160 di compatibilità del database o superiore. Se il livello di compatibilità del database è inferiore a 160, il motore di database non può eseguire query con la WINDOW clausola .

È possibile controllare il livello di compatibilità nella sys.databases vista 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 relative alla sintassi 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 viene usato dalle funzioni della finestra nella OVER clausola 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 tra le finestre definite nella WINDOW clausola .

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:

È possibile definire più finestre denominate nella WINDOW clausola .

È possibile aggiungere altri componenti a una finestra denominata nella OVER clausola usando il window_name seguito dalle specifiche aggiuntive. Tuttavia, le proprietà specificate nella WINDOW clausola non possono essere ridefinite nella OVER clausola .

Quando una query usa più finestre, una finestra denominata può fare riferimento a un'altra finestra denominata usando il window_name. In questo caso, il window_name a cui viene fatto riferimento deve essere specificato nella definizione della finestra di riferimento. Un componente finestra definito in una finestra non può essere ridefinito 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 nell'espressione della finestra di cui fa parte, come reference_window_name, indipendentemente dall'ordine in cui sono definite. I riferimenti ciclici e l'uso di più riferimenti di finestra in una singola finestra non sono consentiti.

L'ambito del nuovo window_name di una finestra definita contenuta in un'espressione di finestra è costituito da tutte le definizioni di finestra che fanno parte dell'espressione finestra, insieme alla SELECT clausola della specifica SELECT o dell'istruzione della query che contiene la clausola window. Se l'espressione di finestra è contenuta in una specifica di query che fa parte dell'espressione di query, ovvero una query di tabella di base, l'ambito del nuovo window_name include anche l'espressione ORDER BY , se presente, di tale espressione di query.

Le restrizioni per l'utilizzo delle specifiche di finestra nella OVER clausola con le funzioni di aggregazione e analisi in base alla relativa semantica sono applicabili alla WINDOW clausola .

Esempi

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

R. Specificare una finestra definita nella clausola window

Nella query di esempio seguente viene utilizzata una finestra denominata nella OVER clausola .

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 è l'equivalente della query precedente senza usare la WINDOW clausola .

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. Specificare una singola finestra in più clausole OVER

Nell'esempio seguente viene illustrata la definizione di una specifica di finestra e l'uso più volte in una OVER clausola .

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 è l'equivalente della query precedente senza usare la WINDOW clausola .

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 Conteggio 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. Definire la specifica comune nella clausola window

In questo esempio viene illustrata la definizione di una specifica comune in una finestra e l'uso per definire specifiche aggiuntive nella OVER clausola .

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 è l'equivalente della query precedente senza usare la WINDOW clausola .

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 Qtà Totale Avg Conteggio
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

In questo esempio viene illustrato l'uso di finestre denominate come riferimenti avanti e indietro durante la definizione di una nuova finestra nella WINDOW clausola .

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 è l'equivalente della query precedente senza usare la WINDOW clausola .

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 Qtà 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

Contenuto correlato