0Pricing
SQL Interview Prep · Lekcja

Sztuczka z różnicą numerów wierszy

Odejmowanie ROW_NUMBER od sekwencji w celu grupowania kolejnych wartości w wyspy

Sztuczka z różnicą numerów wierszy to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 2 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 SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Najbardziej elegancki klucz wyspy

Trik z różnicą numerów wierszy to technika, którą osoby prowadzące rozmowy techniczne najbardziej chcą zobaczyć przy wyspach z kolejnych liczb całkowitych lub dat. Pozwala utworzyć klucz grupy za pomocą pojedynczego odejmowania — bez LAG i bez sumy narastającej.

Cała idea polega na odjęciu ROW_NUMBER od samej wartości. W każdym ciągu kolejnych wartości zarówno wartość, jak i numer wiersza zwiększają się przy każdym kroku dokładnie o 1, dlatego ich różnica pozostaje stała w całym ciągu. Ta stała jest kluczem wyspy.

Dlaczego różnica pozostaje stała

Rozważmy dwa sąsiednie wiersze w ciągu kolejnych wartości. Przechodząc od jednego do następnego, wartość zwiększa się o 1, a numer wiersza również zwiększa się o 1. Po odjęciu wartości +1 się znoszą, więc value - row_number nie zmienia się.

Jednak gdy tylko pojawi się luka, wartość przeskakuje o więcej niż 1, podczas gdy numer wiersza nadal zwiększa się tylko o 1. Różnica przyjmuje nową stałą wartość. Właśnie ta zmiana oddziela jedną wyspę od następnej.

Przykład na naszych danych

Przypomnijmy sobie dni logowania 1, 2, 3, 7, 8, 10. Zestawmy numer wiersza i różnicę obok siebie:

  • day 1, rn 1, diff 0
  • day 2, rn 2, diff 0
  • day 3, rn 3, diff 0
  • day 7, rn 4, diff 3
  • day 8, rn 5, diff 3
  • day 10, rn 6, diff 4

Wartości diff (0,0,0,3,3,4) idealnie dzielą wiersze na trzy wyspy. Ta sama wartość diff oznacza tę samą wyspę.

SELECT
  day_no,
  ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;

Redukowanie do wysp

Gdy różnica pełni funkcję klucza grupy, końcowe zapytanie ma standardową postać redukcji. Należy umieścić różnicę w CTE i wykonać GROUP BY po tej wartości:

Zapytanie zwraca te same trzy wyspy co wcześniej, ale SQL jest krótszy i czytelniejszy niż wersja z LAG i sumą narastającą. W przypadku ciągów liczb całkowitych lub ciągów o stałym kroku jest to rozwiązanie, po które należy sięgnąć w pierwszej kolejności.

WITH keyed AS (
  SELECT
    day_no,
    day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
  FROM logins
)
SELECT
  MIN(day_no) AS start_day,
  MAX(day_no) AS end_day,
  COUNT(*)    AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;

Pułapka: wartości muszą zwiększać się o jeden

Prosta metoda różnicy zakłada, że ciąg zwiększa się o dokładnie 1 na każdym kroku. Jest to prawdą w przypadku kolejnych liczb całkowitych i następujących po sobie dni kalendarzowych, ale metoda przestaje działać, gdy wartości zwiększają się o inną stałą wartość lub gdy występują duplikaty.

  • Nawet wartości parzyste 2,4,6,8 będą wyglądać jak luki przy odejmowaniu numeru wiersza od wartości.
  • Duplikaty zaburzają wyrównanie, ponieważ numer wiersza nadal rośnie, podczas gdy wartość pozostaje bez zmian.

Świadomość tego ograniczenia oraz sposobu jego naprawienia odróżnia zapamiętaną sztuczkę od rzeczywistego zrozumienia.

Naprawianie ciągów o stałym kroku

Jeśli wartości zwiększają się o znaną stałą k zamiast o 1, należy najpierw je znormalizować: podzielić wartość przez k (lub w przypadku liczb całkowitych użyć value / k), aby każdy krok ponownie wynosił 1, a następnie odjąć numer wiersza.

Na przykład dla liczb parzystych zwiększających się o 2 należy użyć day_no / 2 - ROW_NUMBER(). Znormalizowana wartość rośnie teraz o 1 dla każdego kolejnego elementu, przywracając właściwość stałej różnicy.

SELECT
  val,
  (val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;

Zastosowanie metody do dat

Daty są najczęstszym przypadkiem zastosowania w praktyce. Dat nie można bezpośrednio odejmować od numeru wiersza, dlatego najpierw należy przekształcić datę w liczbę dni. W Postgresie należy odjąć ustaloną datę bazową, aby otrzymać całkowitą liczbę dni, a następnie zastosować tę samą metodę.

Ponieważ kolejne dni kalendarzowe różnią się o 1, różnica między liczbą dni a numerem wiersza ponownie jest stała w obrębie jednej wyspy.

WITH keyed AS (
  SELECT
    login_date,
    (login_date - DATE '2000-01-01')
      - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
  FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*)        AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;

Obliczanie różnicy dat w różnych dialektach

Sposób przekształcania daty w liczbę całkowitą zależy od silnika, a osoby prowadzące rozmowy kwalifikacyjne doceniają znajomość różnych dialektów:

  • Postgres: odejmij literał daty: login_date - DATE '2000-01-01' zwraca liczbę całkowitą.
  • MySQL: użyj DATEDIFF(login_date, '2000-01-01').
  • SQL Server: użyj DATEDIFF(day, '2000-01-01', login_date).

W niektórych silnikach można zastosować jeszcze zgrabniejsze rozwiązanie: bezpośrednio odjąć od daty ROW_NUMBER dni za pomocą arytmetyki przedziałów, a następnie wykonać GROUP BY względem otrzymanej daty bazowej.

SELECT
  login_date,
  login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
               * INTERVAL '1 day') AS grp_date
FROM daily_logins;

Dodawanie partycjonowania według grup

W przypadku wysp dla poszczególnych użytkowników należy partycjonować numer wiersza według kolumny grupującej. Co najważniejsze, klucz grupowania musi również uwzględniać kolumnę partycjonowania, ponieważ różni użytkownicy mogą przypadkowo wygenerować tę samą wartość różnicy.

Należy więc wykonać GROUP BY zarówno względem user_id, jak i obliczonej różnicy. Pominięcie user_id w końcowym GROUP BY to subtelny błąd, który osoby prowadzące rozmowy kwalifikacyjne uwielbiają wykrywać.

WITH keyed AS (
  SELECT user_id, day_no,
    day_no - ROW_NUMBER()
      OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;

Metoda różnicy a LAG: którą wybrać

W Państwa zestawie narzędzi znajdują się teraz dwie solidne techniki. Należy wybierać między nimi świadomie:

  • Różnica numeru wiersza: najkrótsze i najbardziej przejrzyste rozwiązanie dla ciągów wartości o stałym kroku (kolejnych liczb całkowitych, kolejnych dat). Pierwszy wybór, gdy sąsiedztwo oznacza „różnicę o stałą wartość”.
  • LAG i suma narastająca: bardziej elastyczne rozwiązanie, gdy sąsiedztwo nie oznacza stałego kroku liczbowego, na przykład „taki sam status jak w poprzednim wierszu” lub niestandardowe, nieregularne reguły.

Podczas rozmowy kwalifikacyjnej należy wskazać wybraną metodę i uzasadnić wybór — rozumowanie robi większe wrażenie niż sama składnia.

Ostrożne postępowanie z duplikatami

Jeśli wartość może się powtarzać, a mimo to potrzebna jest jedna wyspa dla każdego kolejnego przebiegu, należy najpierw usunąć duplikaty za pomocą DISTINCT lub etapu grupowania, aby numer wiersza był przyporządkowany wartościom jeden do jednego. Alternatywnie można użyć DENSE_RANK zamiast ROW_NUMBER, dzięki czemu wartości o tym samym wyniku będą miały wspólną rangę.

Zawsze należy zapytać osobę prowadzącą rozmowę, czy mogą wystąpić duplikaty; właściwe zabezpieczenie zależy od tego, czy duplikaty powinny rozszerzać przebieg, czy być w jego obrębie ignorowane.

WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;

Szybkie sprawdzenie

Należy upewnić się, że wiadomo, dlaczego ta metoda działa.

Podsumowanie: metoda różnicy

Znają już Państwo najprostszą postać klucza wyspy:

  • Wzór klucza: value - ROW_NUMBER() OVER (ORDER BY value) jest stały w obrębie każdego kolejnego przebiegu.
  • Należy wykonać GROUP BY względem różnicy, aby uzyskać początek, koniec i długość.
  • W przypadku ciągów o stałym kroku należy najpierw je znormalizować (podzielić przez krok).
  • W przypadku dat należy przekształcić je w całkowitą liczbę dni za pomocą funkcji różnicy właściwej dla danego dialektu.
  • Dla poszczególnych grup należy użyć PARTITION BY przy obliczaniu numeru wiersza i uwzględnić kolumnę grupy w końcowym GROUP BY.
  • Przed duplikatami należy zabezpieczyć się za pomocą DISTINCT lub DENSE_RANK.

Następnie przeniesiemy uwagę z wysp na puste przestrzenie, czyli znajdowanie luk.

Często zadawane pytania

Czy lekcja „Sztuczka z różnicą numerów wierszy” jest bezpłatna?

Tak — pełny tekst „Sztuczka z różnicą numerów wierszy” 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 SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Sztuczka z różnicą numerów wierszy”?

Odejmowanie ROW_NUMBER od sekwencji w celu grupowania kolejnych wartości w wyspy Ćwiczysz SQL 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ąć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL 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 2 z 4.

Ile czasu zajmuje lekcja „Sztuczka z różnicą numerów wierszy”?

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

Tak. Każda lekcja SQL 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

  1. Rozpoznawanie problemu luk i wysp
  2. Sztuczka z różnicą numerów wierszy
  3. Znajdowanie luk w sekwencji
  4. Wyspy przy zmianach dat i statusów
← Powrót do SQL Interview Prep