Funzioni analitiche

Le funzioni analitiche calcolano un valore aggregato basato su un gruppo di righe. Differiscono dalle funzioni aggregate in quanto restituiscono più righe per ciascun gruppo. Il gruppo di righe è chiamato finestra ed è definito dal analytic_clause. Per ogni riga, viene definita una finestra scorrevole di righe. La finestra determina l’intervallo di righe utilizzato per eseguire i calcoli per la riga corrente. Le dimensioni delle finestre possono essere basate su un numero fisico di righe o su un intervallo logico come il tempo.

Le funzioni analitiche sono l’ultima serie di operazioni eseguite in una query ad eccezione della clausola finaleORDERBY. Tutti i join e tutte le clausoleWHEREGROUPBY eHAVING vengono completate prima che le funzioni analitiche vengano elaborate. Pertanto, le funzioni analitiche possono apparire solo nell’elenco di selezione o nella clausola ORDERBY.

Le funzioni analitiche sono comunemente utilizzate per calcolare aggregati cumulativi, in movimento, centrati e di reporting.

analytic_function::=

Descrizione dell’illustrazione ” analytic_function.gif “

analytic_clause::=

Descrizione dell’illustrazione ” analytic_clause.gif “

query_partition_clause::=

Descrizione dell’illustrazione ” query_partition_clause.gif “

order_by_clause::=

Descrizione dell’illustrazione ” order_by_clause.gif “

windowing_clause:: =

Descrizione dell’illustrazione ” windowing_clause.gif “

La semantica di questa sintassi sono discussi nelle sezioni che seguono.

analytic_function

Specificare il nome di una funzione analitica (vedere l’elenco delle funzioni analitiche dopo questa discussione di semantica).

argomenti

Le funzioni analitiche prendono da 0 a 3 argomenti. Gli argomenti possono essere qualsiasi tipo di dati numerici o qualsiasi tipo di dati non numerici che possono essere convertiti implicitamente in un tipo di dati numerici. Oracle determina l’argomento con la precedenza numerica più alta e converte implicitamente gli argomenti rimanenti in quel tipo di dati. Il tipo restituito è anche quel tipo di dati, se non diversamente specificato per una singola funzione.

analytic_clause

Use OVERanalytic_clause to indicate that the function operates on a query result set. This clause is computed after the FROMWHEREGROUPBY, and HAVING clauses. È possibile specificare le funzioni analitiche con questa clausola nell’elenco di selezione o ORDERBY clausola. Per filtrare i risultati di una query basata su una funzione analitica, annidare queste funzioni all’interno della query padre e quindi filtrare i risultati della sottoquery nidificata.

Note su analytic_clause: Le seguenti note si applicano aanalytic_clause:

• Non è possibile annidare le funzioni analitiche specificando qualsiasi funzione analitica in qualsiasi parte dianalytic_clause. Tuttavia, è possibile specificare una funzione analitica in una sottoquery e calcolare un’altra funzione analitica su di essa.

• È possibile specificare OVERanalytic_clause con funzioni analitiche definite dall’utente e funzioni analitiche integrate. Vedere FUNZIONE CREA.

query_partition_clause

Utilizzare la clausola PARTITIONBYper partizionare il set di risultati della query in gruppi basati su uno o più value_expr. Se si omette questa clausola, la funzione considera tutte le righe del set di risultati della query come un singolo gruppo.

Per utilizzare il query_partition_clause in una funzione analitica, utilizzare il ramo superiore della sintassi (senza parentesi). Per utilizzare questa clausola in una query modello (nelmodel_column_clauses) o in un join esterno partizionato (nelouter_join_clause), utilizzare il ramo inferiore della sintassi (con parentesi).

È possibile specificare più funzioni analitiche nella stessa query, ognuna con lo stesso o diversoPARTITIONBY chiavi.

Se gli oggetti interrogati hanno l’attributo parallel e se si specifica una funzione analitica con query_partition_clause, anche i calcoli delle funzioni vengono parallelizzati.

I valori validi divalue_expr sono costanti, colonne, funzioni non analitiche, espressioni di funzioni o espressioni che coinvolgono uno di questi.

order_by_clause

Utilizzare order_by_clause per specificare come vengono ordinati i dati all’interno di una partizione. Per tutte le funzioni analitiche è possibile ordinare i valori in una partizione su più chiavi, ognuna definita da un value_expr e ciascuna qualificata da una sequenza di ordinamento.

All’interno di ogni funzione, è possibile specificare più espressioni di ordinamento. Ciò è particolarmente utile quando si utilizzano funzioni che classificano valori, poiché la seconda espressione può risolvere i legami tra valori identici per la prima espressione.

Ogni volta che order_by_clause produce valori identici per più righe, la funzione si comporta come segue:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK e RANK restituiscono lo stesso risultato per ognuna delle righe.

• ROW_NUMBERassegna a ogni riga un valore distinto anche se esiste un legame basato su order_by_clause. Il valore si basa sull’ordine in cui viene elaborata la riga, che può essere non deterministico se ORDERBY non garantisce un ordine totale.

• Per tutte le altre funzioni analitiche, il risultato dipende dalle specifiche della finestra. Se si specifica una finestra logica con la parola chiave RANGE, la funzione restituisce lo stesso risultato per ciascuna delle righe. Se si specifica una finestra fisica con la parola chiaveROWS, il risultato non è deterministico.

Restrizioni sulla clausola ORDER BY Le seguenti restrizioni si applicano alla clausola ORDERBY :

• Quando viene utilizzato in una funzione analitica, order_by_clause deve assumere un’espressione (expr). La parola chiave SIBLINGS non è valida (è rilevante solo nelle query gerarchiche). Anche gli alias di posizione (position) e colonna (c_alias) non sono validi. Altrimenti questo order_by_clause è lo stesso utilizzato per ordinare la query o la sottoquery complessiva.

• An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDERBY clause if it specifies any of the following windows:

  • RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW. The short form of this is RANGEUNBOUNDEDPRECEDING.

  • RANGEBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING

  • RANGEBETWEENCURRENTROWANDCURRENTROW

  • RANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING

  I limiti delle finestre diversi da questi quattro possono avere una sola chiave di ordinamento nella clausola ORDERBY della funzione analitica. Questa restrizione non si applica ai limiti delle finestre specificati dalla parola chiave ROW.

ASC / DESC Specificare la sequenza di ordine (crescente o decrescente). ASC è il valore predefinito.

NULLS FIRST / NULLS LAST Specifica se le righe restituite contenenti null devono apparire per prime o per ultime nella sequenza di ordinamento.

NULLSLAST è l’impostazione predefinita per l’ordine crescente e NULLSFIRST è l’impostazione predefinita per l’ordine decrescente.

Le funzioni analitiche operano sempre su righe nell’ordine specificato nelorder_by_clause della funzione. Tuttavia, order_by_clause della funzione non garantisce l’ordine del risultato. Utilizzare il order_by_clause della query per garantire l’ordine del risultato finale.

Vedere anche:

order_by_clause of SELECT per ulteriori informazioni su questa clausola

windowing_clause

Alcune funzioni analitiche consentono il windowing_clause. Nell’elenco delle funzioni analitiche alla fine di questa sezione, le funzioni che consentono il windowing_clause sono seguite da un asterisco (*).

RIGHE / INTERVALLO Queste parole chiave definiscono per ogni riga una finestra (un insieme fisico o logico di righe) utilizzata per calcolare il risultato della funzione. La funzione viene quindi applicata a tutte le righe della finestra. La finestra si sposta attraverso il set di risultati della query o la partizione dall’alto verso il basso.

• ROWS specifica la finestra in unità fisiche (righe).

• RANGE specifica la finestra come offset logico.

Non è possibile specificare questa clausola a meno che non sia stato specificato il order_by_clause. Alcuni limiti delle finestre definiti dalla clausolaRANGE consentono di specificare una sola espressione in order_by_clause. Fare riferimento a “Restrizioni sulla clausola ORDER BY”.

Il valore restituito da una funzione analitica con un offset logico è sempre deterministico. Tuttavia, il valore restituito da una funzione analitica con un offset fisico può produrre risultati non deterministici a meno che l’espressione di ordinamento non determini un ordinamento univoco. Potrebbe essere necessario specificare più colonne in order_by_clause per ottenere questo ordine univoco.

TRA … E utilizzare il BETWEENAND clausola per specificare un punto iniziale e un punto finale per la finestra. La prima espressione (prima di AND) definisce il punto iniziale e la seconda espressione (dopo AND) definisce il punto finale.

Se si omette BETWEEN e si specifica un solo punto finale, Oracle lo considera il punto iniziale e il punto finale viene impostato sulla riga corrente.

UNBOUNDED PRECEDENTE SpecificareUNBOUNDEDPRECEDING per indicare che la finestra inizia dalla prima riga della partizione. Questa è la specifica del punto iniziale e non può essere utilizzata come specifica del punto finale.

UNBOUNDED FOLLOWING SpecificareUNBOUNDEDFOLLOWING per indicare che la finestra termina all’ultima riga della partizione. Questa è la specifica del punto finale e non può essere utilizzata come specifica del punto iniziale.

RIGA CORRENTE Come punto iniziale, CURRENTROWspecifica che la finestra inizia dalla riga o dal valore corrente (a seconda che sia stato specificatoROWoRANGE, rispettivamente). In questo caso il punto finale non può esserevalue_exprPRECEDING.

Come punto finale, CURRENTROW specifica che la finestra termina alla riga o al valore corrente (a seconda che sia stato specificato ROW o RANGE, rispettivamente). In questo caso il punto di partenza non può essere value_exprFOLLOWING.

value_expr PRECEDENTE o value_expr SEGUENTE Per RANGE o ROW:

• Sevalue_exprFOLLOWINGè il punto iniziale, il punto finale deve esserevalue_exprFOLLOWING.

• Sevalue_exprPRECEDINGè il punto finale, allora il punto iniziale deve esserevalue_exprPRECEDING.

Se si sta definendo una finestra logica definita da un intervallo di tempo in formato numerico, potrebbe essere necessario utilizzare le funzioni di conversione.

Vedi anche:

NUMTOYMINTERVAL e NUMTODSINTERVAL per informazioni sulla conversione dei tempi numerici in intervalli

Se hai specificatoROWS:

• value_expr è un offset fisico. Deve essere una costante o un’espressione e deve valutare un valore numerico positivo.

• Se value_expr fa parte del punto iniziale, allora deve valutare una riga prima del punto finale.

Se hai specificatoRANGE:

• value_expr è un offset logico. Deve essere una costante o un’espressione che valuta un valore numerico positivo o un intervallo letterale. Fare riferimento a “Letterali” per informazioni sui letterali a intervalli.

• È possibile specificare una sola espressione nel order_by_clause.

• Se value_expr restituisce un valore numerico, il ORDERBYexpr deve essere un valore numerico o DATE tipo di dati.

• If value_expr evaluates to an interval value, then the ORDERBYexpr must be a DATE data type.

If you omit the windowing_clause entirely, then the default is RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW.

Analytic functions are commonly used in data warehousing environments. Nell’elenco delle funzioni analitiche che segue, le funzioni seguite da un asterisco (*) consentono la sintassi completa, incluso windowing_clause.

AVG *
CORR *
CONTEGGIO *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
PRIMA
FIRST_VALUE *
GAL
ULTIMO
LAST_VALUE *
PIOMBO
LISTAGG
MAX
MEDIANA
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
CLASSIFICA
RATIO_TO_REPORT
REGR_ (Regressione Lineare) Funzioni *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANZA *

leggi Anche:

Oracle Database Data Warehousing Guida per ulteriori informazioni su queste funzioni e per gli scenari che illustrano il loro uso