Comprendere le funzioni della finestra SQL una volta per tutte | di Mateus Trentz | Maggio 2024 | Intelligenza-Artificiale

Una guida passo passo per comprendere le funzioni delle finestre

Le funzioni della finestra sono fondamentali per scrivere un codice SQL che sia efficiente e facile da comprendere. Sapere come funzionano e quando usarli sbloccherà nuovi modi per risolvere i tuoi problemi di reporting.

L'obiettivo di questo articolo è spiegare passo dopo passo le funzioni della finestra in SQL in modo comprensibile in modo che non sia necessario fare affidamento solo sulla memorizzazione della sintassi.

Ecco cosa tratteremo:

• Una spiegazione su come dovresti visualizzare le funzioni della finestra
• Ripassa molti esempi con difficoltà crescente
• Guarda uno scenario reale specifico per mettere in pratica ciò che abbiamo imparato
• Rivedere ciò che abbiamo imparato

Il nostro set di dati è semplice, sei righe di dati sulle entrate per due regioni nell'anno 2023.

Se prendessimo questo set di dati ed eseguissimo un file GROUP BY somma sulle entrate di ciascuna regione, sarebbe chiaro cosa succede, no? Risulterebbero solo due righe rimanenti, una per ciascuna regione, e poi la somma delle entrate:

Il modo in cui voglio che tu visualizzi le funzioni della finestra è molto simile a questo ma, invece di ridurre il numero di righe, l'aggregazione verrà eseguita “in background” e i valori verranno aggiunti alle righe esistenti.

Innanzitutto, un esempio:

SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER () as total_revenue
FROM
sales

Nota che non ne abbiamo GROUP BY e il nostro set di dati rimane intatto. Eppure siamo riusciti a ottenere la somma di tutti i ricavi. Prima di approfondire il funzionamento, parliamo rapidamente della sintassi completa prima di iniziare ad acquisire le nostre conoscenze.

La sintassi è questa:

SUM((some_column)) OVER (PARTITION BY (some_columns) ORDER BY (some_columns))

Separando ogni sezione, questo è ciò che abbiamo:

• Una funzione di aggregazione o finestra: SUM, AVG, MAX, RANK, FIRST_VALUE
• IL OVER parola chiave che dice che questa è una funzione finestra
• IL PARTITION BY sezione, che definisce il gruppi
• IL ORDER BY sezione che definisce se si tratta di una funzione in esecuzione (ne parleremo più avanti)

Non insistere ancora sul significato di ciascuno di questi, poiché diventerà chiaro quando esamineremo gli esempi. Per ora sappi che per definire una funzione finestra utilizzeremo il file OVER parola chiave. E come abbiamo visto nel primo esempio, questo è l'unico requisito.

Passando a qualcosa di realmente utile, applicheremo ora un gruppo nella nostra funzione. Il calcolo iniziale verrà mantenuto per mostrartelo possiamo eseguire più di una funzione finestra contemporaneamenteil che significa che possiamo eseguire diverse aggregazioni contemporaneamente nella stessa query, senza richiedere sottoquery.

SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY region) as region_total,
SUM(revenue) OVER () as total_revenue
FROM sales

Come detto, noi usiamo il PARTITION BY per definire i nostri gruppi (finestre) utilizzati dalla nostra funzione di aggregazione! Quindi, mantenendo intatto il nostro set di dati abbiamo:

• Le entrate totali per ciascuna regione
• Le entrate totali per l'intero set di dati

Inoltre, non siamo limitati a un singolo gruppo. Simile a GROUP BY possiamo suddividere i nostri dati su Regione e Trimestre, ad esempio:

SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY 
region, 
date_trunc('quarter', date)
) AS region_quarterly_revenue
FROM sales

Nell'immagine vediamo che gli unici due punti dati per la stessa regione e trimestre sono stati raggruppati insieme!

A questo punto spero che sia chiaro come possiamo considerare questo come un fare a GROUP BY ma sul posto, senza ridurre il numero di righe nel nostro set di dati. Naturalmente, non sempre lo vogliamo, ma non è così raro vedere query in cui qualcuno raggruppa i dati e poi li unisce nuovamente nel set di dati originale, complicando quella che potrebbe essere una funzione di finestra singola.

Passando al ORDER BY parola chiave. Questo definisce una funzione di finestra in esecuzione. Probabilmente hai sentito parlare di somma parziale una volta nella vita, ma in caso contrario, dovremmo iniziare con un esempio per rendere tutto chiaro.

SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (ORDER BY id) as running_total
FROM sales

Quello che succede qui è che abbiamo sommato, riga per riga, le entrate con tutti i valori precedenti. Ciò è stato fatto seguendo l'ordine del id colonna, ma avrebbe potuto essere qualsiasi altra colonna.

Questo esempio specifico non è particolarmente utile perché stiamo sommando mesi casuali e due regioni, ma utilizzando ciò che abbiamo imparato ora possiamo trovare le entrate cumulative per regione. Lo facciamo applicando la somma parziale all'interno di ciascun gruppo.

SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY region ORDER BY date) as running_total
FROM sales

Prenditi il ​​tempo per assicurarti di capire cosa è successo qui:

• Per ciascuna regione analizziamo mese per mese e sommeremo le entrate
• Una volta terminato il lavoro per quella regione, passiamo a quella successiva, cominciando da zero e spostandoci di nuovo con i mesi!

È piuttosto interessante notare qui che quando scriviamo queste funzioni in esecuzione abbiamo il “contesto” di altre righe. Ciò che intendo è che per ottenere la somma parziale ad un certo punto, dobbiamo conoscere i valori precedenti per le righe precedenti. Ciò diventa più evidente quando apprendiamo che possiamo scegliere manualmente quante righe prima/dopo vogliamo aggregare.

SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) 
AS useless_sum
FROM
sales

Per questa query abbiamo specificato che per ogni riga vogliamo guardare una riga dietro e due righe davanti, quindi ciò significa che otteniamo la somma di quell'intervallo! A seconda del problema che stai risolvendo, questo può essere estremamente potente in quanto ti dà il controllo completo su come raggruppare i tuoi dati.

Infine, un'ultima funzione che voglio menzionare prima di passare a un esempio più difficile è the RANK funzione. Questo viene chiesto spesso nelle interviste e la logica dietro è la stessa di tutto ciò che abbiamo imparato finora.

SELECT
*,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) as rank,
RANK() OVER (ORDER BY revenue DESC) as overall_rank
FROM
sales
ORDER BY region, revenue DESC

Proprio come prima, abbiamo usato ORDER BY per specificare l'ordine in cui cammineremo, riga per riga, e PARTITION BY per specificare i nostri sottogruppi.

La prima colonna classifica ciascuna riga all'interno di ciascuna regione, il che significa che avremo più “classifica uno” nel set di dati. Il secondo calcolo è la classificazione in tutte le righe del set di dati.

Questo è un problema che si presenta di tanto in tanto e per risolverlo su SQL è necessario un utilizzo intenso delle funzioni della finestra. Per spiegare questo concetto utilizzeremo un set di dati diverso contenente timestamp e misurazioni della temperatura. Il nostro obiettivo è riempire le righe delle misurazioni della temperatura mancanti con l'ultimo valore misurato.

Ecco cosa ci aspettiamo di avere alla fine:

Prima di iniziare, voglio solo menzionare che se utilizzi Panda puoi risolvere questo problema semplicemente eseguendo df.ffill() ma se utilizzi SQL il problema diventa un po' più complicato.

Il primo passo per risolvere questo problema è, in qualche modo, raggruppare i NULL con il precedente valore non nullo. Potrebbe non essere chiaro come lo facciamo, ma spero che sia chiaro che ciò richiederà un funzione di corsa. Ciò significa che è una funzione che “camminerà riga per riga”, sapendo quando raggiungiamo un valore nullo e quando raggiungiamo un valore non nullo.

La soluzione è usare COUNT e, più specificamente, contare i valori delle misurazioni della temperatura. Nella query seguente eseguo sia un normale conteggio corrente che un conteggio sui valori della temperatura.

SELECT
*,
COUNT() OVER (ORDER BY timestamp) as normal_count,
COUNT(temperature) OVER (ORDER BY timestamp) as group_count
from sensor

• Nel primo calcolo abbiamo semplicemente contato ogni riga in modo crescente
• Sul secondo abbiamo contato ogni valore di temperatura che abbiamo visto, senza contare quando era NULL

IL normal_count la colonna è inutile per noi, volevo solo mostrare che corsa COUNT sembrava. Il nostro secondo calcolo però, il group_count ci avvicina alla risoluzione del nostro problema!

Si noti che questo modo di contare garantisce che venga conteggiato il primo valore, subito prima dell'inizio dei NULL, e quindi, ogni volta che la funzione vede un null, non accade nulla. Ciò garantisce che stiamo “contrassegnando” ogni valore nullo successivo con lo stesso conteggio che avevamo quando abbiamo smesso di effettuare misurazioni.

Andando avanti, ora dobbiamo copiare il primo valore che è stato taggato in tutte le altre righe all'interno dello stesso gruppo. Questo significa che per il gruppo 2 devono essere tutti riempiti con il valore 15.0.

Riesci a pensare a una funzione ora che possiamo usare qui? C'è più di una risposta a questo, ma, ancora una volta, spero che almeno sia chiaro che ora stiamo guardando una semplice aggregazione di finestre con PARTITION BY .

SELECT
*,
FIRST_VALUE(temperature) OVER (PARTITION BY group_count) as filled_v1,
MAX(temperature) OVER (PARTITION BY group_count) as filled_v2
FROM (
SELECT
*,
COUNT(temperature) OVER (ORDER BY timestamp) as group_count
from sensor
) as grouped
ORDER BY timestamp ASC

Possiamo usarli entrambi FIRST_VALUE O MAX per ottenere ciò che desideriamo. L'unico obiettivo è ottenere il primo valore non nullo. Poiché sappiamo che ogni gruppo contiene un valore non nullo e una serie di valori nulli, entrambe queste funzioni funzionano!

Questo esempio è un ottimo modo per esercitarsi con le funzioni della finestra. Se vuoi una sfida simile prova ad aggiungere due sensori e poi inserisci i valori con la lettura precedente di quel sensore. Qualcosa di simile a questo:

Potresti farlo? Non utilizza nulla che non abbiamo imparato qui finora.

Ormai sappiamo tutto ciò di cui abbiamo bisogno su come funzionano le funzioni delle finestre in SQL, quindi facciamo solo un breve riepilogo!

Questo è ciò che abbiamo imparato:

• Noi usiamo il OVER parola chiave per scrivere funzioni della finestra
• Noi usiamo PARTITION BY per specificare i nostri sottogruppi (finestre)
• Se forniamo solo il file OVER() la parola chiave la nostra finestra è l'intero set di dati
• Noi usiamo ORDER BY quando vogliamo avere una funzione in esecuzione, il che significa che il nostro calcolo cammina riga per riga
• Le funzioni della finestra sono utili quando vogliamo raggruppare i dati per eseguire un'aggregazione ma vogliamo mantenere il nostro set di dati così com'è