Esempio di funzione finestra SQL con spiegazioni

Vi interessa sapere come funzionano le funzioni finestra? Scorrete in basso per vedere il nostro esempio di funzione finestra in SQL con le spiegazioni definitive!

Lefunzioni finestra in SQL sono un po' diverse: calcolano il loro risultato sulla base di un insieme di righe piuttosto che su una singola riga. Infatti, il termine "finestra" in "funzione finestra" si riferisce a questo insieme di righe.

Le funzioni finestra sono simili alle funzioni aggregate, ma c'è una differenza importante. Quando si utilizzano funzioni aggregate con la clausola GROUP BY si "perdono" le singole righe. Non è possibile mescolare gli attributi di una singola riga con i risultati di una funzione aggregata; la funzione viene eseguita sulle righe come un intero gruppo. Questo non avviene quando si utilizzano le funzioni SQL window: possiamo generare un insieme di risultati con alcuni attributi di una singola riga insieme ai risultati della funzione finestra. È bene che i nuovi sviluppatori SQL lo tengano presente. Esaminiamo quindi un semplice esempio di funzione finestra SQL in azione.

Volete imparare le funzioni SQL window? Date un'occhiata al nostro corso interattivo Window Functions corso interattivo!

Esempio di funzione finestra SQL

Le funzioni finestra possono essere chiamate nell'istruzione SELECT o nella clausola ORDER BY o nella clausola Tuttavia, non possono mai essere chiamate nella clausola WHERE nella clausola. Si noterà che tutti gli esempi di questo articolo chiamano la funzione finestra nell'elenco delle colonne. SELECT nell'elenco delle colonne.

Passiamo al primo esempio di funzione finestra SQL. Utilizzeremo la funzione "Employee" :

Inizieremo con RANKche è uno degli esempi più semplici di funzione finestra SQL. Restituisce la posizione di qualsiasi riga all'interno della partizione. Utilizziamola per classificare gli stipendi all'interno dei dipartimenti:

SELECT	
		RANK() OVER (PARTITION BY department ORDER BY salary DESC) 
			AS dept_ranking,
		department,
		employee_id, 
		full_name, 
		salary
FROM employee;

I risultati sono visibili qui sotto:

E se volessimo avere lo stesso report ma con tutti i dipendenti in cima alla classifica, poi tutti i dipendenti in seconda posizione e così via? Beh, vi diamo questa sfida da risolvere da soli. Condividete le vostre idee nella sezione commenti!

Procedendo con l'esempio della funzione SQL window, scopriamo la posizione dello stipendio di ogni dipendente rispetto allo stipendio più alto del suo reparto. A tal fine è necessaria un'espressione matematica, come ad esempio:

stipendio_dipendente / stipendio_massimo_in_profondità

La prossima query mostrerà tutti i dipendenti ordinati in base alla metrica di cui sopra; i dipendenti con lo stipendio più basso (rispetto allo stipendio più alto del loro reparto) saranno elencati per primi:

SELECT
  employee_id, 
  full_name, 
  department,
  salary,
  salary / MAX(salary) OVER (PARTITION BY department ORDER BY salary DESC) 
    AS salary_metric
FROM employee
ORDER BY 5;

Siete interessati a imparare le funzioni SQL a finestra? Provate il nostro corso interattivo Window Functions corso interattivo!

Un altro esempio di funzione SQL Window

Passiamo da un database di stipendi dei dipendenti al seguente database di orari dei treni:

Supponiamo di voler aggiungere una nuova colonna chiamata "tempo alla prossima stazione". Per ottenere questo valore, sottraiamo gli orari delle stazioni per coppie di stazioni contigue. Possiamo calcolare questo valore senza utilizzare una funzione SQL window, ma può essere molto complicato. È più semplice farlo utilizzando la funzione LEAD la funzione finestra. Questa funzione confronta i valori di una riga con quelli della riga successiva per ottenere un risultato. In questo caso, confronta i valori della riga "tempo di una stazione con quella immediatamente successiva.

Ecco quindi un altro esempio di funzione SQL window, questa volta per l'orario dei treni:

SELECT 
	train_id, 
	station,
	time as "station_time",
	lead(time) OVER (PARTITION BY train_id ORDER BY time) - time 
		AS time_to_next_station
FROM train_schedule;

Si noti che il calcolo della funzione LEAD funzione finestra utilizzando un'espressione che coinvolge una singola colonna e una funzione finestra; questo non è possibile con le funzioni aggregate.

Ecco i risultati della query:

Nel prossimo esempio, aggiungeremo una nuova colonna che mostra il tempo trascorso dalla prima fermata del treno alla stazione attuale. La chiameremo "tempo di viaggio trascorso". Con la funzione finestra MIN si ottiene l'ora di inizio del viaggio e si sottrae l'ora della stazione corrente. Ecco il prossimo esempio di funzione finestra SQL

SELECT 	
	train_id, 
	station,
	time as "station_time",
	time - min(time) OVER (PARTITION BY train_id ORDER BY time) 	
								AS elapsed_travel_time,
	lead(time) OVER (PARTITION BY train_id ORDER BY time) - time 
								AS time_to_next_station
FROM train_schedule;

Notate la nuova colonna nella tabella dei risultati:

Scoprite altri esempi di funzione SQL Window!

Lefunzioni finestra non sono un aspetto molto conosciuto dell'SQL, ma la loro potenza e flessibilità le rendono molto importanti. Ci sono clausole (ad esempio PARTITION BY e window frame) e argomenti che non abbiamo trattato in questo articolo, ma non lasciatevi fermare! È possibile saperne di più e trovare altri esempi di funzioni SQL a finestra utilizzando il nostro blog e l'opzione Window Functions corso in LearnSQL.it. Iniziate oggi stesso!