0Pricing
Coding Interview Prep · Lezione

Restituire in modo affidabile le righe Top-N

Capire perché ORDER BY più LIMIT può produrre risultati non deterministici senza un criterio di spareggio

Restituire in modo affidabile le righe Top-N è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 3 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 Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.

Il bug nascosto nelle query Top-N

«Mi dia i 5 dipendenti con lo stipendio più alto» sembra una richiesta semplice: ORDER BY salary DESC LIMIT 5. Tuttavia, gli intervistatori inseriscono una trappola. Cosa succede se sei persone condividono lo stesso stipendio al limite? E se molte righe sono a pari merito?

Il problema fondamentale è il determinismo: quando la chiave di ordinamento contiene pari merito, LIMIT interrompe il risultato in modo arbitrario e le righe esatte restituite possono cambiare tra un'esecuzione e l'altra. Questa lezione rende affidabili le query Top-N.

Perché ORDER BY + LIMIT può essere non deterministico

Si considerino stipendi per i quali le posizioni 4, 5 e 6 corrispondono tutte a 50000. ORDER BY salary DESC LIMIT 5 deve restituire esattamente 5 righe, quindi conserva due delle tre righe a pari merito e ne esclude una, ma non è definito quali due.

Eseguendo la query due volte, oppure dopo che l'ottimizzatore ha modificato i piani di esecuzione, si potrebbero ottenere persone diverse. Questo non determinismo è il bug che gli intervistatori vogliono che individuiate.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Correzione 1: aggiungere un criterio di spareggio univoco

La correzione più semplice consiste nel rendere totale l'ordinamento aggiungendo una colonna univoca, generalmente la chiave primaria. Ora nessuna coppia di righe ha la stessa chiave completa, quindi il punto di interruzione è deterministico e riproducibile.

Questo non modifica gli stipendi visualizzati, ma rende stabile tra le esecuzioni la scelta delle righe a pari merito.

SELECT id, name, salary
FROM employees
ORDER BY salary DESC, id ASC
LIMIT 5;

Correzione 2: includere tutti i pari merito con WITH TIES

A volte il requisito è «includere tutte le persone a pari merito con il limite», non restituire esattamente N righe. SQL standard e SQL Server offrono WITH TIES, che restituisce le righe aggiuntive il cui valore di ORDER BY corrisponde a quello dell'ultima riga.

Se il quinto stipendio è condiviso da tre persone, vengono restituite 7 righe. Si noti che WITH TIES richiede un ORDER BY.

SELECT name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

Chiarire prima il requisito

Prima di scrivere il codice, chieda all'intervistatore: «Se ci sono pari merito al limite, desidera esattamente N righe o tutte le righe a pari merito?» Questa singola domanda di chiarimento dimostra esperienza.

  • Esattamente N, in modo stabile: aggiungere un criterio di spareggio univoco.
  • Includere tutti i pari merito: usare WITH TIES o RANK.
  • Valori distinti: usare DENSE_RANK.

L'approccio portabile con le funzioni finestra

Molti motori non supportano WITH TIES. Il modello portabile e potente consiste nell'utilizzare una funzione finestra di ordinamento in una sottoquery o in una CTE, quindi filtrare in base al rango. ROW_NUMBER restituisce esattamente N righe con una chiave di ordinamento deterministica.

È necessario racchiudere la funzione finestra, perché non è possibile farvi riferimento direttamente in WHERE.

SELECT name, salary
FROM (
  SELECT name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 5;

Usare RANK per mantenere i pari merito

Sostituisca ROW_NUMBER con RANK quando desidera mantenere tutte le righe a pari merito e lasciare lacune nella numerazione. Se tre righe sono a pari merito in quarta posizione, ricevono tutte il rango 4 e il rango successivo è 7.

Filtrando con rank <= 5 si restituisce ogni riga nelle prime cinque posizioni di stipendio, inclusi i pari merito.

SELECT name, salary
FROM (
  SELECT name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 5;

DENSE_RANK per i primi N valori distinti

«I primi 3 livelli di stipendio» (non le prime 3 persone) indica valori distinti. DENSE_RANK assegna lo stesso rango ai pari merito e non salta numeri, quindi dense_rnk <= 3 restituisce tutte le persone che ricevono uno dei tre stipendi distinti più alti.

Sapere quale funzione di ordinamento corrisponde a ciascuna formulazione è un elemento distintivo classico.

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
  FROM employees
) ranked
WHERE drnk <= 3;

Caso speciale Top-1

Per la singola riga più alta, ORDER BY ... LIMIT 1 funziona, ma comporta comunque il rischio di escludere i pari merito. Se desidera ogni riga che contiene il valore massimo, lo confronti con il massimo restituito da una sottoquery oppure usi RANK() = 1.

La forma con la sottoquery del massimo è chiara e funziona in qualsiasi dialetto.

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Confronto tra gli approcci

Riepilogo degli strumenti da utilizzare per ottenere Top-N affidabili:

  • LIMIT + criterio di spareggio univoco: esattamente N righe, stabile e semplice.
  • FETCH ... WITH TIES: esattamente N righe più i pari merito al limite, secondo lo standard SQL.
  • ROW_NUMBER: esattamente N righe, deterministico e completamente portabile.
  • RANK: le prime N posizioni, inclusi tutti i pari merito.
  • DENSE_RANK: i primi N valori distinti.

Anteprima dei Top-N per gruppo

L'approccio con le funzioni finestra si generalizza molto bene. Aggiunga PARTITION BY per ottenere i primi N elementi all'interno di ogni gruppo, ad esempio i 2 dipendenti con lo stipendio più alto per reparto. Dopo il partizionamento si applica lo stesso filtro rn <= n.

I Top-N per gruppo sono tra i problemi più frequenti nei colloqui tecnici reali e si basano esattamente sul modello appena appreso.

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (PARTITION BY department
                            ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 2;

Controllo rapido

Abbini il requisito alla funzione corretta.

Riepilogo

Per restituire Top-N in modo affidabile:

  • ORDER BY ... LIMIT da solo è non deterministico quando la chiave di ordinamento contiene pari merito.
  • Aggiunga un criterio di spareggio univoco per ottenere risultati stabili con esattamente N righe.
  • Usi WITH TIES o RANK per mantenere i pari merito al limite.
  • Usi DENSE_RANK per i primi N valori distinti.
  • Chiarisca sempre se l'intervistatore desidera esattamente N righe o tutti i pari merito.

Domande Frequenti

La lezione «Restituire in modo affidabile le righe Top-N» è gratuita?

Sì — il testo completo di «Restituire in modo affidabile le righe Top-N» è 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 Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Restituire in modo affidabile le righe Top-N»?

Capire perché ORDER BY più LIMIT può produrre risultati non deterministici senza un criterio di spareggio Eserciti Coding 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 Coding Interview Prep?

Non è richiesta alcuna esperienza precedente. Coding 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 3 di 4.

Quanto tempo richiede la lezione «Restituire in modo affidabile le righe Top-N»?

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 Coding Interview Prep?

Sì. Ogni lezione Coding 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

  1. Ordinamento su più colonne e posizione dei NULL
  2. LIMIT, OFFSET e FETCH FIRST
  3. Restituire in modo affidabile le righe Top-N
  4. Ordinare per espressioni e alias
← Torna a Coding Interview Prep