Funzione SQL FIRST_VALUE

Scoprite la potenza della funzione FIRST_VALUE, una funzione finestra che restituisce il primo valore in una partizione ordinata dell'insieme dei risultati. Questa funzione è utile a chiunque faccia analisi di dati in SQL.

Le funzioni finestra sono una potente categoria di funzioni che consentono di effettuare sofisticate analisi dei dati in SQL. Queste funzioni operano su una "finestra" di righe relative alla riga corrente, consentendo di preparare rapporti complessi come totali, medie mobili, classifiche e altro. Una delle funzioni finestra più comuni è la funzione FIRST_VALUE.

La funzione FIRST_VALUE restituisce il primo valore di una partizione ordinata di un insieme di risultati. La funzione può essere utile negli scenari in cui si confronta un valore attuale con il primo valore di un determinato insieme:

• la variazione dei prezzi delle azioni dal prezzo della prima emissione al prezzo attuale, che fornisce una visione della performance di un'azione dal suo debutto.
• la variazione del prezzo del prodotto dal prezzo di prima emissione al prezzo attuale, per analizzare le fluttuazioni di prezzo o il livello di sconto rispetto al prezzo originale.
• la variazione dei primi segni vitali registrati (come la pressione sanguigna o il livello di glucosio) di un paziente al momento del ricovero in ospedale rispetto alle letture successive.
• confrontare la temperatura attuale con la prima temperatura registrata in questa data o in questo anno, ad esempio per studiare i cambiamenti climatici o i modelli meteorologici.

La funzione FIRST_VALUE è la controparte duale di un'altra funzione finestra di SQL, LAST_VALUE. FIRST_VALUE restituisce il primo valore di un insieme di dati, mentre LAST_VALUE restituisce l'ultimo valore di un insieme di dati.

Se volete imparare a conoscere le funzioni finestra, vi consiglio il nostro corso interattivo Window Functions (Funzioni Finestra). Questo corso è ideale per chiunque voglia imparare le funzioni finestra di SQL e utilizzarle per lavorare con i dati in modo più efficace. Il corso contiene oltre 200 esercizi interattivi che vi aiuteranno a prendere confidenza con le vostre capacità.

Come utilizzare la funzione FIRST_VALUE

La funzione FIRST_VALUE restituisce il primo valore di un'espressione in un insieme ordinato da un'altra espressione. La sintassi di FIRST_VALUE è semplice:

FIRST_VALUE(expression) OVER (
	[PARTITION BY column_1, column_2, ...]
	ORDER BY expression2
	[ROWS BETWEEN range_start AND range_end]
)

• expression - la colonna o l'espressione da cui si vuole recuperare il primo valore.
• ORDER BY - definisce l'ordinamento delle righe dei dati in ogni partizione. FIRST_VALUE lo utilizza per identificare la prima riga da cui prelevare il valore.
• PARTITION BY - opzionale; divide l'insieme dei risultati in partizioni alle quali viene applicata la funzione FIRST_VALUE in modo indipendente. Se PARTITION BY viene omesso, la funzione tratta l'intero insieme di risultati come un'unica partizione.
• ROWS BETWEEN - opzionale; definisce la cornice della finestra, cioè l'insieme delle righe di ogni partizione rispetto alla riga corrente.

Dimostriamo la funzione FIRST_VALUE attraverso degli esempi che utilizzano una tabella stock_quotes. La tabella contiene i prezzi delle azioni di diverse società (simboli) in date e orari diversi. La tabella ha le seguenti colonne:

• id (integer) - Un identificatore unico per ogni record.
• symbol (text) - Il simbolo del titolo.
• quote_date (timestamp) - La data e l'ora della quotazione.
• price (decimal) - Il prezzo del titolo in quella data.

Ecco alcune righe di esempio della tabella (con dati fittizi):

Esempio 1: Uso semplice di FIRST_VALUE - il primo prezzo di una particolare società

Questo è l'uso più semplice della funzione FIRST_VALUE: vogliamo trovare il primo prezzo registrato per una particolare società, come Apple Inc. (AAPL). Possiamo utilizzare la seguente query SQL:

 SELECT
  quote_date,
  price,
  FIRST_VALUE(price) OVER (ORDER BY quote_date) AS first_price
 FROM stock_quotes
 WHERE symbol = 'AAPL';

Questa query seleziona solo i prezzi delle azioni di Apple Inc. (simbolo AAPL). Seleziona la data di quotazione e il prezzo in tale data. Utilizza la funzione FIRST_VALUE per selezionare il primo prezzo registrato per la società. Tutti i prezzi delle azioni Apple sono ordinati con OVER (ORDER BY quote_date). La funzione FIRST_VALUE restituisce il prezzo della prima riga di questo ordinamento, ovvero il primo prezzo mai registrato per Apple.

Esempio 2: FIRST_VALUE con PARTITION BY - Primo prezzo per tutti i simboli

In genere si utilizza FIRST_VALUE con PARTITION BY. In questo modo la funzione FIRST_VALUE viene applicata separatamente in ogni partizione definita da PARTITION BY. Supponiamo di dover trovare il primo prezzo per ogni società. Ecco la query da scrivere:

SELECT
  symbol,
  quote_date,
  price,
  FIRST_VALUE(price) OVER (PARTITION BY symbol ORDER BY quote_date) AS first_price
FROM stock_quotes;

In questa query si partizionano i dati in base a symbol (PARTITION BY symbol). In questo modo si crea una partizione separata per ogni società (simbolo). La funzione FIRST_VALUE viene applicata a ciascuna partizione separatamente. Come nell'esempio precedente, i dati vengono ordinati per data di quotazione. La funzione FIRST_VALUE restituisce il prezzo della prima riga di ogni partizione. La query restituisce quindi il simbolo, la data di quotazione, il prezzo in questa data e il primo prezzo registrato per questo simbolo.

È possibile utilizzare il valore della funzione FIRST_VALUE per calcolare la crescita percentuale del prezzo attuale rispetto al primo prezzo registrato, in questo modo:

SELECT
  symbol,
  quote_date,
  price,
  FIRST_VALUE(price) OVER (
    PARTITION BY symbol 
    ORDER BY quote_date) AS first_price,
  100.0 * (price - FIRST_VALUE(price) OVER (
         PARTITION BY symbol 
         ORDER BY quote_date)) 
   / FIRST_VALUE(price) OVER (
         PARTITION BY symbol 
         ORDER BY quote_date) AS perc_growth
FROM stock_quotes;

Esempio 3. Primo valore di ogni giorno per ogni società

È possibile utilizzare la funzione FIRST_VALUE per calcolare il prezzo di apertura (il primo prezzo del giorno) per ogni data, in questo modo:

SELECT DISTINCT
  symbol,
  CAST(quote_date AS DATE) AS date,
  FIRST_VALUE(price) OVER (
    PARTITION BY symbol, CAST(quote_date AS DATE) 
    ORDER BY quote_date) AS opening_price
FROM stock_quotes;

Qui dividiamo i dati per simbolo (società) e data. La colonna quote_date viene trasformata in DATE per estrarre solo il valore della data. Come prima, utilizziamo ORDER BY quote_date per ordinare i valori per data. Utilizziamo la funzione FIRST_VALUE che seleziona la prima riga di ogni partizione, ovvero il primo prezzo per ogni data e per ogni società. In questo modo otteniamo il prezzo di apertura di ogni giorno: il primo prezzo registrato in questa data. Utilizziamo anche DISTINCT per ottenere una sola riga per ogni data e simbolo.

Per altri esempi pratici di funzioni SQL a finestra, consultate il nostro articolo: Esempi di SQL Window Functions (Funzioni Finestra) . È inoltre possibile consultare il nostro articolo 11 Esercizi SQL Window Functions (Funzioni Finestra) con soluzioni che contiene esercizi pratici sulle funzioni finestra.

FIRST_VALUE e altre Window Functions (Funzioni Finestra)

FIRST_VALUE è simile alle altre funzioni finestra.

PRIMO_VALORE vs ULTIMO_VALORE

La funzione FIRST_VALUE ha una doppia controparte, LAST_VALUE. Come suggerisce il nome, la funzione LAST_VALUE restituisce l'ultimo valore nella partizione ordinata di un insieme di risultati. Con FIRST_VALUE, si confronta il valore corrente con il punto iniziale dei dati, mentre con LAST_VALUE, lo si confronta con il punto finale.

Quando si usa la funzione LAST_VALUE, bisogna ricordarsi di impostare la cornice della finestra su RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING per assicurarsi che includa le righe dalla riga corrente alla fine della partizione. Altrimenti OVER() utilizza la cornice predefinita per ORDER BY e LAST_VALUE restituisce il valore della riga corrente.

Per ricordare questo dettaglio e altre sfumature delle funzioni finestra di SQL, consigliamo il nostro SQL Window Functions (Funzioni Finestra) Cheat Sheet. Si tratta di una guida rapida e scaricabile per le funzioni finestra.

MIN vs FIRST_VALUE

La funzione FIRST_VALUE può essere facilmente confusa con la funzione MIN. La funzione MIN restituisce il valore più piccolo dell'insieme di dati. La funzione FIRST_VALUE trova il valore di un'espressione per la riga più piccola in base all'espressione fornita in ORDER BY.

In genere si usa la funzione FIRST_VALUE con due espressioni diverse come argomento della funzione FIRST_VALUE e in ORDER BY. Questa query, come abbiamo visto più volte in questo articolo, restituisce il primo prezzo registrato di Apple, Inc.

SELECT
   quote_date,
   price,  
   FIRST_VALUE(price) OVER(ORDER BY quote_date) AS first_price
FROM stock_quotes
WHERE symbol = 'AAPL';

Se si utilizza la colonna prezzo sia come argomento della funzione FIRST_VALUE sia in ORDER BY, la query restituisce anche il prezzo più piccolo di Apple, Inc.

SELECT
   FIRST_VALUE(price) OVER(ORDER BY price) AS first_price
FROM stock_quotes
WHERE symbol = 'AAPL';

Questa query restituisce più righe, una per ogni riga con il prezzo delle azioni Apple.

Questa query restituisce il prezzo minimo di Apple, Inc:

SELECT
  MIN(price)
FROM stock_quotes
WHERE symbol = 'AAPL';

La query restituisce una sola riga con un solo valore: il prezzo minimo registrato per Apple.

È possibile utilizzare la funzione MIN come funzione finestra:

SELECT
  quote_date,
  price,  
  MIN(price) OVER(ORDER BY quote_date) AS min_price
FROM stock_quotes
WHERE symbol = 'AAPL';

Questa query restituisce il minimo della colonna price ordinata per quote_date. In altre parole, restituisce il prezzo più piccolo riscontrato dall'inizio del set di dati fino a quella riga, ordinata per quote_date.

FIRST_VALUE vs. LEAD e LAG

FIRST_VALUE è simile alle funzioni LEAD e LAG. LEAD e LAG restituiscono il valore delle righe successive o precedenti, mentre FIRST_VALUE restituisce il valore della prima riga della partizione. LEAD e LAG sono ideali quando è necessario confrontare una sequenza di valori, ad esempio per confrontare le vendite da un mese all'altro. FIRST_VALUE si usa in scenari in cui si fa riferimento alla voce iniziale di un set di dati.

Per una panoramica degli argomenti più importanti relativi alle funzioni SQL window, consultate il nostro articolo Top 10 SQL Window Functions (Funzioni Finestra) Interview Questions

Conclusioni e ulteriori letture

La funzione FIRST_VALUE è un'importante funzione finestra di SQL. È utile per analizzare le tendenze nel tempo, ad esempio per vedere l'andamento dei prezzi delle azioni o delle vendite in un determinato periodo. Imparare a usare FIRST_VALUE può migliorare la vostra capacità di analizzare i dati delle serie temporali.

Se siete interessati a migliorare l'uso delle funzioni di SQL window, vi consigliamo il nostro corso interattivo Window Functions (Funzioni Finestra). Contiene 218 esercizi pratici che aiutano ad apprendere le funzioni finestra attraverso la pratica. Se conoscete già le funzioni finestra e volete fare più pratica, consultate il nostroset di esercitazioni Window Functions (Funzioni Finestra) con 100 esercizi per esercitarvi sulle funzioni finestra in scenari reali.

Per una guida rapida sulle funzioni finestra di SQL, consultate il foglio SQL Window Functions (Funzioni Finestra) Cheat Sheet. Si tratta di una guida rapida alle funzioni finestra, disponibile online e scaricabile in formato PDF.

Tutti i nostri corsi, compresi Window Functions (Funzioni Finestra) e Window Functions (Funzioni Finestra) Practice Set, sono disponibili da soli o come parte del nostro pacchetto Completo per sempre SQL. Questo pacchetto consente di accedere a vita a tutti i corsi SQL attuali e futuri della nostra piattaforma con un pagamento unico. Sia che stiate iniziando o che stiate migliorando le vostre competenze in SQL, questo pacchetto supporta il vostro percorso di apprendimento in ogni fase. Iscrivetevi oggi stesso per migliorare costantemente le vostre competenze in SQL.