MENU
  1. ホーム
  2. SQL
  3. Come utilizzare le funzioni FIRST_VALUE e LAST_VALUE in SQL per ottenere il primo e l’ultimo valore

Come utilizzare le funzioni FIRST_VALUE e LAST_VALUE in SQL per ottenere il primo e l’ultimo valore

SQL

Nell’analisi dei dati o nella creazione di report tramite SQL, spesso è necessario ottenere il primo o l’ultimo valore di un insieme di dati. Per semplificare questo processo, le funzioni FIRST_VALUE e LAST_VALUE risultano molto utili. In questo articolo, spiegheremo come utilizzare queste funzioni per manipolare i dati in modo efficiente, fornendo esempi concreti.

Indice

Utilizzo base della funzione FIRST_VALUE

La funzione FIRST_VALUE viene utilizzata per ottenere il primo valore all’interno di una finestra o partizione specificata. Ciò consente di estrarre facilmente il valore della prima riga di un set di dati.

Sintassi di base

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS first_value
FROM 
    table_name;

In questo esempio, i dati vengono partizionati in base a column2 e ordinati secondo column3, per poi restituire il primo valore di column1.

Esempio di utilizzo

Ad esempio, se si desidera ottenere il nome del primo dipendente assunto in ogni reparto da un database aziendale, si può utilizzare la seguente query.

SELECT 
    department,
    employee_name,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired
FROM 
    employees;

Questa query restituisce il nome del primo dipendente assunto in ogni reparto.

Esempi avanzati di utilizzo della funzione FIRST_VALUE

Oltre all’uso base, la funzione FIRST_VALUE è utile anche per query e analisi più complesse. Qui di seguito forniamo esempi pratici su dataset reali.

Ottenere la data della prima vendita da un dataset di vendite

Ad esempio, se si desidera ottenere la data della prima vendita per ogni prodotto in ciascun negozio, si può utilizzare la seguente query.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date) AS first_sale_date
FROM 
    sales;

Questa query restituisce la data della prima vendita di ogni prodotto in ciascun negozio.

Ottenere il risultato del primo esame da un dataset di voti degli studenti

Se si desidera gestire i dati dei voti degli studenti, è possibile ottenere il risultato del primo esame di ciascuno studente.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS first_exam_score
FROM 
    exam_results;

Questa query restituisce il punteggio del primo esame di ciascuno studente.

Ottenere l’importo del primo acquisto dai dati delle transazioni

Un esempio tratto dai dati delle transazioni dei clienti mostra come ottenere l’importo del primo acquisto per ciascun cliente.

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS first_purchase_amount
FROM 
    transactions;

Questa query restituisce l’importo del primo acquisto di ciascun cliente.

Utilizzo base della funzione LAST_VALUE

La funzione LAST_VALUE viene utilizzata per ottenere l’ultimo valore all’interno di una finestra o partizione specificata. Ciò consente di estrarre facilmente il valore dell’ultima riga di un set di dati.

Sintassi di base

SELECT 
    column1,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

In questo esempio, i dati vengono partizionati in base a column2 e ordinati secondo column3, per poi restituire l’ultimo valore di column1. È importante specificare l’intera finestra con la clausola ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Esempio di utilizzo

Ad esempio, se si desidera ottenere il nome dell’ultimo dipendente assunto in ogni reparto da un database aziendale, si può utilizzare la seguente query.

SELECT 
    department,
    employee_name,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired
FROM 
    employees;

Questa query restituisce il nome dell’ultimo dipendente assunto in ciascun reparto.

Esempi avanzati di utilizzo della funzione LAST_VALUE

Come la funzione FIRST_VALUE, anche LAST_VALUE è molto utile nell’analisi dei dati e nella creazione di report. Qui di seguito forniamo esempi pratici utilizzando dataset reali.

Ottenere la data dell’ultima vendita da un dataset di vendite

Ad esempio, se si desidera ottenere la data dell’ultima vendita per ogni prodotto in ciascun negozio, si può utilizzare la seguente query.

SELECT 
    store_id,
    product_id,
    sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Questa query restituisce la data dell’ultima vendita di ogni prodotto in ciascun negozio.

Ottenere il risultato dell’ultimo esame da un dataset di voti degli studenti

Se si desidera gestire i dati dei voti degli studenti, è possibile ottenere il risultato dell’ultimo esame di ciascuno studente.

SELECT 
    student_id,
    exam_date,
    score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Questa query restituisce il punteggio dell’ultimo esame di ciascuno studente.

Ottenere l’importo dell’ultimo acquisto dai dati delle transazioni

Un esempio tratto dai dati delle transazioni dei clienti mostra come ottenere l’importo dell’ultimo acquisto per ciascun cliente.

SELECT 
    customer_id,
    transaction_date,
    amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Questa query restituisce l’importo dell’ultimo acquisto di ciascun cliente.

Combinare FIRST_VALUE e LAST_VALUE

Combinando le funzioni FIRST_VALUE e LAST_VALUE, è possibile ottenere contemporaneamente il primo e l’ultimo valore di un set di dati. Questo permette di effettuare analisi avanzate, come il confronto tra il primo e l’ultimo valore di un intervallo temporale.

Ottenere la prima e l’ultima data di vendita da un dataset di vendite

Ad esempio, la seguente query restituisce la prima e l’ultima data di vendita per ciascun prodotto in ogni negozio.

SELECT 
    store_id,
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Questa query restituisce la prima e l’ultima data di vendita per ciascun prodotto in ogni negozio.

Ottenere la prima e l’ultima data di assunzione dei dipendenti

Per ottenere la prima e l’ultima data di assunzione di ciascun dipartimento nel database dei dipendenti, si può utilizzare la seguente query.

SELECT 
    department,
    employee_name,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Questa query restituisce il nome del dipendente assunto per primo e per ultimo in ciascun reparto.

Ottenere il primo e l’ultimo importo di acquisto dai dati delle transazioni

La seguente query mostra come ottenere il primo e l’ultimo importo di acquisto di ciascun cliente dai dati delle transazioni.

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Questa query restituisce il primo e l’ultimo importo di acquisto di ciascun cliente.

Utilizzo come funzioni di finestra

Le funzioni FIRST_VALUE e LAST_VALUE possono essere utilizzate come funzioni di finestra, il che consente di analizzare e manipolare i dati in modo più flessibile. Come funzioni di finestra, calcolano i valori di ogni riga e li restituiscono come risultati.

Concetto di base delle funzioni di finestra

Le funzioni di finestra eseguono calcoli sui set di risultati di una query e si applicano a specifiche finestre di righe. Le finestre sono definite utilizzando le clausole PARTITION BY e ORDER BY.

Sintassi di base

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS

 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

In questa sintassi, i dati vengono partizionati in base a column2 e ordinati secondo column3, per poi restituire il primo e l’ultimo valore di column1.

Esempio di utilizzo: Funzione di finestra sui dati di vendita

Ad esempio, per ottenere la prima e l’ultima data di vendita nei dati di vendita di ciascun negozio, si può utilizzare la seguente query.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Questa query restituisce la prima e l’ultima data di vendita di ciascun negozio.

Esempio di utilizzo: Dati degli studenti

Se si desidera ottenere il primo e l’ultimo punteggio di ciascun studente nel proprio dataset, si può utilizzare la seguente query.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Questa query restituisce il primo e l’ultimo punteggio d’esame di ciascun studente.

Partizionamento e utilizzo di ORDER BY

Una delle caratteristiche potenti delle funzioni FIRST_VALUE e LAST_VALUE è la capacità di partizionare e ordinare i dati. Questo permette di ottenere il primo e l’ultimo valore all’interno di gruppi specifici.

Concetto base del partizionamento

Il partizionamento suddivide i dati in gruppi specifici, consentendo di eseguire calcoli indipendenti per ciascun gruppo. La clausola PARTITION BY viene utilizzata per definire i partizionamenti.

Concetto base dell’ORDER BY

La clausola ORDER BY viene utilizzata per ordinare le righe all’interno di ciascuna partizione. Questo consente di identificare con precisione i primi e gli ultimi valori.

Esempio di utilizzo di partizionamento e ORDER BY

La seguente query mostra come ottenere la prima e l’ultima data di vendita per ciascun prodotto in ogni negozio utilizzando il partizionamento e l’ORDER BY.

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Questa query restituisce la prima e l’ultima data di vendita per ciascun prodotto in ogni negozio.

Esempio avanzato sui dati degli studenti

Per ottenere il primo e l’ultimo punteggio di ciascun studente dai dati di esame, si può utilizzare la seguente query.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Questa query restituisce il primo e l’ultimo punteggio d’esame di ciascun studente.

Esempio avanzato sui dati delle transazioni

Per ottenere il primo e l’ultimo importo di acquisto di ciascun cliente dai dati delle transazioni, si può utilizzare la seguente query.

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Questa query restituisce il primo e l’ultimo importo di acquisto di ciascun cliente.

Esercizi pratici e soluzioni

Per consolidare quanto appreso sulle funzioni FIRST_VALUE e LAST_VALUE in questo articolo, abbiamo preparato alcuni esercizi pratici. Ogni esercizio include una soluzione, che potrete testare per verificare la vostra comprensione.

Esercizio 1: Ottenere la prima e l’ultima data di assunzione di ciascun reparto

Crea una query per ottenere la prima e l’ultima data di assunzione di ciascun reparto dalla tabella employees.

-- employees table
+---------+------------+------------+
| emp_id  | department | hire_date  |
+---------+------------+------------+
| 1       | Sales      | 2020-01-01 |
| 2       | Sales      | 2021-05-10 |
| 3       | HR         | 2019-03-15 |
| 4       | HR         | 2020-07-23 |
| 5       | IT         | 2021-01-05 |
+---------+------------+------------+

Soluzione

SELECT 
    department,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Esercizio 2: Ottenere la prima e l’ultima data di vendita di ciascun prodotto

Crea una query per ottenere la prima e l’ultima data di vendita di ciascun prodotto dalla tabella sales.

-- sales table
+---------+------------+------------+
| sale_id | product_id | sale_date  |
+---------+------------+------------+
| 1       | 101        | 2022-01-01 |
| 2       | 102        | 2022-01-05 |
| 3       | 101        | 2022-02-01 |
| 4       | 103        | 2022-01-10 |
| 5       | 102        | 2022-03-01 |
+---------+------------+------------+

Soluzione

SELECT 
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esercizio 3: Ottenere il primo e l’ultimo importo di acquisto di ciascun cliente

Crea una query per ottenere il primo e l’ultimo importo di acquisto di ciascun cliente dalla tabella transactions.

-- transactions table
+-------------+----------+--------+--------------+
| transaction_id | customer_id | amount | transaction_date |
+-------------+----------+--------+--------------+
| 1           | 1001     | 200    | 2023-01-01   |
| 2           | 1002     | 150    | 2023-01-05   |
| 3           | 1001     | 300    | 2023-02-01   |
| 4           | 1003     | 250    | 2023-01-10   |
| 5           | 1002     | 400    | 2023-03-01   |
+-------------+----------+--------+--------------+

Soluzione

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Conclusione

In questo articolo, abbiamo esplorato l’utilizzo delle funzioni SQL FIRST_VALUE e LAST_VALUE per ottenere il primo e l’ultimo valore di un dataset. Abbiamo trattato le basi, esempi avanzati, l’utilizzo come funzioni di finestra, nonché il partizionamento e l’ORDER BY. Utilizzando queste funzioni, l’analisi dei dati e la creazione di report diventano molto più efficienti. Vi invitiamo a provare queste tecniche su dataset reali.

SQL
Analisi dei dati Data di Vendita Dati di Transazione FIRST_VALUE Funzioni Finestra LAST_VALUE ORDER BY Partizionamento Punteggi Esame SQL
Indice