SQL Interview Prep · Lezione

OVER, PARTITION BY e ORDER BY

Anatomia di una specifica di finestra e modo in cui le partizioni reimpostano il calcolo

OVER, PARTITION BY e ORDER BY è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.

Perché gli intervistatori scelgono le funzioni finestra

Una funzione finestra esegue un calcolo su un insieme di righe correlate alla riga corrente, senza comprimerle come fa GROUP BY. Questa singola caratteristica spiega perché gli intervistatori le apprezzano: si mantengono tutte le righe di dettaglio e si ottiene comunque un aggregato, una posizione o un totale progressivo accanto a ciascuna di esse.

GROUP BY restituisce una riga per gruppo.
Una funzione finestra restituisce ogni riga di input, con una colonna calcolata aggiuntiva.

Quando un intervistatore dice "mostrare ogni dipendente e lo stipendio medio del suo reparto sulla stessa riga", sta verificando se si sceglie una funzione finestra invece di un self-join.

Anatomia della clausola OVER

Ogni funzione finestra è seguita da una clausola OVER (...). La clausola comprende tre parti opzionali; nominarle con precisione fa una buona impressione agli intervistatori:

PARTITION BY — divide le righe in gruppi; la funzione ricomincia in ciascun gruppo.
ORDER BY — ordina le righe all'interno di ogni partizione, come richiesto per le classifiche e i totali progressivi.
frame — limita le righe che alimentano il calcolo (ROWS/RANGE).

Un OVER () vuoto tratta l'intero insieme di risultati come un'unica partizione.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Finestra e aggregato: stessa funzione, risultato diverso

La stessa identica funzione di aggregazione si comporta in modo diverso quando viene usata come funzione finestra. Si confrontino concettualmente le due query riportate di seguito.

AVG(salary) con GROUP BY department restituisce una riga per reparto.
AVG(salary) OVER (PARTITION BY department) restituisce ogni dipendente, indicando per ciascuno la media del reparto.

Suggerimento per il colloquio: sottolinei che la versione con la finestra non richiede GROUP BY e non elimina le righe di dettaglio duplicate.

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY: reimpostare il calcolo

PARTITION BY sta alle funzioni finestra come GROUP BY sta alle aggregazioni, con la differenza che non accorpa le righe. Ogni valore distinto della partizione ha il proprio calcolo indipendente.

Nell'esempio, il numero di riga riparte da 1 per ogni reparto. Senza PARTITION BY, la numerazione proseguirebbe senza interruzioni tra tutti gli impiegati.

È possibile creare partizioni in base a una o più colonne.
L'assenza di PARTITION BY equivale a un'unica enorme partizione (l'intero insieme).

SELECT
  department,
  name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

ORDER BY all'interno di OVER

Il ORDER BY all'interno di OVER non è uguale al ORDER BY finale della query. Definisce solo la sequenza delle righe all'interno di ogni partizione su cui opera la funzione.

Le funzioni di ranking (ROW_NUMBER, RANK) lo richiedono: hanno bisogno di un ordine in base al quale assegnare il ranking.
Le semplici aggregazioni su una partizione non ne hanno bisogno, a meno che non si desideri un calcolo progressivo.

Nei colloqui è comune confondere il ORDER BY della finestra con l'ordine di presentazione dell'output.

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

Combinare PARTITION BY e ORDER BY

La classica finestra per il ranking combina entrambi: PARTITION BY raggruppa, poi ORDER BY ordina le righe all'interno di ogni gruppo.

La specifica seguente significa: "All'interno di ogni reparto, ordini gli impiegati per stipendio decrescente e assegni loro un numero." La persona con lo stipendio più alto di ogni reparto riceve il numero di riga 1.

Questa singola specifica è alla base dei problemi più comuni sui colloqui relativi alle funzioni finestra, incluso il problema dei primi N elementi per gruppo.

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

ORDER BY modifica il comportamento delle aggregazioni

Ecco un aspetto sottile su cui gli intervistatori amano verificare la preparazione: aggiungere ORDER BY a una finestra di aggregazione la trasforma in un calcolo progressivo, perché entra in gioco una cornice implicita ("dall'inizio della partizione alla riga corrente").

SUM(x) OVER (PARTITION BY g) → lo stesso totale del gruppo su ogni riga.
SUM(x) OVER (PARTITION BY g ORDER BY d) → un totale progressivo fino alla riga corrente.

Capire che ORDER BY aggiunge implicitamente una cornice distingue i candidati di livello intermedio da quelli junior.

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

Dove sono consentite le funzioni finestra

Le funzioni finestra possono comparire solo nell'elenco SELECT e nella clausola ORDER BY. Non sono consentite in WHERE, GROUP BY o HAVING.

Il motivo è legato all'ordine logico di esecuzione: le funzioni finestra vengono valutate dopo l'esecuzione di WHERE, GROUP BY e HAVING. Le righe sono già state selezionate prima che la finestra possa elaborarle.

Per questo, per filtrare in base a un ranking è necessaria una subquery o una CTE: un punto che verrà trattato completamente in una lezione successiva.

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Più funzioni finestra in un'unica query

È possibile usare diverse funzioni finestra nello stesso SELECT, ciascuna con una propria specifica o con una specifica condivisa. Il database le calcola in un'unica scansione dei dati partizionati.

È utile nei colloqui quando servono contemporaneamente il ranking e la media del reparto. Se due funzioni condividono una specifica, alcuni dialetti consentono di assegnarle un nome con una clausola WINDOW, evitando di ripeterla.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w  AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Esempio svolto: stipendio e media del reparto

Una domanda frequente per un analista è: "Elencare ogni impiegato con il relativo stipendio, la media del reparto e la differenza." Una sola espressione finestra svolge il lavoro più impegnativo; per il resto basta l'aritmetica.

Noti che non c'è alcun GROUP BY e che ogni riga degli impiegati rimane nel risultato. dept_avg viene ripetuto per tutti gli impiegati dello stesso reparto: è proprio questo che rende possibile il confronto riga per riga.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Errori comuni a cui prestano attenzione gli intervistatori

Quando si parla di funzioni finestra, eviti queste trappole:

Inserire una funzione finestra in WHERE o HAVING: è illegale; usi una subquery.
Dimenticare ORDER BY in una funzione di ranking: i risultati diventano arbitrari.
Supporre che PARTITION BY riduca il numero di righe: non lo fa mai.
Confondere il ORDER BY della finestra con l'ordine finale dell'output.
Aggiungere ORDER BY a una finestra di aggregazione senza rendersi conto che è diventata un totale progressivo.

Verifica rapida

Verifichi la sua comprensione della specifica della finestra.

Riepilogo: la specifica della finestra

Ora conosce l'anatomia di OVER (...):

Le funzioni finestra mantengono ogni riga mentre eseguono calcoli sulle righe correlate.
PARTITION BY raggruppa e reimposta il calcolo; non elimina mai le righe.
ORDER BY ordina le righe all'interno di una partizione; le funzioni di ranking lo richiedono e trasforma le aggregazioni in calcoli progressivi.
Le funzioni finestra sono consentite solo in SELECT e ORDER BY: mai in WHERE/HAVING.

Ora assegnerà numeri di sequenza deterministici con ROW_NUMBER.

Domande Frequenti

La lezione «OVER, PARTITION BY e ORDER BY» è gratuita?

Sì — il testo completo di «OVER, PARTITION BY e ORDER BY» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.

Cosa imparerò in «OVER, PARTITION BY e ORDER BY»?

Anatomia di una specifica di finestra e modo in cui le partizioni reimpostano il calcolo Eserciti SQL Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Interview Prep?

Non è richiesta alcuna esperienza precedente. SQL Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «OVER, PARTITION BY e ORDER BY»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Interview Prep?

Sì. Ogni lezione SQL Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Interview Prep