La clausola ORDER BY nelle funzioni SQL Window

In questo articolo viene fornita una panoramica di ORDER BY nelle funzioni di finestra. Imparerete come si differenzia da PARTITION BY e dal normale ORDER BY, il tutto corredato da esempi e spiegazioni.

Se volete creare rapporti che vadano oltre le semplici aggregazioni, avete bisogno delle funzioni finestra di SQL. Le funzioni finestra consentono di creare classifiche, calcolare totali e medie mobili e trovare la differenza tra le righe. Per utilizzare le funzioni finestra in modo efficace, è necessario comprendere il ruolo della clausola ORDER BY. Non solo cambia il comportamento delle funzioni finestra, ma alcune funzioni finestra non vengono eseguite senza ORDER BY.

Questo articolo è rivolto a chi ha già una conoscenza generale delle funzioni di finestra. Se non le conoscete, vi consiglio vivamente di seguire il nostro corso Window Functions (Funzioni Finestra) . Vi fornirà una conoscenza completa delle funzioni di finestra, comprese le funzioni di classificazione, le funzioni di analisi e le clausole ORDER BY e PARTITION BY. Il corso presenta 218 sfide di codifica che attendono di essere risolte; potete trovarne altre nel set di esercitazioni di Window Functions (Funzioni Finestra) .

Anche se conoscete le funzioni SQL a finestra, vi consigliamo di tenere a portata di mano il nostro Window Functions (Funzioni Finestra) Cheat Sheet per una rapida consultazione.

Cosa sono le Window Functions (Funzioni Finestra)?

Le funzioni finestra in SQL eseguono operazioni su un riquadro di finestra, che consiste nella riga corrente e nelle righe ad essa correlate. A differenza delle funzioni aggregate, le funzioni finestra non comprimono le singole righe, ma aggiungono una colonna a ogni riga con il risultato della funzione. Ciò significa che le funzioni finestra consentono di visualizzare contemporaneamente dati singoli e aggregati.

Sintassi

La sintassi delle funzioni finestra SQL è illustrata di seguito:

window_function OVER ([PARTITION BY column_name] [ORDER BY column_name ASC|DESC])

Ecco una breve descrizione di ogni parte della sintassi:

• window_function: La funzione finestra che si desidera utilizzare.
• OVER(): Una clausola obbligatoria per la creazione di una funzione finestra.
• PARTITION BY: Una clausola opzionale che suddivide i dati.
• ORDER BY: Una clausola opzionale che ordina i dati all'interno della finestra.

Che cos'è la clausola ORDER BY in Window Functions (Funzioni Finestra)?

ORDER BY (insieme a PARTITION BY) è una parte fondamentale di molte funzioni finestra. ORDER BY nella funzione finestra ordina le righe all'interno della cornice della finestra. Definisce l'ordine di esecuzione dei calcoli della funzione finestra.

ORDER BY può ordinare i dati all'interno di una finestra in modo ascendente (da A a Z, da 1 a 10) o discendente (da Z ad A, da 10 a 1). È possibile ordinare i dati di testo in ordine alfabetico o alfabetico inverso, i dati numerici dal più basso al più alto (o viceversa) e i dati di data/ora dal più vecchio al più recente (o dal più recente al più vecchio).

Esempio: ORDER BY con una funzione finestra di classificazione

Vediamo un esempio di come ORDER BY nelle funzioni finestra influenza l'esecuzione delle query.

La query seguente classifica i dati della tabella product_sales in base alle vendite in ordine decrescente, cioè dalle vendite più alte a quelle più basse.

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales DESC) AS ranking
FROM product_sales; 

DENSE_RANK() è una delle funzioni finestra di classificazione. Essa classifica l'intero set di dati dalle vendite più alte a quelle più basse, specificate in ORDER BY.

La tabella originale product_sales originale ha questo aspetto:

Pertanto, il codice precedente classifica le righe del set di dati in modo decrescente: dalla più alta alla più bassa.

Questo è l'output della query, con i ranghi mostrati in una colonna separata:

Ora, la classifica sarebbe significativamente diversa se si sostituisse DESC con ASC in ORDER BY, come mostrato di seguito:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales ASC) AS ranking
FROM product_sales; 

Ora la classifica viene eseguita dalle vendite più basse a quelle più alte:

ORDER BY e PARTITION BY in Window Functions (Funzioni Finestra)

Sappiamo cosa fa ORDER BY nelle funzioni della finestra. Che dire di PARTITION BY? Si tratta di una clausola opzionale che divide i dati in sottoinsiemi basati su una o più categorie. Lo si fa specificando le colonne nella clausola PARTITION BY in questo modo: PARTITION BY product_name. Ne dimostrerò il funzionamento nella sezione seguente.

Senza PARTITION BY, ORDER BY ordina i dati nell'intero insieme di risultati. Se invece lo si usa con PARTITION BY, i dati verranno ordinati separatamente all'interno di ogni partizione.

Esempio: Uso di ORDER BY con PARTITION BY in una funzione finestra

Se utilizzato con PARTITION BY, ORDER BY ordina i dati all'interno di ogni partizione.

L'uso di ORDER BY con la funzione finestra SUM() produce una somma cumulativa (cioè la somma del valore della riga corrente più tutte le righe precedenti nella partizione). Ad esempio, il codice seguente calcola la somma cumulativa delle vendite per nome di prodotto (come specificato in PARTITION BY) dalla data più vecchia a quella più recente (come specificato in ORDER BY).

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name ORDER BY date ASC) AS cumulative_sum
FROM product_sales; 

La query dividerà innanzitutto il set di dati in partizioni per nome di prodotto. Quindi i valori all'interno di ogni partizione saranno ordinati dalla data più vecchia a quella più recente, specificata in ORDER BY. Quindi la funzione SUM() window calcolerà il totale cumulativo sommando le vendite della data corrente con le vendite di tutte le vendite precedenti all'interno della partizione.

Ecco l'output, che mostra ogni riga e le vendite cumulative per ogni prodotto:

Se si rimuove ORDER BY dal codice, come mostrato di seguito...

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name) AS cumulative_sum
FROM product_sales; 

... il codice restituirà la somma delle vendite totali per ciascun prodotto. In altre parole, omettendo semplicemente ORDER BY da una funzione finestra, si perde la capacità di calcolare in modo cumulativo. Senza ORDER BY, tutte le righe della partizione costituiscono la cornice della finestra.

ORDER BY in Window Functions (Funzioni Finestra) vs. ORDER BY regolare

Quando si parla di ordine regolare, si intende l'ordine standard di ORDER BY alla fine della query. In che modo è diverso da ORDER BY in una funzione finestra?

Un ORDER BY regolare ordina l'output di una query, mentre ORDER BY nelle funzioni finestra ordina una finestra di dati o una partizione di dati.

Esempio: ORDER BY in Window Functions (Funzioni Finestra) e ORDER BY regolare

Questa query utilizza la funzione finestra DENSE_RANK() con PARTITION BY e ORDER BY per classificare le date di vendita di ciascun prodotto:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS day_rank
FROM product_sales;

Si può notare che l'output complessivo non è ordinato dalle vendite più alte a quelle più basse. L'ordinamento decrescente delle date viene applicato solo all'interno di ogni partizione.

Se si desidera che l'output sia ordinato in modo decrescente in base alle vendite, è necessario farlo esplicitamente aggiungendo ORDER BY alla fine della query:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS sales_rank_by_product
FROM product_sales
ORDER BY sales DESC;

Ora si ottiene l'output ordinato per vendite e data. È possibile vedere come la classifica delle vendite di una data si confronta con le vendite dello stesso prodotto in altre date.

Window Functions (Funzioni Finestra) Che richiedono ORDER BY

Come si è visto nell'esempio precedente, le funzioni finestra possono funzionare anche senza ORDER BY. ORDER BY è generalmente considerata una clausola opzionale nelle funzioni finestra.

Ma anche se ORDER BY nelle funzioni finestra è considerata una clausola opzionale, alcune funzioni finestra richiedono ORDER BY per funzionare. In questi casi, ORDER BY diventa obbligatorio; queste funzioni di finestra richiedono un ordine di ordinamento per funzionare correttamente. Queste funzioni sono:

• RANK()
• DENSE_RANK()
• NTILE()
• LEAD()
• LAG()

Cornici di finestra predefinite con e senza ORDER BY

Il comportamento di una funzione finestra cambia a seconda che sia scritta con o senza ORDER BY. Più precisamente, la presenza o l'assenza di ORDER BY influisce sulla cornice predefinita della finestra.

Se non c'è ORDER BY, la cornice predefinita della finestra include la riga corrente e tutte le righe precedenti e successive. In altre parole, tutte le righe della partizione sono incluse. Lo abbiamo visto con l'esempio della somma cumulativa: senza ORDER BY, il riquadro della finestra si riferisce all'intera partizione e la somma diventa la somma totale.

Se è presente ORDER BY, la finestra include il valore corrente e tutti i valori precedenti. Lo abbiamo visto con l'esempio della somma cumulativa: con ORDER BY, il riquadro della finestra include tutte le righe precedenti alla riga corrente e la riga corrente.

Se non si gradisce la finestra predefinita, è possibile definirla esplicitamente utilizzando le parole chiave ROWS e RANGE.

Utilizzo di ORDER BY in Window Functions (Funzioni Finestra)

Ecco come funziona: ORDER BY - a volte obbligatoria, a volte facoltativa - è la clausola che fa e a volte fa fallire le funzioni delle finestre.

Tuttavia, sapere tutto su ORDER BY nelle funzioni finestra non serve a nulla se non è possibile utilizzarlo nelle query. Verificate quindi quanto appreso qui risolvendo le sfide di codifica del nostro corso sulle funzioni finestra e del set di esercitazioni Window Functions (Funzioni Finestra) .

Per ulteriori esercizi, risolvete questi 11 esercizi sulle funzioni finestra e rispondete alle 10 migliori domande di intervista sulle funzioni finestra. Buon apprendimento!