Una funzione analitica calcola i valori su un gruppo di righe e restituisce un risultato unico per ogni riga. Questo è diverso da una funzione di aggregazione, che restituisce un singolo risultato per un gruppo di righe.

Una funzione analitica include una clausolaOVER, che definisce una finestra di righe attorno alla riga da valutare. Per ogni riga, il risultato della funzione analiticaè calcolato utilizzando la finestra selezionata di righe come input, possibile aggregazione.

Con funzioni analitiche è possibile calcolare medie mobili, elementi di rango, somme calculatecumulative, ed eseguire altre analisi.

Le seguenti funzioni possono essere utilizzate come funzioni analitiche:funzioni di navigazione,funzioni di numerazione e funzioni analitiche aggregate

Sintassi della funzione analitica

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }window_specification: ] ] window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }

Regole di notazione

• Le parentesi quadre “” indicano clausole opzionali.
• Parentesi “() ” indica parentesi letterali.
• La barra verticale ” / ” indica un OR logico.
• Le parentesi graffe “{ } ” racchiudono una serie di opzioni.
• Una virgola seguita da un’ellissi tra parentesi quadre “” indica che l’elemento precedente può ripetersi in un elenco separato da virgole.

Descrizione

Una funzione analitica calcola i risultati su un gruppo di righe. È possibile utilizzare la seguente sintassi per creare una funzione analitica:

• analytic_function_name: La funzione che esegue un’operazione analitica.Ad esempio, la funzione di numerazione RANK() potrebbe essere utilizzata qui.
• argument_list: Argomenti specifici per la funzione analitica.Alcune funzioni li hanno, altre no.
• OVER: Parola chiave richiesta nella sintassi della funzione analitica precedente alla clausola OVER.
• over_clause: Fa riferimento a una finestra che definisce un gruppo di righe in una tabella su cui utilizzare una funzione analitica.
• window_specification: Definisce le specifiche per la finestra.
• window_frame_clause: Definisce il frame della finestra per la finestra.
• rows_range: Definisce le righe fisiche o l’intervallo alogico per un frame di finestra.

Note

Una funzione analitica può apparire come un operando di espressione scalare in due posizioni nella query:

• L’elencoSELECT. Se la funzione analitica appare nell’elencoSELECT, la sua lista di argomenti e la clausolaOVER non possono fare riferimento agli alias introdotti nello stesso elenco di SELEZIONE.
• La clausolaORDER BY. Se la funzione analitica appare nella clausolaORDER BY della query, la sua lista di argomenti può fare riferimento agli aliasSELECT.

Una funzione analitica non può fare riferimento a un’altra funzione analitica nella sua lista di argomenti o alla sua clausolaOVER, anche indirettamente attraverso un alias.

Una funzione analitica viene valutata dopo l’aggregazione. Ad esempio, la clausolaGROUP BY e le funzioni aggregate non analitiche vengono valutate per prime.Poiché le funzioni aggregate vengono valutate prima delle funzioni analitiche, le funzioni aggregate possono essere utilizzate come operandi di input per le funzioni analitiche.

Restituisce

Un singolo risultato per ogni riga nell’input.

Definizione della clausola OVER

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }

Descrizione

La clausolaOVER fa riferimento a una finestra che definisce un gruppo di righe in una tabella su cui utilizzare una funzione analitica. È possibile fornire un named_window che è definito nella query, oppure è possibile definire le specifiche per una nuova finestra.

Note

Se non viene fornita né una finestra con nome né una specifica finestra, tutte le righe di input sono incluse nella finestra per ogni riga.

Esempi di utilizzo del tag OVER clausola

Queste query utilizzare la finestra di specifiche:

• consente di Calcolare un totale complessivo
• consente di Calcolare un totale parziale
• Calcolare una somma cumulativa
• Calcolo di una media mobile
• Calcola il numero di elementi all’interno di una gamma
• Ottenere l’elemento più popolare in ogni categoria
• Ottenere l’ultimo valore in un intervallo
• Calcolare il rango

Queste query con nome finestra:

• Ottieni l’ultimo valore in un intervallo
• Usa una finestra con nome in una clausola del frame della finestra

Definizione delle specifiche della finestra

window_specification: ] ] 

Descrizione

Definisce le specifiche per la finestra.

• named_window: il nome di una finestra esistente definita con una clausola WINDOW.

• PARTITION BY: Suddivide le righe di input in partizioni separate, su cui la funzione analitica viene valutata in modo indipendente.
  • Espressioni multiple di partizione sono consentite nella clausolaPARTITION BY.
  • Un’espressione non può contenere tipi in virgola mobile, tipi non raggruppabili,costanti o funzioni analitiche.
  • Se questa clausola opzionale non viene utilizzata, tutte le righe della tabella di input rappresentano una singola partizione.
• ORDER BY: definisce come le righe sono ordinate all’interno di una partizione.Questa clausola è facoltativa nella maggior parte delle situazioni, ma è richiesta in alcuni casi per le funzioni di navigazione.
• window_frame_clause: Per l’analisi aggregatafunzioni, definisce la cornice della finestra all’interno della partizione corrente.La cornice della finestra determina cosa includere nella finestra.Se viene utilizzata questa clausola, è necessario ORDER BY ad eccezione di fullyunbounded windows.

Note

Se non sono presenti né la clausolaORDER BY né la clausola window frame,la finestra include tutte le righe in quella partizione.

Per le funzioni analitiche aggregate, se la clausola ORDER BY è presente ma la clausola window frame non lo è, la seguente clausola window frame viene utilizzata per impostazione predefinita:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Per esempio, la query riportata di seguito sono equivalenti:

SELECT book, LAST_VALUE(item) OVER (ORDER BY year)FROM Library

SELECT book, LAST_VALUE(item) OVER ( ORDER BY year RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)FROM Library

le Regole per l’utilizzo di un nome di finestra in finestra specifica

Se si utilizza un nome di finestra in finestra specifiche, queste regole si applicano:

• specifiche denominata finestra può essere extendedwith nuove specifiche che definiscono la finestra specifica clausola.
• Non è possibile avere definizioni ridondanti. Se si dispone di una clausolaORDER BY nella finestra denominata e nella clausola specifica della finestra, viene generato un errore.

• L’ordine delle clausole è importante. PARTITION BY deve venire prima,seguito da ORDER BY e window_frame_clause. Se si aggiunge una finestra con nome, le specifiche della finestra vengono elaborate per prime.

  --this works:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW item_window AS (ORDER BY purchases)--this does not work:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ORDER BY purchases) AS most_popularFROM ProduceWINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

• Una finestra con nome ePARTITION BY non possono apparire insieme nelle specifiche della finestra. Se hai bisogno di PARTITION BY, aggiungilo alla finestra denominata.

• Non è possibile fare riferimento a una finestra con nome in una clausola ORDER BY, una query esterna o qualsiasi sottoquery.

Esempi di utilizzo della finestra specifica

Queste query definire le partizioni in una funzione analitica:

• consente di Calcolare un totale parziale
• Calcolare una somma cumulativa
• Ottenere l’elemento più popolare in ogni categoria
• Ottenere l’ultimo valore in un intervallo
• Calcolare il rango
• Utilizzare un nome finestra nel telaio di una finestra clausola

Queste query includono denominata finestra in una finestra specifica:

• Ottieni l’ultimo valore in un intervallo
• Usa una finestra con nome in una clausola del frame della finestra

Queste query definiscono come le righe sono ordinate in una partizione:

• consente di Calcolare un totale parziale
• Calcolare una somma cumulativa
• Calcolo di una media mobile
• Calcola il numero di elementi all’interno di una gamma
• Ottenere l’elemento più popolare in ogni categoria
• Ottenere l’ultimo valore in un intervallo
• Calcolare il rango
• Utilizzare un nome finestra nel telaio di una finestra clausola

Definire la cornice della finestra clausola

window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }frame_between: { BETWEEN unbounded_preceding AND frame_end_a | BETWEEN numeric_preceding AND frame_end_a | BETWEEN current_row AND frame_end_b | BETWEEN numeric_following AND frame_end_c }frame_start: { unbounded_preceding | numeric_preceding | }frame_end_a: { numeric_preceding | current_row | numeric_following | unbounded_following }frame_end_b: { current_row | numeric_following | unbounded_following }frame_end_c: { numeric_following | unbounded_following }unbounded_preceding: UNBOUNDED PRECEDINGnumeric_preceding: numeric_expression PRECEDINGunbounded_following: UNBOUNDED FOLLOWINGnumeric_following: numeric_expression FOLLOWINGcurrent_row: CURRENT ROW

Il telaio della finestra clausola definisce la cornice della finestra intorno alla riga corrente withina partizione, oltre che la funzione analitica è stata valutata.Solo le funzioni analitiche aggregate possono utilizzare una clausola window frame.

• rows_range: Una clausola che definisce un frame di finestra con righe fisiche o un intervallo logico.

  • ROWS: Calcola il frame della finestra in base agli offset fisici dalla riga corrente. Ad esempio, è possibile includere due righe prima e dopola riga corrente.
  • RANGE: Calcola il frame della finestra in base a un intervallo logico di righe attorno alla riga corrente, in base al valore della chiave ORDER BY della riga corrente.Il valore intervallo fornito viene aggiunto o sottratto al valore skey della riga corrente per definire un limite di intervallo iniziale o finale per il frame finestra. In un frame di finestra basato su intervallo, deve esserci esattamente unoespressione nella clausolaORDER BY e l’espressione deve avere un tipo anumerico.

  Suggerimento: Se si desidera utilizzare un intervallo con una data, utilizzareORDER BY con la funzioneUNIX_DATE(). Se si desidera utilizzare un intervallo con un timestamp,utilizzare la funzione UNIX_SECONDS()UNIX_MILLIS() o UNIX_MICROS().

• frame_between: Crea una cornice della finestra con un limite inferiore e superiore.Il primo limite rappresenta il limite inferiore. Il secondo confinarerappresenta il limite superiore. Solo alcune combinazioni di confini possono essereutilizzato, come mostrato nella sintassi precedente.

  • Definire l’inizio del frame della finestra conunbounded_precedingnumeric_precedingnumeric_following, o current_row.
    • unbounded_preceding: La cornice della finestra inizia all’inizio della partizione.
    • numeric_precedingonumeric_following: L’inizio del windowframe è relativo alla riga corrente.
    • current_row: La cornice della finestra inizia dalla riga corrente.
  • Definire la fine del telaio della finestra con numeric_precedingnumeric_followingcurrent_row o unbounded_following.
    • numeric_precedingonumeric_following: La fine del frame della finestra è relativa alla riga corrente.
    • current_row: La cornice della finestra termina alla riga corrente.
    • unbounded_following: La cornice della finestra termina alla fine della partizione.

• frame_start: Crea una cornice della finestra con un limite inferiore.La cornice della finestra termina alla riga corrente.

  • unbounded_preceding: La cornice della finestra inizia all’inizio della partizione.
  • numeric_preceding: L’inizio del frame della finestra è relativo alla riga corrente.
  • current_row: La cornice della finestra inizia dalla riga corrente.

• numeric_expression: Un’espressione che rappresenta un tipo numerico.L’espressione numerica deve essere un parametro integeror costante e non negativo.

Note

Se un limite si estende oltre l’inizio o la fine di una partizione,la cornice della finestra includerà solo le righe all’interno di quella partizione.

Non è possibile utilizzare una clausola window frame withnavigation functions andnumbering functions,ad esempio RANK().

Esempi utilizzando la clausola window frame

Queste query calcolano i valori con ROWS:

• Calcolare una somma cumulativa
• Calcolo di una media mobile
• Ottenere l’elemento più popolare in ogni categoria
• Ottenere l’ultimo valore in un intervallo
• Utilizzare un nome finestra nel telaio di una finestra clausola

Queste query calcolare i valori con RANGE:

• Calcola il numero di elementi all’interno di una gamma

Queste query calcolare i valori parzialmente o completamente scollegati finestra:

• consente di Calcolare un totale complessivo
• consente di Calcolare un totale parziale
• Calcolare una somma cumulativa
• Ottenere l’elemento più popolare in ogni categoria
• Calcolare il rango

Queste query calcolare i valori numerici confini:

• Calcolare una somma cumulativa
• Calcolo di una media mobile
• Calcola il numero di elementi all’interno di una gamma
• Ottenere l’ultimo valore in un intervallo
• Utilizzare un nome finestra nel telaio di una finestra clausola

Queste query calcolare i valori con la riga corrente come un limite:

• Calcola un totale complessivo
• Calcola un totale parziale
• Calcola una somma cumulativa

Facendo riferimento a una finestra con nome

SELECT query_expr, analytic_function_name ( ) OVER over_clauseFROM from_itemWINDOW named_window_expression over_clause: { named_window | ( ) }window_specification: ] ] named_window_expression: named_window AS { named_window | ( ) }

Una finestra con nome rappresenta un gruppo di righe in una tabella su cui utilizzare una funzione ananalitica. Una finestra denominata è definita nella clausolaWINDOW e fa riferimento aOVER di una funzione analitica clause.In una clausolaOVER, una finestra con nome può apparire da sola o incorporata all’interno di una specifica finestra.

Esempi

• Ottieni l’ultimo valore in un intervallo
• Usa una finestra con nome in una clausola del frame della finestra

Concetti di funzione di navigazione

Le funzioni di navigazione generalmente calcolano alcuni value_expression su una riga diversa nel frame della finestra dalla riga corrente. La sintassi della clausolaOVER varia tra le funzioni di navigazione.

Requisiti per la clausolaOVER:

• PARTITION BY: Opzionale.
• ORDER BY:
  1. Disallowed for PERCENTILE_CONT and PERCENTILE_DISC.
  2. Required for FIRST_VALUELAST_VALUENTH_VALUELEADand LAG.
• window_frame_clause:
  1. Disallowed for PERCENTILE_CONTPERCENTILE_DISCLEAD and LAG.
  2. Optional for FIRST_VALUELAST_VALUE, and NTH_VALUE.

Per tutte le funzioni di navigazione, il tipo di dati risultato è lo stesso divalue_expression.

Concetti di funzione di numerazione

Le funzioni di numerazione assegnano valori interi a ciascuna riga in base alla loro posizione all’interno della finestra specificata.

Esempio: RANK()DENSE_RANK() e ROW_NUMBER():

WITH Numbers AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 10 UNION ALL SELECT 10)SELECT x, RANK() OVER (ORDER BY x ASC) AS rank, DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank, ROW_NUMBER() OVER (ORDER BY x) AS row_numFROM Numbers+---------------------------------------------------+| x | rank | dense_rank | row_num |+---------------------------------------------------+| 1 | 1 | 1 | 1 || 2 | 2 | 2 | 2 || 2 | 2 | 2 | 3 || 5 | 4 | 3 | 4 || 8 | 5 | 4 | 5 || 10 | 6 | 5 | 6 || 10 | 6 | 5 | 7 |+---------------------------------------------------+

• RANK(): Per x = 5, rankè 4, poichéRANK() aumenta in base al numero di peer nel precedente gruppo di ordinamento delle finestre.
• DENSE_RANK(): Per x=5,dense_rank è 3, poichéDENSE_RANK() aumenta sempre di 1, senza mai saltare un valore.
• ROW_NUMBER(): Per x=5, row_num è 4.

Concetti di funzione analitica aggregata

Una funzione aggregata è una funzione che esegue un calcolo su un insieme di valori. La maggior parte delle funzioni aggregate può essere utilizzata in funzione ananalitica. Queste funzioni aggregate sono chiamatefunzioni analitiche aggregate.

Con le funzioni analitiche aggregate, la clausolaOVER viene aggiunta alla chiamata di funzione aggregate; la sintassi della chiamata di funzione rimane altrimenti invariata.Come le loro controparti di funzioni aggregate, queste funzioni analitiche eseguonoaggregazioni, ma in particolare sul frame della finestra pertinente per ogni riga.I tipi di dati di risultato di queste funzioni analitiche sono gli stessi delle controparti della funzione theiraggregate.

Esempi di funzioni analitiche

In questi esempi, l’elemento evidenziato è la riga corrente. Gli elementi in grassetto sono le righe incluse nell’analisi.

Tabelle comuni utilizzate negli esempi

Nei successivi esempi di aggregate analyticquery vengono utilizzate le seguenti tabelle:ProduceEmployeeseFarm.

Produce tabella

Alcuni esempi fanno riferimento a una tabella chiamataProduce:

WITH Produce AS (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category UNION ALL SELECT 'orange', 2, 'fruit' UNION ALL SELECT 'cabbage', 9, 'vegetable' UNION ALL SELECT 'apple', 8, 'fruit' UNION ALL SELECT 'leek', 2, 'vegetable' UNION ALL SELECT 'lettuce', 10, 'vegetable')SELECT * FROM Produce+-------------------------------------+| item | category | purchases |+-------------------------------------+| kale | vegetable | 23 || orange | fruit | 2 || cabbage | vegetable | 9 || apple | fruit | 8 || leek | vegetable | 2 || lettuce | vegetable | 10 |+-------------------------------------+

tabella Impiegati

Alcuni esempi di riferimento una tabella denominata Employees:

WITH Employees AS (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29) UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24) UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23) UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11) UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))SELECT * FROM Employees+-------------------------------------+| name | department | start_date |+-------------------------------------+| Isabella | 2 | 1997-09-28 || Anthony | 1 | 1995-11-29 || Daniel | 2 | 2004-06-24 || Andrew | 1 | 1999-01-23 || Jacob | 1 | 1990-07-11 || Jose | 2 | 2013-03-17 |+-------------------------------------+

Fattoria tabella

Alcuni esempi di riferimento una tabella denominata Farm:

WITH Farm AS (SELECT 'cat' as animal, 23 as population, 'mammal' as category UNION ALL SELECT 'duck', 3, 'bird' UNION ALL SELECT 'dog', 2, 'mammal' UNION ALL SELECT 'goose', 1, 'bird' UNION ALL SELECT 'ox', 2, 'mammal' UNION ALL SELECT 'goat', 2, 'mammal')SELECT * FROM Farm+-------------------------------------+| animal | category | population |+-------------------------------------+| cat | mammal | 23 || duck | bird | 3 || dog | mammal | 2 || goose | bird | 1 || ox | mammal | 2 || goat | mammal | 2 |+-------------------------------------+

Calcolare un totale complessivo

Questo calcola un totale complessivo per tutti gli elementiProduce tabella.

• (arancia, mela, porro, cavolo, lattuga, cavolo) = 54 totale acquisti
• (arancia, mela, porro, cavolo, lattuga, cavolo) = 54 totale acquisti
• (arancia, mela, porro, cavolo, lattuga, cavolo) = 54 totale acquisti
• (arancia, mela, porro, cavolo, lattuga, cavolo) = 54 totale acquisti
• (arancia, mela, porro, cavolo, lattuga, cavolo) = 54 totale acquisti
• (arancia, mela, porro, cavolo, lattuga, cavolo) = 54 totale acquisti

SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 54 || leek | 2 | vegetable | 54 || apple | 8 | fruit | 54 || cabbage | 9 | vegetable | 54 || lettuce | 10 | vegetable | 54 || kale | 23 | vegetable | 54 |+-------------------------------------------------------+

Calcolare un totale parziale

Questo calcola un totale parziale per ogni categoria inProduce tabella.

• frutta
  • (arancia, mela) = 10 totale acquisti
  • (arancia, mela) = 10 totale acquisti
• vegetali
  • (porro, cavolo, lattuga, cavolo) = 44 totale acquisti
  • (porro, cavolo, lattuga, cavolo) = 44 totale acquisti
  • (porro, cavolo, lattuga, cavolo) = 44 totale acquisti
  • (porro, cavolo, lattuga, cavolo) = 44 totale acquisti

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 10 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 44 || cabbage | 9 | vegetable | 44 || lettuce | 10 | vegetable | 44 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+

Calcolare una somma cumulativa

Questo calcola una somma cumulativa per ogni categoria nelProduce tabella. La somma viene calcolata rispetto all’ordine definito utilizzando la clausolaORDER BY.

• frutta
  • (arancia, mela) = 2 totale acquisti
  • (arancia, mela) = 10 totale acquisti
• vegetali
  • (porro, cavolo, lattuga, cavolo) = 2 totale acquisti
  • (porro, cavolo, lattuga, cavolo) = 11 totale acquisti
  • (porro, cavolo, lattuga, cavolo) = 21 totale acquisti
  • (porro, cavolo, lattuga, cavolo) = 44 totale acquisti

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 2 || cabbage | 9 | vegetable | 11 || lettuce | 10 | vegetable | 21 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+

Questa non è la stessa cosa dell’esempio precedente. Non è necessario aggiungere CURRENT ROW come limite a meno che non si desideri per la leggibilità.

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS UNBOUNDED PRECEDING ) AS total_purchasesFROM Produce

In questo esempio, tutti gli elementi della tabellaProduce sono inclusi nella partizione. Vengono analizzate solo le righe precedenti. L’analisi inizia due volte prima della riga corrente nella partizione.

• (arancione, porri, mele, cavolo, lattuga, cavolo) = NULL
• (arancione, porri, mele, cavolo, lattuga, cavolo) = NULL
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 2
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 4
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 12
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 21

SELECT item, purchases, category, SUM(purchases) OVER ( ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING ) AS total_purchasesFROM Produce;+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | NULL || leek | 2 | vegetable | NULL || apple | 8 | fruit | 2 || cabbage | 9 | vegetable | 4 || lettuce | 10 | vegetable | 12 || kale | 23 | vegetable | 21 |+-------------------------------------------------------+

Calcolare una media mobile

Questo calcola una media mobile nel Produce tabella.Il limite inferiore è 1 riga prima delriga corrente. Il limite superiore è 1 riga dopo la riga corrente.

• (arancione, porri, mele, cavolo, lattuga, cavolo) = 2 medio acquisti
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 4 medio acquisti
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 6.3333 media acquisti
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 9 media acquisti
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 14 media acquista
• (arancione, porri, mele, cavolo, lattuga, cavolo) = 16.5 acquisti medi

SELECT item, purchases, category, AVG(purchases) OVER ( ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | avg_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || leek | 2 | vegetable | 4 || apple | 8 | fruit | 6.33333 || cabbage | 9 | vegetable | 9 || lettuce | 10 | vegetable | 14 || kale | 23 | vegetable | 16.5 |+-------------------------------------------------------+

Calcola il numero di elementi all’interno di un intervallo

Questo esempio ottiene il numero di animali che hanno una popolazione simile nella tabellaFarm.

• (oca, cane, bue, capra, anatra, gatto) = 4 animali tra popolazione 0-2.
• (oca, cane, bue, capra, anatra, gatto) = 5 animali tra la fascia di popolazione 1-3.
• (oca, cane, bue, capra, anatra, gatto) = 5 animali tra la fascia di popolazione 1-3.
• (oca, cane, bue, capra, anatra, gatto) = 5 animali tra la fascia di popolazione 1-3.
• (oca, cane, bue, capra, anatra, gatto) = 4 animali tra la fascia di popolazione 2-4.
• (oca, cane, bue, capra, anatra, gatto) = 1 animale tra la fascia di popolazione 22-24.

SELECT animal, population, category, COUNT(*) OVER ( ORDER BY population RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS similar_populationFROM Farm;+----------------------------------------------------------+| animal | population | category | similar_population |+----------------------------------------------------------+| goose | 1 | bird | 4 || dog | 2 | mammal | 5 || ox | 2 | mammal | 5 || goat | 2 | mammal | 5 || duck | 3 | bird | 4 || cat | 23 | mammal | 1 |+----------------------------------------------------------+

Ottieni l’elemento più popolare in ogni categoria

Questo esempio ottiene l’elemento più popolare in ogni categoria. Definisce come le righe in una finestra sono partizionate e ordinate in ogni partizione. Si fa riferimento alla tabellaProduce.

• frutta
  • (arancia, mela) = apple è più popolare
  • (arancia, mela) = apple è più popolare
• vegetali
  • (porro, cavolo, lattuga, cavolo) = kale è più popolare
  • (porro, cavolo, lattuga, cavolo) = kale è più popolare
  • (porro, cavolo, lattuga, cavolo) = kale è più popolare
  • (porro, cavolo, lattuga, cavolo) = kale è più popolare

SELECT item, purchases, category, LAST_VALUE(item) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS most_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | kale || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+

Ottenere l’ultimo valore in un intervallo

in Questo esempio si ottiene l’elemento più popolare in una specifica cornice della finestra, usando Produce tavolo. Il telaio della finestra analizza fino a trerows alla volta. Dai un’occhiata da vicino alla colonnamost_popular per le verdure.Invece di ottenere l’elemento più popolare in una categoria specifica, ottiene l’elemento più popolare in un intervallo specifico in quella categoria.

• frutta
  • (arancia, mela) = apple è più popolare
  • (arancia, mela) = apple è più popolare
• vegetali
  • (porro, cavolo, lattuga, cavolo) = cavolo è più popolare
  • (porro, cavolo, lattuga, cavolo) = lattuga è più popolare
  • (porro, cavolo, lattuga, cavolo) = kale è più popolare
  • (porro, cavolo, lattuga, cavolo) = kale è più popolare

SELECT item, purchases, category, LAST_VALUE(item) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | cabbage || cabbage | 9 | vegetable | lettuce || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+

in Questo esempio restituisce lo stesso risultato dell’esempio precedente, ma includesa denominata finestra chiamata item_window. Alcune delle specifiche della finestra sono definite direttamente nella clausolaOVER e alcune sono definite nella finestra denominata.

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases)

Calcola rango

Questo esempio calcola il rango di ciascun dipendente all’interno del proprio dipartimento,in base alla data di inizio. La specifica della finestra è definita direttamentenella clausolaOVER. Si fa riferimento alla tabella Employees.

• dipartimento 1
  • (Giacobbe, Antonio, Andrea) = Assegnare rango 1 a Giacobbe
  • (Giacobbe, Antonio, Andrea) = Assegna il livello 2 di Anthony
  • (Giacobbe, Antonio, Andrea) = Assegnare rango 3, Andrew
• dipartimento 2
  • (Isabella, Daniel, Jose) = Assegnare rango 1 a Isabella
  • (Isabella, Daniel, Jose) = Assegna il livello 2 di Daniel
  • (Isabella, Daniel, Jose) = Assegnare rango 3 per Jose

SELECT name, department, start_date, RANK() OVER (PARTITION BY department ORDER BY start_date) AS rankFROM Employees;+--------------------------------------------+| name | department | start_date | rank |+--------------------------------------------+| Jacob | 1 | 1990-07-11 | 1 || Anthony | 1 | 1995-11-29 | 2 || Andrew | 1 | 1999-01-23 | 3 || Isabella | 2 | 1997-09-28 | 1 || Daniel | 2 | 2004-06-24 | 2 || Jose | 2 | 2013-03-17 | 3 |+--------------------------------------------+

Utilizzare un nome finestra nel telaio di una finestra clausola

Si può definire un po ‘ di logica in un nome finestra e alcuni di essi in clausola del telaio di awindow. Questa logica è combinata. Ecco un esempio, utilizzando la tabellaProduce.

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)+-------------------------------------------------------+| item | purchases | category | most_popular |+-------------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | lettuce || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+-------------------------------------------------------+

Si può anche ottenere i risultati precedenti con questi esempi:

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING), item_window AS (c)

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), item_window AS (b)

L’esempio seguente genera un errore perché il telaio di una finestra clausola beendefined due volte:

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)