Porównywanie wierszy w jednej tabeli
Wzorce self-join do znajdowania par, duplikatów i sąsiednich rekordów
Porównywanie wierszy w jednej tabeli to bezpłatna lekcja Coding Interview Prep na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej Coding Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.
Self join do porównywania wierszy
Poza hierarchiami drugim ważnym zastosowaniem self join jest porównywanie wierszy tej samej tabeli ze sobą. Zamiast relacji nadrzędny–podrzędny można łączyć dowolne wiersze, aby znajdować duplikaty, prawie identyczne rekordy lub sąsiednie wpisy.
Wzorzec pozostaje taki sam: tabelę należy opatrzyć dwoma aliasami i napisać warunek ON, który opisuje relację między dwiema wierszami przeznaczonymi do połączenia.
Znajdowanie par w tej samej grupie
Klasyczne pytanie brzmi: znajdź wszystkie pary pracowników pracujących w tym samym dziale. Należy połączyć tabelę z samą sobą według równości działów, ale zachować rozróżnienie między oboma wierszami.
Proste złączenie połączyłoby również każdego pracownika z nim samym i zwróciłoby każdą parę dwukrotnie. Naprawimy to w następnym kroku.
SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b ON a.department = b.department;Usuwanie par wiersza z samym sobą i duplikatów w odwrotnej kolejności
Łączenie w pary z tej samej grupy wiąże się z dwoma problemami: wiersz pasuje do samego siebie, na przykład Alice do Alice, a każda para pojawia się dwukrotnie, na przykład Alice–Bob i Bob–Alice.
Oba problemy można naprawić jednym warunkiem nierówności: a.id < b.id. Gwarantuje on, że oba wiersze są różne, oraz zachowuje tylko jedną kolejność każdej pary.
SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b
ON a.department = b.department
AND a.id < b.id;Dlaczego a.id < b.id, a nie a.id <> b.id
Użycie a.id <> b.id usuwa pary wiersza z samym sobą, ale nadal zwraca obie kolejności, podwajając liczbę wyników. Użycie a.id < b.id jednocześnie usuwa takie pary i eliminuje duplikat wynikający z odwrotnej kolejności.
Rekruterzy zwracają szczególną uwagę na wybór między < a <>; pokazuje on zrozumienie kombinatoryki self join.
-- <> keeps Alice-Bob AND Bob-Alice (duplicated)
-- < keeps only Alice-Bob (correct unique pairs)Znajdowanie zduplikowanych wierszy
Aby znaleźć rekordy, które mają takie same wartości w kolumnach kluczowych, należy wykonać self join według tych kolumn i wymagać różnych kluczy głównych.
W tym przykładzie wyszukujemy klientów współdzielących adres e-mail. Warunek a.id < b.id zachowuje każdą parę duplikatów tylko raz. Często czytelniejsze jest użycie GROUP BY ... HAVING COUNT(*) > 1, ale self join pokazuje faktyczne problematyczne pary obok siebie.
SELECT a.id, b.id, a.email
FROM customers a
JOIN customers b
ON a.email = b.email
AND a.id < b.id;Porównywanie sąsiednich rekordów
Częstym zadaniem analitycznym jest porównanie każdego wiersza z następnym w kolejności, na przykład porównanie sprzedaży z danego dnia ze sprzedażą z dnia poprzedniego. Self join może połączyć kolejne wiersze.
W tym przykładzie łączymy każdy dzień z wierszem dokładnie o jeden dzień wcześniejszym, aby obliczyć różnicę. Działa to wtedy, gdy w sekwencji nie ma luk.
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';Problem luk przy self join dla sąsiednich wierszy
Poprzednie zapytanie nie działa, jeśli brakuje któregoś dnia: nie ma wiersza dokładnie z poprzedniego dnia, więc dany wiersz znika przy złączeniu wewnętrznym albo trzeba obsłużyć wartości NULL.
Dlatego rekruterzy często kierują rozmowę w stronę funkcji okna, takich jak LAG, służących do 'porównania z poprzednim wierszem'. Wykorzystują one pozycję porządkową zamiast dopasowania wartości i dobrze radzą sobie z lukami.
-- 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 a funkcja okna
Warto znać kompromis:
- W przypadku self join wiersze są porównywane na podstawie relacji wartości (ten sam dział, wcześniejsza data). To rozwiązanie jest elastyczne, ale może zwielokrotniać wiersze i niepoprawnie obsługiwać luki w danych.
- Funkcja okna porównuje wiersze na podstawie pozycji porządkowej w obrębie uporządkowanej partycji. Jest wygodniejsza w logice dotyczącej poprzedniego lub następnego wiersza.
W przypadku „porównania z sąsiednim wierszem” należy preferować LAG/LEAD. Gdy trzeba „znaleźć wszystkie pary spełniające warunek”, naturalnym narzędziem jest self join.
Znajdowanie wierszy, które przewyższają inne w swojej grupie
Inny typ zadania: znaleźć pracowników, którzy zarabiają więcej niż co najmniej jeden współpracownik w ich dziale. Self join wyraża to bezpośrednio.
Łączymy każdego pracownika z innymi pracownikami z tego samego działu, którzy zarabiają mniej, a następnie zachowujemy unikatowych pracowników występujących w wyniku. To niemal dosłowny zapis zdania sformułowanego po angielsku.
SELECT DISTINCT a.name, a.department, a.salary
FROM employees a
JOIN employees b
ON a.department = b.department
AND a.salary > b.salary;Uwaga na zwielokrotnienie wierszy
Self join na kolumnie, która nie jest unikatowa, zwielokrotnia wiersze. Utworzenie par w dziale liczącym 100 osób daje około 100 x 100 potencjalnych par przed filtrowaniem.
Zawsze należy uwzględnić predykat usuwający duplikaty (a.id < b.id) oraz dodać DISTINCT lub grupowanie, gdy potrzebne są tylko wiersze uczestniczące w parach, a nie każda para. Warto wspomnieć o świadomości takiego zwielokrotnienia wierszy podczas rozmowy kwalifikacyjnej.
Wybór narzędzia do porównywania
Przewodnik po decyzjach dotyczących porównywania danych w jednej tabeli:
- Wszystkie pasujące pary (duplikaty, kombinacje w tej samej grupie): self join z użyciem
a.id < b.id. - Poprzedni lub następny wiersz w określonej kolejności: funkcja okna (
LAG/LEAD). - Porównanie każdego wiersza z agregatem grupy: podzapytanie skorelowane lub agregat okna.
Szybki test
Potrzebują Państwo znaleźć każdą unikatową parę produktów należących do tej samej kategorii, bez łączenia produktu z samym sobą i bez zduplikowanych kolejności par.
Podsumowanie: porównywanie wierszy w jednej tabeli
Najważniejsze wnioski:
- Należy używać self join, aby łączyć wiersze tabeli w pary podczas wyszukiwania duplikatów i kombinacji w tej samej grupie.
- Użycie
a.id < b.ideliminuje w jednym predykacie pary własne i odwrócone duplikaty. - Porównywanie sąsiednich wierszy za pomocą self join nie działa poprawnie w przypadku luk; w logice dotyczącej poprzedniego lub następnego wiersza należy preferować
LAG/LEAD. - Zawsze należy uwzględniać zwielokrotnienie wierszy podczas łączenia po kolumnach, które nie są unikatowe.
Często zadawane pytania
Czy lekcja „Porównywanie wierszy w jednej tabeli” jest bezpłatna?
Tak — pełny tekst „Porównywanie wierszy w jednej tabeli” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu Coding Interview Prep, przejdź na CoddyKit PRO. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.
Co nauczysz się w „Porównywanie wierszy w jednej tabeli”?
Wzorce self-join do znajdowania par, duplikatów i sąsiednich rekordów Ćwiczysz Coding Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć Coding Interview Prep?
Nie wymagamy żadnego doświadczenia. Coding Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.
Ile czasu zajmuje lekcja „Porównywanie wierszy w jednej tabeli”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji Coding Interview Prep?
Tak. Każda lekcja Coding Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- CROSS JOIN i iloczyny kartezjańskie
- SELF JOIN dla hierarchii
- Porównywanie wierszy w jednej tabeli
- Wybór właściwego typu złączenia