Clausola SELECT - OVER (Transact-SQL)

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics AnalyticsPlatform System (PDW)SQL analytics endpoint in Microsoft FabricWarehouse in Microsoft Fabric

Determina il partizionamento e l'ordinamento di un set di righe prima dell'applicazione della funzione finestra associata. In altre parole, la clausola OVER definisce una finestra o un set di righe specificato dall'utente all'interno di un set di risultati della query. Una funzione finestra calcola quindi un valore per ogni riga della finestra. È possibile utilizzare la clausola OVER con le funzioni per calcolare i valori aggregati, ad esempio medie mobili, aggregazioni cumulative, totali parziali o i primi N risultati per gruppo.

Convenzioni di sintassi Transact-SQL

Sintassi

-- 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 ] )  

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Argomenti

Nella clausola OVER delle funzioni finestra possono essere presenti gli argomenti seguenti:

  • 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. Richiede l’uso dell’argomento ORDER BYe il valore predefinito è dall'inizio della partizione fino all'elemento corrente se viene specificato l'argomento ORDER BY.

Se non si specifica alcun argomento, le funzioni finestra vengono applicate all'intero set di risultati.

select 
      object_id
    , [min]    = min(object_id) over()
    , [max]    = max(object_id) over()
from sys.objects
object_id min max
3 3 2139154666
5 3 2139154666
... ... ...
2123154609 3 2139154666
2139154666 3 2139154666

PARTITION BY

Suddivide il set di risultati della query in partizioni. La funzione finestra viene applicata a ogni singola partizione e il calcolo viene riavviato per ogni partizione.

PARTITION BY *value_expression* 

Se PARTITION BY non viene specificato, la funzione considera tutte le righe del set di risultati della query come un’unica partizione. Se non si specifica la clausola ORDER BY, la funzione viene applicata a tutte le righe della partizione.

PARTITION BY value_expression

Specifica la colonna in base alla quale viene partizionato il set di righe. value_expression può fare riferimento solo alle colonne rese disponibili dalla clausola FROM. value_expression non può fare riferimento a espressioni o alias nell'elenco di selezione. value_expression può essere un'espressione di colonna, una sottoquery scalare, una funzione scalare o una variabile definita dall'utente.

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 type min max
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]  

Definisce l'ordine logico delle righe all'interno di ogni partizione del set di risultati. In altre parole, specifica l'ordine logico in cui viene eseguito il calcolo della funzione finestra.

  • Se non viene specificato, l'ordine predefinito è ASC e la funzione finestra utilizza tutte le righe della partizione.
  • Se viene specificato, e non viene specificato un oggetto ROWS/RANGE, il valore predefinito RANGE UNBOUNDED PRECEDING AND CURRENT ROW viene usato come impostazione predefinita per la cornice della finestra dalle funzioni che possono accettare specifiche facoltative di ROWS/RANGE, ad esempio min o max.
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 type min max
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
Specifica una colonna o un'espressione in base alla quale eseguire l'ordinamento. order_by_expression può fare riferimento solo alle colonne rese disponibili dalla clausola FROM. Non è possibile specificare un numero intero per rappresentare un alias o un nome di colonna.

COLLATE collation_name
Specifica che l'operazione ORDER BY deve essere eseguita in base alle regole di confronto definite in collation_name. In collation_name è possibile usare nomi di regole di confronto di Windows o SQL. Per ulteriori informazioni, vedi Supporto per Unicode e regole di confronto. COLLATE può essere applicato solo alle colonne di tipo char, varchar, nchar e nvarchar.

ASC | DESC
Specifica che i valori nella colonna specificata devono essere ordinati in ordine crescente o decrescente. ASC è l'ordinamento predefinito. I valori Null vengono considerati i valori in assoluto più piccoli.

ROWS o RANGE

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Limita ulteriormente le righe all'interno della partizione specificando i punti iniziali e finali. A tale scopo, è necessario specificare un intervallo di righe rispetto alla riga corrente in base all'associazione logica o all'associazione fisica. L'associazione fisica viene eseguita mediante la clausola ROWS.

La clausola ROWS limita le righe all'interno di una partizione specificando un numero fisso di righe precedenti o successive alla riga corrente. In alternativa, la clausola RANGE limita logicamente le righe all'interno di una partizione specificando un intervallo di valori rispetto al valore nella riga corrente. Le righe precedenti e successive vengono definite in base all'ordinamento nella clausola ORDER BY. Cornice della finestra "RANGE ... RIGA CORRENTE ..." include tutte le righe con gli stessi valori nell'espressione ORDER BY della riga corrente. Ad esempio, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa che la finestra di righe a cui viene applicata la funzione è di tre righe, a partire dalle 2 righe precedenti fino alla riga corrente inclusa.

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 preceding central following
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

Nota

ROWS o RANGE richiede che venga specificata la clausola ORDER BY. Se ORDER BY contiene più espressioni di ordinamento, CURRENT ROW FOR RANGE considera tutte le colonne nell'elenco ORDER BY per la determinazione della riga corrente.

UNBOUNDED PRECEDING

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Specifica che la finestra inizia in corrispondenza della prima riga della partizione. UNBOUNDED PRECEDING può essere specificato solo come punto iniziale della finestra.

<unsigned value specification> PRECEDING
Specificato con <unsigned value specification> per indicare il numero di righe o valori che deve precedere la riga corrente. Questa specifica non è consentita per RANGE.

CURRENT ROW

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Specifica che la finestra inizia o termina in corrispondenza della riga corrente quando viene utilizzato con ROWS o in corrispondenza del valore corrente quando viene utilizzato con RANGE. CURRENT ROW può essere specificato sia come punto iniziale che come punto finale.

BETWEEN AND

Si applica a: SQL Server 2012 (11.x) e versioni successive.

BETWEEN <window frame bound > AND <window frame bound >  

Utilizzato con ROWS o RANGE per specificare i punti limite inferiore (punto iniziale) e superiore (punto finale) della finestra. Il valore <window frame bound> definisce il punto limite iniziale e il valore <window frame bound> definisce il punto limite finale. Il limite superiore non può essere minore del limite inferiore.

UNBOUNDED FOLLOWING

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Specifica che la finestra termina in corrispondenza dell'ultima riga della partizione. UNBOUNDED FOLLOWING può essere specificato solo come punto finale della finestra. Ad esempio, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING definisce una finestra che inizia in corrispondenza della riga corrente e termina in corrispondenza dell'ultima riga della partizione.

<unsigned value specification> FOLLOWING
Specificato con <unsigned value specification> per indicare il numero di righe o valori che deve seguire la riga corrente. Quando <unsigned value specification> FOLLOWING viene specificato come punto iniziale della finestra, il punto finale deve essere <unsigned value specification> FOLLOWING. Ad esempio, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING definisce una finestra che inizia in corrispondenza della seconda riga successiva alla riga corrente e termina in corrispondenza della decima riga successiva alla riga corrente. Questa specifica non è consentita per RANGE.

valore letterale integer senza segno
Si applica a: SQL Server 2012 (11.x) e versioni successive.

Valore letterale integer positivo (incluso 0) che specifica il numero di righe o valori che deve precedere o seguire la riga o il valore corrente. Questa specifica è valida solo per ROWS.

Osservazioni:

È possibile utilizzare più funzioni finestra in una singola query con una singola clausola FROM. La clausola OVER di ogni funzione può specificare un partizionamento e un ordinamento diversi.

Se PARTITION BY non viene specificato, la funzione considera tutte le righe del set di risultati della query come un unico gruppo.

Importante

Se viene specificato ROWS/RANGE e <window frame preceding> viene usato come <window frame extent> (sintassi breve), questa specifica verrà usata per il punto limite iniziale della cornice della finestra e CURRENT ROW verrà usato per il punto limite finale. Ad esempio, "ROWS 5 PRECEDING" corrisponde a "ROWS BETWEEN 5 PRECEDING AND CURRENT ROW".

Nota

Se ORDER BY non viene specificato, viene utilizzata l'intera partizione per una cornice di finestra. Si applica solo alle funzioni che non richiedono la clausola ORDER BY. Se ROWS/RANGE non viene specificato, ma si specifica ORDER BY, come valore predefinito per la cornice della finestra viene utilizzato RANGE UNBOUNDED PRECEDING AND CURRENT ROW. Si applica solo alle funzioni che possono accettare la specifica facoltativa di ROWS/RANGE. Le funzioni di rango, ad esempio, non possono accettare ROWS/RANGE, pertanto questa cornice della finestra non viene applicata anche se ORDER BY è presente e ROWS/RANGE non lo è.

Limitazioni e restrizioni

La clausola OVER non può essere utilizzata con le aggregazioni DISTINCT.

RANGE non può essere usato con <unsigned value specification> PRECEDING o <unsigned value specification> FOLLOWING.

A seconda della funzione analitica, di aggregazione o di rango usata con la clausola OVER, è possibile che la <clausola ORDER BY> e/o la <clausola ROWS e RANGE> non siano supportate.

Esempi

R. Utilizzo della clausola OVER con la funzione ROW_NUMBER

Nell'esempio seguente viene illustrato l'utilizzo della clausola OVER con la funzione ROW_NUMBER per visualizzare un numero di riga per ogni riga all'interno di una partizione. La clausola ORDER BY specificata nella clausola OVER ordina le righe in ogni partizione in base alla colonna SalesYTD. La clausola ORDER BY nell'istruzione SELECT determina l'ordine in cui viene restituito l'intero set di risultati della query.

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.

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. Utilizzo della clausola OVER con funzioni di aggregazione

Nell'esempio seguente viene utilizzata la clausola OVER con funzioni di aggregazione su tutte le righe restituite dalla query. Nell'esempio l'utilizzo della clausola OVER è più efficace rispetto all'utilizzo di sottoquery per derivare i valori di aggregazione.

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

Nell'esempio seguente viene illustrato l'utilizzo della clausola OVER con una funzione di aggregazione in un valore calcolato.

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  

Questo è il set di risultati. Si noti che le aggregazioni vengono calcolate da SalesOrderID e vengono Percent by ProductID calcolate per ogni riga di ogni 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. Generazione di una media mobile e di un totale cumulativo

Nell'esempio seguente vengono utilizzate le funzioni AVG e SUM con la clausola OVER per fornire una media mobile e un totale cumulativo delle vendite annuali per ogni area presente nella tabella Sales.SalesPerson. I dati vengono partizionati in base a TerritoryID e ordinati logicamente in base a SalesYTD. La funzione AVG viene pertanto calcolata per ogni area in base all'anno di vendita. Si noti che per TerritoryID 1, sono presenti due righe per l'anno di vendita 2005, a indicare due venditori con vendite in tale anno. Viene calcolata la media delle vendite delle due righe e nel calcolo viene quindi inclusa la terza riga che rappresenta le vendite per l'anno 2006.

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;  

Questo è il set di risultati.

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)  
  

In questo esempio la clausola OVER non include PARTITION BY. La funzione verrà pertanto applicata a tutte le righe restituite dalla query. La clausola ORDER BY specificata nella clausola OVER determina l'ordine logico in base al quale viene applicata la funzione AVG. La query restituisce una media mobile delle vendite annuali per tutte le aree di vendita specificate nella clausola WHERE. La clausola ORDER BY specificata nell'istruzione SELECT determina l'ordine in cui vengono visualizzate le righe della query.

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;  

Questo è il set di risultati.

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. Specifica della clausola ROWS

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Nell'esempio seguente viene usata la clausola ROWS per definire una finestra su cui vengono calcolate le righe come la riga corrente e il numero N di righe che seguono (in questo esempio 1 riga).

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;  

Questo è il set di risultati.

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  

Nell'esempio seguente la clausola ROWS viene specificata con UNBOUNDED PRECEDING. Come risultato si ottiene che la finestra inizia in corrispondenza della prima riga della partizione.

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;  

Questo è il set di risultati.

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  
  

Esempi: Piattaforma di strumenti analitici (PDW)

E. Utilizzo della clausola OVER con la funzione ROW_NUMBER

L'esempio seguente restituisce ROW_NUMBER per i venditori in base alle rispettive quote di vendite assegnata.

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

Set di risultati parziale:

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. Utilizzo della clausola OVER con funzioni di aggregazione

Nell'esempio seguente viene illustrato l'uso della clausola OVER con funzioni di aggregazione. Nell'esempio l'uso della clausola OVER è più efficace rispetto all'uso di sottoquery.

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

Questo è il set di risultati.

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  

Nell'esempio seguente viene illustrato l'uso della clausola OVER con una funzione di aggregazione in un valore calcolato. Si noti che le aggregazioni vengono calcolate in base a SalesOrderNumber, mentre la percentuale dell'ordine di vendita totale viene calcolata per ogni riga di ogni 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;  

La prima parte di questo set di risultati è:

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  

Vedi anche

Funzioni di aggregazione (Transact-SQL)
Funzioni analitiche (Transact-SQL)
Post di blog sulle funzioni finestra e OVER, in sqlmag.com, di Itzik Ben-Gan