È particolarmente utile quando è necessario calcolare le aggregazioni o le classifiche per gruppi di righe senza effettivamente comprimere l'intero set di risultati.
Unisciti a noi in questo tutorial mentre impariamo tutto ciò che c'è da sapere per iniziare a lavorare con la clausola OVER.
Requisiti:
Prima di approfondire le funzionalità e il funzionamento della clausola OVER, assicurati di conoscere le nozioni di base di SQL. Presumiamo inoltre che tu abbia accesso a un database che puoi utilizzare per testare le tue conoscenze.
Nel nostro caso utilizzeremo il database MySQL con il database di esempio Sakila. Assicurati solo di disporre di autorizzazioni sufficienti e che il motore del database supporti le funzioni della finestra.
Sintassi:
Come accennato in precedenza, nella maggior parte dei casi utilizziamo principalmente la clausola OVER insieme alle funzioni della finestra.
Pertanto, possiamo esprimere la sintassi della clausola nel modo seguente:
[PARTIZIONE PER espressione_partizione, ...]
[ORDINA PER espressione_ordinamento [ASC | DESC], ...]
[specifica_telaio]
)
Nella sintassi data, possiamo scomporre ciascun componente come segue:
-
– Si riferisce alla funzione finestra che desideriamo applicare su una specifica finestra di righe come SUM(), AVG(), ROW_NUMBER(), RANK, ecc. - Espressione: specifica una colonna o un'espressione a cui viene applicata la funzione finestra.
- PARTITION BY – Questa è una clausola opzionale che divide il set di risultati in partizioni in cui ciascuna partizione è come un'unità separata in cui viene applicata la funzione. Le righe all'interno della stessa partizione condividono gli stessi valori nelle colonne specificate.
- ORDER BY – Specifica l'ordine in cui vengono elaborate le righe in ciascuna partizione.
- frame_specification: questa è una clausola facoltativa che definisce il frame di righe all'interno della partizione. Le specifiche comuni dei frame includono RIGHE TRA
E o INTERVALLO TRA E
Detto questo, esploriamo alcuni esempi pratici su come utilizzarlo.
Esempio:
Dimostriamo come utilizzare la clausola utilizzando il database di esempio Sakila. Consideriamo un esempio in cui dobbiamo determinare le entrate totali per ciascuna categoria di film.
Possiamo utilizzare la funzione della finestra somma con la clausola OVER e una serie di istruzioni join come mostrato nell'esempio seguente:
SELEZIONAREnome_categoria AS nome_categoria,
titolo_film AS titolo_film,
film.tariffa_noleggio,
SUM(pagamento.importo) OVER (PARTIZIONE PER categoria.nome) AS entrate_totali
DA
film
GIUNTURA
categoria_film attiva
film.film_id = film_category.film_id
GIUNTURA
categoria ATTIVA
film_category.category_id = categoria.category_id
GIUNTURA
inventario attivo
film.film_id = inventario.film_id
GIUNTURA
noleggio attivo
inventario.inventario_id = noleggio.inventario_id
GIUNTURA
pagamento ATTIVO
noleggio.noleggio_id = pagamento.noleggio_id
ORDINATO DA
Nome della categoria,
titolo.film;
Nella query specificata, iniziamo selezionando il titolo del film, la tariffa di noleggio e utilizziamo l'espressione somma (pagamento.importo su partizione per categoria.nome) per determinare la somma di ciascuna partizione di categoria in base al nome della categoria.
Dobbiamo utilizzare la clausola PARTITION BY per garantire che il calcolo della somma ricominci da ogni categoria univoca.
L'output risultante è il seguente:
Ecco qua!
Conclusione
In questo esempio abbiamo esplorato i fondamenti dell'utilizzo della clausola OVER in SQL. Questa non è una clausola di base e richiede una precedente familiarità con altre funzionalità SQL.