Condividi tramite


Clausola OVER (Transact-SQL)

Determina il partizionamento e l'ordinamento del set di righe prima dell'applicazione della funzione finestra associata.

Si applica a:

Funzioni finestra di rango

Funzioni finestra di aggregazione. Per ulteriori informazioni, vedere Funzioni di aggregazione (Transact-SQL).

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQL

Sintassi

Ranking Window Functions 
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
           <ORDER BY_Clause> )

Aggregate Window Functions 
< OVER_CLAUSE > :: = 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

Argomenti

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

  • value_expression
    Specifica la colonna di partizionamento del set di righe generato dalla clausola FROM corrispondente. value_expression può fare riferimento solo a colonne rese disponibili dalla clausola FROM e non a espressioni o alias nell'elenco di selezione. value_expression può essere un'espressione di colonna, una sottoquery scalare, una funzione scalare o una varibile definita dall'utente.

  • <ORDER BY Clause>
    Specifica l'ordine di applicazione della funzione finestra di rango. Per ulteriori informazioni, vedere Clausola ORDER BY (Transact-SQL).

    Nota importanteImportante

    Quando viene utilizzato nel contesto di una funzione finestra di rango, l'argomento <ORDER BY Clause> può fare riferimento solo a colonne rese disponibili dalla clausola FROM. Non è possibile specificare un valore integer per rappresentare la posizione del nome o l'alias di una colonna nell'elenco di selezione. <ORDER BY Clause> non può essere utilizzato con funzioni finestra di aggregazione.

Osservazioni

Le funzioni finestra sono definite nello standard SQL ISO. SQL Server offre funzioni finestra di aggregazione e di rango. Una finestra è un set di righe specificato dall'utente e una funzione finestra calcola un valore per ogni riga in un set di risultati derivato dalla finestra.

È possibile utilizzare più funzioni finestra di rango o aggregazione in una singola query con una singola clausola FROM. La clausola OVER di ogni funzione, tuttavia, può specificare un partizionamento e un ordinamento diversi. Non è possibile utilizzare la clausola OVER con la funzione di aggregazione CHECKSUM.

Esempi

A. Utilizzo della clausola OVER con la funzione ROW_NUMBER

Ogni funzione di rango, ad esempio ROW_NUMBER, DENSE_RANK, RANK o NTILE, utilizza la clausola OVER. Nell'esempio seguente viene illustrato l'utilizzo della clausola OVER con ROW_NUMBER.

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;
GO

B. Utilizzo della clausola OVER con funzioni di aggregazione

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

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

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

Set di risultati: Si noti che le aggregazioni vengono calcolate in base a SalesOrderID, mentre Percent by ProductID viene calcolato 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.43

43664

773

1

14

7.14

43664

778

1

14

7.14

Vedere anche

Riferimento