0Pricing
SQL Interview Prep · Lezione

Trovare le lacune in una sequenza

Rilevare i valori mancanti e l'inizio e la fine di ogni lacuna

Trovare le lacune in una sequenza è una lezione SQL 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 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.

Ora cerchiamo i gap

Finora abbiamo raggruppato le righe in isole. La domanda speculare tipica dei colloqui è: quali valori mancano? Gli intervistatori possono formularla così: «trovi i gap in questa sequenza di ID», «quali numeri di fattura sono stati saltati» oppure «in quali giorni non c'è stata attività».

I gap sono gli spazi vuoti tra le isole. L'idea fondamentale è che di solito non è necessario elencare ogni singolo valore mancante: occorre riportare l'inizio e la fine di ogni intervallo di gap, una rappresentazione molto più compatta e quella che gli intervistatori si aspettano.

Il dataset di esempio con i gap

Riutilizzi i valori presenti 1, 2, 3, 7, 8, 10 dalla tabella seq(n). I gap da riportare sono:

  • Da 4 a 6 (dopo la prima isola, prima di 7)
  • Da 9 a 9 (tra 8 e 10)

Noti che descriviamo un gap come un intervallo: gap_start = ultimo valore presente + 1, gap_end = prossimo valore presente - 1. Questa forma compatta è l'obiettivo della tecnica fondamentale seguente.

CREATE TABLE seq (n INT);
INSERT INTO seq VALUES (1),(2),(3),(7),(8),(10);

L'approccio LEAD ai gap

Il rilevatore di gap più semplice confronta ogni riga con quella successiva usando LEAD. Se il valore successivo è maggiore di quello corrente di più di 1, tra i due esiste un gap.

Per ciascuna riga di questo tipo, il gap inizia da n + 1 e termina a next_n - 1. Esamini prima l'output grezzo di LEAD:

SELECT
  n,
  LEAD(n) OVER (ORDER BY n) AS next_n
FROM seq
ORDER BY n;

Riportare gli intervalli dei gap

Racchiuda il risultato di LEAD in una CTE e mantenga solo le righe in cui il salto al valore successivo supera 1. Queste righe indicano i gap:

Il risultato restituisce esattamente il gap 4-6 e il gap 9-9. L'espressione next_n - n - 1 fornisce anche il conteggio dei valori mancanti in ogni gap, una domanda frequente di approfondimento.

WITH stepped AS (
  SELECT n, LEAD(n) OVER (ORDER BY n) AS next_n
  FROM seq
)
SELECT
  n + 1            AS gap_start,
  next_n - 1       AS gap_end,
  next_n - n - 1   AS missing_count
FROM stepped
WHERE next_n - n > 1
ORDER BY gap_start;

La variante simmetrica con LAG

È possibile rilevare gli stessi gap guardando indietro con LAG. Esiste un gap prima della riga corrente quando il valore precedente è inferiore a quello corrente di più di 1.

È del tutto equivalente; scelga la variante che risulta più naturale per la domanda. Alcuni intervistatori preferiscono LEAD perché il gap viene descritto rispetto alla riga che lo precede, in linea con il modo in cui le persone ne parlano.

WITH stepped AS (
  SELECT n, LAG(n) OVER (ORDER BY n) AS prev_n
  FROM seq
)
SELECT prev_n + 1 AS gap_start,
       n - 1       AS gap_end
FROM stepped
WHERE n - prev_n > 1
ORDER BY gap_start;

Elencare ogni valore mancante

Talvolta l'intervistatore desidera davvero l'elenco completo dei valori mancanti, non soltanto gli intervalli. L'approccio più robusto consiste nel generare la sequenza completa prevista e fare un anti-join con i valori esistenti. In Postgres, generate_series costruisce l'intervallo completo:

Ogni intero nell'intervallo previsto che non è presente in seq è un valore mancante. Questo gestisce anche i gap ai due estremi, se si conoscono il minimo e il massimo previsti.

SELECT g.n AS missing_value
FROM generate_series(
       (SELECT MIN(n) FROM seq),
       (SELECT MAX(n) FROM seq)
     ) AS g(n)
LEFT JOIN seq s ON s.n = g.n
WHERE s.n IS NULL
ORDER BY g.n;

Generare sequenze nei diversi dialetti

Non tutti i motori dispongono di generate_series. Conosca le alternative:

  • Postgres: generate_series(1, 100).
  • SQL Server: una CTE ricorsiva o una tabella numbers/tally.
  • MySQL 8: una CTE ricorsiva che conta fino al valore massimo.

Una CTE ricorsiva è l'alternativa portabile. Produce la stessa sequenza prevista con cui eseguire l'anti-join.

WITH RECURSIVE nums AS (
  SELECT (SELECT MIN(n) FROM seq) AS n
  UNION ALL
  SELECT n + 1 FROM nums
  WHERE n + 1 <= (SELECT MAX(n) FROM seq)
)
SELECT nums.n AS missing_value
FROM nums
LEFT JOIN seq s ON s.n = nums.n
WHERE s.n IS NULL;

Gap nelle date di calendario

Per le date mancanti, generi un calendario completo con passo giornaliero ed esegua un anti-join. Questa è la query standard per «quali giorni non hanno avuto ordini»:

Combini questo approccio con la tecnica degli intervalli applicando LEAD alle date effettive, così da riportare gli intervalli di date mancanti invece dei singoli giorni, usando + INTERVAL '1 day' per i limiti.

SELECT d::date AS missing_day
FROM generate_series(
       DATE '2026-01-01', DATE '2026-01-31',
       INTERVAL '1 day') AS d
LEFT JOIN daily_logins l ON l.login_date = d::date
WHERE l.login_date IS NULL
ORDER BY missing_day;

Gap ai margini oltre i dati

Una trappola sottile: LEAD/LAG rileva i gap solo tra valori presenti. Se manca un numero prima del minimo o dopo il massimo dei valori presenti, l'approccio basato sulle funzioni finestra non può rilevarlo, perché non esiste una riga adiacente.

Se l'intervistatore definisce un intervallo completo previsto (per esempio, gli ID da 1 a 100) e i dati iniziano da 5, deve usare l'anti-join con generate-series delimitato dall'intervallo dichiarato, non dal minimo e dal massimo dei dati. Chiarisca sempre se i limiti attesi sono fissi.

SELECT g.n AS missing_value
FROM generate_series(1, 100) AS g(n)
LEFT JOIN seq s ON s.n = g.n
WHERE s.n IS NULL;

Rilevare i gap per gruppo

Per i gap per utente, partizioni LEAD/LAG per colonna del gruppo, così non viene mai segnalato un gap tra i flussi di due utenti diversi:

Gli intervalli mancanti di ogni utente vengono calcolati indipendentemente. Come per le isole, dimenticare di creare la partizione unisce silenziosamente gli utenti e produce gap fantasma che attraversano righe non correlate.

WITH stepped AS (
  SELECT user_id, n,
    LEAD(n) OVER (PARTITION BY user_id ORDER BY n) AS next_n
  FROM seq_per_user
)
SELECT user_id, n + 1 AS gap_start, next_n - 1 AS gap_end
FROM stepped
WHERE next_n - n > 1
ORDER BY user_id, gap_start;

Scegliere il metodo giusto per i gap

Guida decisionale per il colloquio:

  • Desidera intervalli compatti e solo gap interni? Usi LEAD/LAG, filtrando i casi in cui il passo supera 1.
  • Desidera ogni singolo valore mancante o gap oltre i margini dei dati? Usi l'anti-join con generate-series sull'intervallo completo dichiarato.

Menzionare entrambe le opzioni e spiegare quando si applica ciascuna dimostra una comprensione approfondita. Il metodo con LEAD è meno costoso; quello con la serie è più completo.

Verifica rapida

Individui il caso limite problematico.

Riepilogo: trovare i gap

Il rilevamento dei gap in sintesi:

  • Riporti i gap come intervalli: gap_start = value + 1, gap_end = next_value - 1.
  • LEAD (o il simmetrico LAG), filtrato quando il passo supera 1, individua in modo efficiente i gap interni.
  • L'anti-join con generate-series elenca ogni valore mancante e rileva i gap ai margini rispetto a un intervallo dichiarato.
  • Le CTE ricorsive generano la serie quando generate_series non è disponibile.
  • Partizioni per la colonna del gruppo quando rileva i gap per utente.
  • Chiarisca sempre i limiti attesi.

Infine affrontiamo la variante più ricca: isole definite dalla data e dai cambiamenti di stato.

Domande Frequenti

La lezione «Trovare le lacune in una sequenza» è gratuita?

Sì — il testo completo di «Trovare le lacune in una sequenza» è 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 «Trovare le lacune in una sequenza»?

Rilevare i valori mancanti e l'inizio e la fine di ogni lacuna 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 3 di 4.

Quanto tempo richiede la lezione «Trovare le lacune in una sequenza»?

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

  1. Riconoscere un problema di gaps-and-islands
  2. Il trucco della differenza tra numeri di riga
  3. Trovare le lacune in una sequenza
  4. Isole con cambi di data e stato
← Torna a SQL Interview Prep