SQL Interview Prep · Lezione

ROW_NUMBER per una sequenza univoca

Assegnare un numero di riga deterministico all'interno di ogni partizione

ROW_NUMBER per una sequenza univoca è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 2 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.

Cosa garantisce ROW_NUMBER

ROW_NUMBER() assegna a ogni riga della partizione un intero univoco e consecutivo, a partire da 1, seguendo il ORDER BY della finestra. Nessuna coppia di righe riceve lo stesso numero, anche quando i relativi valori di ordinamento sono identici.

È proprio questa unicità che spinge gli intervistatori a usarlo per risolvere i problemi di selezione di "una riga per gruppo" e di deduplicazione. Le altre funzioni di ranking (RANK, DENSE_RANK) non garantiscono l'unicità in caso di parità.

Query minima con ROW_NUMBER

La forma più semplice assegna un numero all'intero insieme di risultati secondo un ordine scelto. ROW_NUMBER richiede sempre un ORDER BY all'interno di OVER: senza di esso la numerazione sarebbe arbitraria e la maggior parte dei motori la rifiuterebbe o genererebbe un avviso.

Qui, se si ordina per data di assunzione decrescente, l'impiegato assunto più di recente riceve il numero di riga 1.

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date DESC) AS rn
FROM employees;

Numerazione all'interno delle partizioni

Aggiunga PARTITION BY per numerare le righe indipendentemente all'interno di ogni gruppo. Il contatore riparte da 1 per ogni nuovo valore della partizione.

Nell'esempio, ogni reparto ha la propria sequenza 1, 2, 3, ordinata per stipendio. Il dipendente con lo stipendio più alto di ogni reparto riceve il numero 1: è la base del modello dei primi N elementi per gruppo.

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

ROW_NUMBER e i pareggi: non ammette pareggi

Punto fondamentale nei colloqui: quando due righe hanno lo stesso valore di ordinamento, ROW_NUMBER assegna comunque loro numeri diversi. Quale delle due riceva il numero più basso è non deterministico, a meno che non si aggiunga una colonna di spareggio.

RANK assegnerebbe lo stesso ranking alle righe a pari merito.
ROW_NUMBER sceglie arbitrariamente un ordine tra di esse.

Per ottenere risultati riproducibili, aggiunga sempre un criterio di spareggio univoco a ORDER BY.

SELECT
  name,
  salary,
  ROW_NUMBER() OVER (
    ORDER BY salary DESC, employee_id  -- employee_id breaks ties deterministically
  ) AS rn
FROM employees;

La regola dello spareggio deterministico

Se il suo ORDER BY non definisce un ordinamento stretto (nessuna combinazione di colonne è univoca), la numerazione delle righe può cambiare tra un'esecuzione e l'altra, anche sugli stessi dati. Gli intervistatori inseriscono questa trappola nelle domande sulla paginazione e sull'"ultimo record".

Regola generale: aggiunga una chiave primaria o un'altra colonna univoca come ultimo criterio di ordinamento ogni volta che il risultato deve essere stabile.

-- Unstable: many rows can share the same created_at
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)

-- Stable: id guarantees a single deterministic winner
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC)

Esempio svolto: ultimo ordine per cliente

Una domanda classica è: "Restituire l'ordine più recente di ogni cliente." Numeri gli ordini di ciascun cliente per data decrescente, quindi mantenga rn = 1 in una query esterna.

Poiché ROW_NUMBER garantisce esattamente una riga con numero 1 per partizione, otterrà precisamente una riga per cliente, senza duplicati anche se due ordini hanno lo stesso timestamp (il criterio di spareggio risolve il caso).

SELECT customer_id, order_id, order_date, amount
FROM (
  SELECT
    customer_id, order_id, order_date, amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
) t
WHERE rn = 1;

Esempio svolto: deduplicazione delle righe

ROW_NUMBER è lo strumento più semplice per la deduplicazione. Crei una partizione in base alle colonne che definiscono un duplicato, ordini secondo una regola di preferenza, quindi mantenga rn = 1.

Qui consideriamo duplicati le righe con la stessa email e manteniamo quella creata per prima. Tutto ciò che ha rn > 1 è un duplicato che può eliminare o ignorare.

SELECT id, email, created_at
FROM (
  SELECT
    id, email, created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at ASC, id ASC
    ) AS rn
  FROM users
) t
WHERE rn = 1;

ROW_NUMBER per la paginazione

Prima che OFFSET/FETCH diventasse universale, ROW_NUMBER era alla base della paginazione e lo è ancora in SQL Server e in molti processi ETL. Numeri le righe in un ordine stabile, quindi filtra un intervallo di numeri in una query esterna.

Attenzione nei colloqui: la paginazione è corretta solo se l'ordinamento è deterministico; altrimenti la stessa riga può comparire in due pagine oppure può essere completamente saltata.

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
  FROM articles
) t
WHERE rn BETWEEN 21 AND 40;  -- page 2, 20 per page

Assegnare una sequenza per riordinare i dati

A volte serve semplicemente un indice sequenziale, ad esempio per rinumerare le righe dopo un ordinamento, creare un'etichetta da 1 a N o abbinare le righe a una serie generata. Applicare ROW_NUMBER all'ordine desiderato produce una sequenza compatta e ordinata, senza buchi.

È anche il modo per assegnare a un insieme arbitrario e non ordinato una posizione stabile da usare in un'unione successiva.

SELECT
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id) AS leaderboard_position,
  player_id,
  score
FROM scores;

ROW_NUMBER vs COUNT per la "N-esima riga"

Quando le viene chiesto di trovare "il terzo ordine più recente" o "la seconda riga", usi ROW_NUMBER e filtri il valore esatto in una query esterna. Poiché la numerazione è univoca, rn = 3 restituisce esattamente una riga.

Il confronto con RANK è importante: se vuole il secondo valore distinto (ad esempio il secondo stipendio più alto, includendo i pari merito), ROW_NUMBER è lo strumento sbagliato: serve DENSE_RANK, che verrà trattato nella prossima lezione.

SELECT order_id, order_date
FROM (
  SELECT order_id, order_date,
         ROW_NUMBER() OVER (ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders
) t
WHERE rn = 3;  -- exactly the 3rd most recent order

Riepilogo delle insidie

Tenga a mente questi punti anche sotto la pressione di un colloquio:

ROW_NUMBER è sempre univoco e consecutivo all'interno di una partizione.
Richiede un ORDER BY in OVER; senza un criterio di spareggio univoco, in caso di parità i risultati non sono deterministici.
Non può essere filtrato in WHERE: lo racchiuda in una subquery/CTE.
Lo usi per ottenere una riga per gruppo, per la deduplicazione e per la paginazione; usi DENSE_RANK quando i pari merito devono condividere un numero.

Verifica rapida

Come gestisce ROW_NUMBER le righe che hanno lo stesso valore in ORDER BY?

Riepilogo: numerazione deterministica

ROW_NUMBER è lo strumento per assegnare numeri univoci e consecutivi all'interno delle partizioni. Ha imparato a:

Numerare interi insiemi e gruppi per partizione.
Aggiungere un criterio di spareggio univoco per ottenere risultati deterministici e riproducibili.
Risolvere i problemi dell'ultima riga per chiave, della deduplicazione, della paginazione e della N-esima riga filtrando rn in una query esterna.

Ora vedrà come RANK e DENSE_RANK assegnino deliberatamente lo stesso numero ai pari merito e in che modo differiscano nella gestione dei buchi.

Domande Frequenti

La lezione «ROW_NUMBER per una sequenza univoca» è gratuita?

Sì — il testo completo di «ROW_NUMBER per una sequenza univoca» è 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 «ROW_NUMBER per una sequenza univoca»?

Assegnare un numero di riga deterministico all'interno di ogni partizione 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 2 di 4.

Quanto tempo richiede la lezione «ROW_NUMBER per una sequenza univoca»?

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