SQL
Funzioni (analitico)

introduzione

Sintassi

1. FIRST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
2. LAST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
3. LAG (scalar_expression [, offset] [, default]) OVER ([partition_by_clause] order_by_clause)
4. LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
5. PERCENT_RANK () OVER ([partition_by_clause] order_by_clause)
6. CUME_DIST () OVER ([partition_by_clause] order_by_clause)
7. PERCENTILE_DISC (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])
8. PERCENTILE_CONT (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])

FIRST_VALUE

Si utilizza la funzione FIRST_VALUE per determinare il primo valore in un set di risultati ordinato, che si identifica utilizzando un'espressione scalare.

SELECT StateProvinceID, Name, TaxRate,
       FIRST_VALUE(StateProvinceID)
        OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

In questo esempio, la funzione FIRST_VALUE viene utilizzata per restituire l' ID dello stato o della provincia con l'aliquota d'imposta più bassa. La clausola OVER viene utilizzata per ordinare le aliquote fiscali per ottenere il tasso più basso.

LAST_VALUE

La funzione LAST_VALUE fornisce l'ultimo valore in un set di risultati ordinato, che si specifica utilizzando un'espressione scalare.

SELECT TerritoryID, StartDate, BusinessentityID,
       LAST_VALUE(BusinessentityID) 
        OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

Questo esempio utilizza la funzione LAST_VALUE per restituire l'ultimo valore per ogni set di righe nei valori ordinati.

GAL e LEAD

La funzione LAG fornisce i dati sulle righe prima della riga corrente nello stesso set di risultati. Ad esempio, in un'istruzione SELECT , è possibile confrontare i valori nella riga corrente con i valori di una riga precedente.

Si utilizza un'espressione scalare per specificare i valori da confrontare. Il parametro offset è il numero di righe prima della riga corrente che verrà utilizzata nel confronto. Se non si specifica il numero di righe, viene utilizzato il valore predefinito di una riga.

Il parametro predefinito specifica il valore che deve essere restituito quando l'espressione all'offset ha un valore NULL . Se non si specifica un valore, viene restituito un valore di NULL .

La funzione LEAD fornisce i dati sulle righe dopo la riga corrente nel set di righe. Ad esempio, in un'istruzione SELECT , è possibile confrontare i valori nella riga corrente con i valori nella seguente riga.

Specificare i valori che dovrebbero essere confrontati utilizzando un'espressione scalare. Il parametro offset è il numero di righe dopo la riga corrente che verrà utilizzata nel confronto.

Specificare il valore che deve essere restituito quando l'espressione all'offset ha un valore NULL utilizzando il parametro predefinito. Se non si specificano questi parametri, viene utilizzato il valore predefinito di una riga e viene restituito un valore di NULL .

SELECT BusinessEntityID, SalesYTD,
       LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
       LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

Questo esempio utilizza le funzioni LEAD e LAG per confrontare i valori di vendita di ciascun dipendente con quelli degli impiegati elencati sopra e sotto, con i record ordinati in base alla colonna BusinessEntityID.

PERCENT_RANK e CUME_DIST

La funzione PERCENT_RANK calcola la classifica di una riga relativa al set di righe. La percentuale si basa sul numero di righe nel gruppo che hanno un valore inferiore rispetto alla riga corrente.

Il primo valore nel set di risultati ha sempre una percentuale di zero. Il valore per il valore più alto o ultimo del set è sempre uno.

La funzione CUME_DIST calcola la posizione relativa di un valore specificato in un gruppo di valori, determinando la percentuale di valori inferiore o uguale a quel valore. Questa è chiamata la distribuzione cumulativa.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Cumulative Distribution"
FROM Employee;

In questo esempio, si utilizza una clausola ORDER per partizionare - o raggruppare - le righe recuperate SELECT base ai titoli di lavoro dei dipendenti, con i risultati in ogni gruppo ordinati in base al numero di ore di congedo per malattia che i dipendenti hanno utilizzato.

La funzione PERCENT_RANK classifica le voci all'interno di ciascun gruppo. Per ciascuna voce, restituisce la percentuale di voci nello stesso gruppo con valori inferiori.

La funzione CUME_DIST è simile, tranne che restituisce la percentuale di valori inferiore o uguale al valore corrente.

PERCENTILE_DISC e PERCENTILE_CONT

La funzione PERCENTILE_DISC elenca il valore della prima voce in cui la distribuzione cumulativa è superiore al percentile fornito utilizzando il parametro numeric_literal .

I valori sono raggruppati per set di righe o partizioni, come specificato dalla clausola WITHIN GROUP .

La funzione PERCENTILE_CONT è simile alla funzione PERCENTILE_DISC , ma restituisce la media della somma della prima voce corrispondente e della voce successiva.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

Per trovare il valore esatto dalla riga che corrisponde o supera lo 0,5 percentile, si passa il percentile come valore letterale numerico nella funzione PERCENTILE_DISC . La colonna Percentile Discreta in un set di risultati elenca il valore della riga in cui la distribuzione cumulativa è superiore al percentile specificato.

Per basare il calcolo su un set di valori, si utilizza la funzione PERCENTILE_CONT . La colonna "Percentile continuo" nei risultati elenca il valore medio della somma del valore del risultato e il successivo valore di corrispondenza più alto.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;