0Pricing
Coding Interview Prep · Lezione

Confrontare le righe all'interno di una tabella

Pattern di self-join per trovare coppie, duplicati e record adiacenti

Confrontare le righe all'interno di una tabella è 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.

Self join per confrontare le righe tra loro

Oltre alle gerarchie, l'altro uso principale di un self join consiste nel confrontare tra loro le righe della stessa tabella. Invece di rappresentare una relazione padre-figlio, si abbinano righe arbitrarie per trovare duplicati, corrispondenze approssimative o record adiacenti.

Lo schema è lo stesso: si assegna un alias alla tabella due volte e si scrive una condizione ON che esprime la relazione tra le due righe da abbinare.

Trovare coppie nello stesso gruppo

Domanda classica: trovare tutte le coppie di dipendenti che lavorano nello stesso reparto. Si unisce la tabella con sé stessa in base all'uguaglianza del reparto, mantenendo però distinte le due righe.

Il join ingenuo abbinerebbe inoltre ogni dipendente a sé stesso e produrrebbe ogni coppia due volte. Risolveremo questo problema nel passaggio successivo.

SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b ON a.department = b.department;

Rimuovere le coppie con sé stessi e i duplicati speculari

Nel raggruppamento per coppie dello stesso gruppo si presentano due problemi: una riga corrisponde a sé stessa, come Alice con Alice, e ogni coppia compare due volte, come Alice-Bob e Bob-Alice.

Si risolvono entrambi con una sola disuguaglianza: a.id < b.id. In questo modo le due righe sono certamente diverse e viene mantenuto un solo ordinamento per ogni coppia.

SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.id < b.id;

Perché a.id < b.id e non a.id <> b.id

Usare a.id <> b.id elimina le coppie con sé stessi, ma restituisce comunque entrambi gli ordinamenti e raddoppia i risultati. Usare a.id < b.id elimina le coppie con sé stessi e deduplica il duplicato speculare in un solo passaggio.

Gli intervistatori prestano particolare attenzione alla scelta tra < e <>: dimostra che si comprende la combinatoria dei self join.

-- <>  keeps Alice-Bob AND Bob-Alice (duplicated)
-- <   keeps only Alice-Bob (correct unique pairs)

Trovare righe duplicate

Per trovare record duplicati in base a colonne chiave, si esegue un self join su quelle colonne e si richiedono chiavi primarie diverse.

Qui individuiamo i clienti che condividono un indirizzo e-mail. a.id < b.id mantiene ogni coppia di duplicati una sola volta. Spesso un GROUP BY ... HAVING COUNT(*) > 1 è più chiaro, ma il self join mostra le coppie effettivamente problematiche una accanto all'altra.

SELECT a.id, b.id, a.email
FROM customers a
JOIN customers b
  ON a.email = b.email
 AND a.id < b.id;

Confrontare record adiacenti

Un'attività frequente per gli analisti consiste nel confrontare ogni riga con quella successiva nella sequenza, ad esempio le vendite di ogni giorno con quelle del giorno precedente. Un self join può abbinare righe consecutive.

Qui uniamo ogni giorno alla riga esattamente di un giorno precedente per calcolare una differenza. Questo funziona quando nella sequenza non ci sono intervalli mancanti.

SELECT t.day, t.amount,
       t.amount - y.amount AS change_vs_prev
FROM daily_sales t
JOIN daily_sales y
  ON y.day = t.day - INTERVAL '1 day';

Il problema degli intervalli mancanti nei self join per adiacenza

La query precedente non funziona se manca un giorno: non esiste alcuna riga esattamente del giorno precedente, quindi quella riga viene eliminata con un inner join oppure è necessario gestire i valori NULL.

Per questo gli intervistatori spesso indirizzano verso le funzioni finestra, come LAG, per il caso "confrontare con la riga precedente": usano la posizione ordinale anziché la corrispondenza di un valore e gestiscono con eleganza gli intervalli mancanti.

-- LAG handles gaps; the self join assumed contiguous days
SELECT day, amount,
       amount - LAG(amount) OVER (ORDER BY day) AS change_vs_prev
FROM daily_sales;

Self join vs funzione finestra

Tenga presente il compromesso:

  • Un self join confronta le righe in base a una relazione di valore (stesso reparto, data precedente). È flessibile, ma può moltiplicare le righe e gestire male le lacune.
  • Una funzione finestra confronta le righe in base alla posizione ordinale all'interno di una partizione ordinata. È più chiara per la logica della riga precedente o successiva.

Per «confrontare con la riga adiacente», preferisca LAG/LEAD. Per «trovare tutte le coppie che soddisfano una condizione», il self join è lo strumento naturale.

Trovare le righe che superano i colleghi

Un altro schema consiste nel trovare i dipendenti che guadagnano più di almeno un collega del loro reparto. Un self join lo esprime direttamente.

Colleghiamo ogni dipendente agli altri dello stesso reparto che guadagnano meno, quindi manteniamo i dipendenti distinti che compaiono. La struttura rispecchia quasi la frase originale.

SELECT DISTINCT a.name, a.department, a.salary
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.salary > b.salary;

Attenzione al fan-out

I self join su una colonna non univoca moltiplicano le righe. L'abbinamento all'interno di un reparto di 100 persone produce circa 100 x 100 coppie candidate prima del filtraggio.

Predisponga sempre il predicato che elimina i duplicati (a.id < b.id) e aggiunga DISTINCT o un raggruppamento quando servono solo le righe coinvolte, non ogni coppia. In un colloquio, dimostri di tenere conto di questa moltiplicazione delle righe.

Scegliere lo strumento di confronto

Guida alla scelta per il confronto all'interno di una tabella:

  • Tutte le coppie corrispondenti (duplicati, combinazioni nello stesso gruppo): self join con a.id < b.id.
  • Riga precedente o successiva in un ordinamento: funzione finestra (LAG/LEAD).
  • Confrontare ogni riga con un aggregato di gruppo: sottoquery correlata o funzione aggregata di finestra.

Verifica rapida

Si desidera ottenere ogni coppia univoca di prodotti appartenenti alla stessa categoria, senza associare un prodotto a se stesso e senza duplicare l'ordine delle coppie.

Riepilogo: confrontare le righe all'interno di una tabella

Punti chiave:

  • Usi un self join sulla tabella per abbinare le sue stesse righe nella ricerca di duplicati e nelle combinazioni all'interno dello stesso gruppo.
  • Usi a.id < b.id per eliminare sia le coppie di una riga con se stessa sia i duplicati speculari con un unico predicato.
  • I confronti di adiacenza tramite self join si interrompono in presenza di lacune; preferisca LAG/LEAD per la logica della riga precedente o successiva.
  • Consideri sempre il fan-out quando esegue join su colonne non univoche.

Domande Frequenti

La lezione «Confrontare le righe all'interno di una tabella» è gratuita?

Sì — il testo completo di «Confrontare le righe all'interno di una tabella» è 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 «Confrontare le righe all'interno di una tabella»?

Pattern di self-join per trovare coppie, duplicati e record adiacenti 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 «Confrontare le righe all'interno di una tabella»?

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. CROSS JOIN e prodotti cartesiani
  2. SELF JOIN per le gerarchie
  3. Confrontare le righe all'interno di una tabella
  4. Scegliere il tipo di join corretto
← Torna a Coding Interview Prep