SQL Interview Prep · Lekcja

Wyspy przy zmianach dat i statusów

Grupowanie kolejnych okresów o tym samym statusie — częsty problem dotyczący stanu subskrypcji

Wyspy przy zmianach dat i statusów to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 4 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.

Wyspy wyznaczane przez zmieniającą się wartość

Najbardziej istotny biznesowo wariant problemu luk i wysp grupuje kolejne wiersze mające ten sam status, przekształcając zaszumiony dziennik zdarzeń w przejrzyste okresy stanów. Klasyczne polecenie brzmi: „Mając dziennik zdarzeń subskrypcji, zwróć jeden wiersz dla każdego ciągłego okresu, w którym użytkownik pozostawał w danym statusie”.

W tym przypadku sąsiedztwo nie oznacza „wartości różnią się o 1”. Oznacza, że status nie zmienił się względem poprzedniego wiersza. Nowa wyspa zaczyna się w chwili zmiany statusu. Właśnie tutaj technika oparta na LAG przewyższa prostą metodę numeru wiersza.

Przykładowe dane subskrypcji

Rozważmy tabelę sub_events dla jednego użytkownika, uporządkowaną według daty:

2026-01-01 active
2026-02-01 active
2026-03-01 paused
2026-04-01 active
2026-05-01 active

Oczekiwany wynik to trzy okresy statusów: active od stycznia do lutego, paused w marcu oraz active od kwietnia do maja. Należy zauważyć, że dwa okresy active stanowią oddzielne wyspy, ponieważ okres paused je rozdziela. Ten sam status, który nie występuje w kolejnych wierszach, oznacza różne wyspy.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

Oznaczanie zmian statusu

Należy użyć LAG, aby porównać status każdego wiersza z poprzednim. Gdy statusy się różnią (lub poprzednia wartość to NULL w pierwszym wierszu), rozpoczyna się nowa wyspa. Dla zmiany zwracamy 1, a w przeciwnym razie 0.

W obrębie użytkownika należy sortować dane ściśle według daty. Dla naszych danych flagi zmian mają wartości 1,0,1,1,0 i wyznaczają granice trzech okresów.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Suma narastająca jako klucz okresu

Jak wcześniej, suma narastająca flag zmian tworzy klucz grupy stały w obrębie każdego okresu statusu: dla naszych wierszy są to wartości 1,1,2,3,3. Każdy odrębny klucz oznacza jeden ciągły okres.

Metoda różnicy numeru wiersza nie zadziała tutaj, ponieważ status nie jest liczbą zwiększającą się o 1; schemat LAG i sumy narastającej jest właściwym narzędziem, gdy sąsiedztwo oznacza „niezmienioną wartość”.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Zwijanie danych do okresów statusu

Należy teraz wykonać GROUP BY względem user_id, status i klucza sumy narastającej, aby raportować zakres każdego okresu. Uwzględnienie statusu w GROUP BY jest bezpieczne, ponieważ pozostaje on stały w obrębie okresu, a ponadto umożliwia wybranie go bez agregacji.

Wynik składa się dokładnie z trzech wierszy: active od 01-01 do 02-01, paused od 03-01 do 03-01 oraz active od 04-01 do 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

Od zdarzeń do przedziałów półotwartych

Subtelny punkt często poruszany na rozmowach kwalifikacyjnych: data zdarzenia wskazuje moment, w którym status się rozpoczął, a okres naprawdę kończy się dopiero wtedy, gdy zaczyna się kolejny status, a nie w dniu ostatniego zdarzenia z tym samym statusem. Prawidłowym końcem okresu jest często początek kolejnego okresu, co modeluje się jako przedział półotwarty [start, next_start).

Początek kolejnego okresu można obliczyć za pomocą LEAD zastosowanego do zagregowanych okresów, pozostawiając ostatni okres bez określonego końca (NULL lub 'current').

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Obsługa kolejnych powtórzeń statusu

Co zrobić, gdy dziennik zawiera nadmiarowe wiersze, takie jak active, active, active, bez żadnej zmiany między nimi? Dla powtórzeń flaga zmiany ma wartość 0, więc suma narastająca automatycznie pozostawia je w jednej wyspie. To właśnie pożądane zachowanie: kolejne identyczne statusy zostają połączone w jeden okres.

Ta naturalna deduplikacja powtórzeń jest jedną z głównych zalet metody z flagą zmiany i warto wspomnieć o niej rekruterowi.

Kiedy przerwy w czasie powinny dzielić okres

Czasami sam „ten sam status” nie wystarcza — duża przerwa w czasie również powinna podzielić okres, nawet jeśli status się nie zmienił. Na przykład active w styczniu, a następnie ponownie active po sześciu miesiącach przerwy może być uznane za dwa okresy.

Rozszerz flagę zmiany o drugi warunek: rozpocznij nową wyspę, gdy status się zmieni lub czas od poprzedniego zdarzenia przekroczy ustalony próg. W ten sposób oba kryteria sąsiedztwa można przejrzyście połączyć.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Zliczanie różnych zmian stanu

Naturalne pytanie uzupełniające brzmi: „Ile razy ten użytkownik zmienił status?”. To po prostu liczba flag zmian pomniejszona o pierwszą z nich, która oznacza stan początkowy, a nie zmianę.

Równoważnie jest to liczba okresów pomniejszona o 1. Klucz oparty na sumie narastającej już koduje tę informację, więc odpowiedź wynika z tej samej konstrukcji, którą utworzono na potrzeby okresów.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Dlaczego tutaj to rozwiązanie jest lepsze od samozłączeń

Rozwiązanie z samozłączeniem dla okresów statusów wymagałoby połączenia każdego wiersza z sąsiednim, wykrycia zmian, a następnie połączenia granic w całość — to podatny na błędy, wieloetapowy proces, który sprawia trudności przy trzech lub większej liczbie okresów.

Potok LAG–flaga–suma narastająca–GROUP BY obsługuje dowolną liczbę okresów w jednym przejściu i bez złączeń. Umiejętność przedstawienia tego kontrastu — liniowego rozwiązania w jednym przejściu w porównaniu z samozłączeniem o złożoności kwadratowej — jest dokładnie tym rodzajem dojrzałego rozumowania, które rekruterzy doceniają.

Uniwersalny szablon

Warto zapamiętać ten czteroklauzulowy szablon — rozwiązuje całą rodzinę problemów z wyspami statusów, zmieniając w CASE tylko test sąsiedztwa:

flag: CASE z LAG do wykrywania nowej wyspy.
key: narastająca SUM flagi, z podziałem na partycje i uporządkowaniem.
collapse: GROUP BY kolumny partycji, statusu i klucza.
interval (opcjonalnie): LEAD do wyznaczania końców przedziałów półotwartych.

Ten sam szkielet działa dla kolejnych liczb całkowitych, dat i statusów — zmienia się tylko warunek w CASE.

Szybkie sprawdzenie

Upewnij się, że rozumiesz regułę grupowania wysp statusów.

Podsumowanie: wyspy statusów i dat

Potrafisz już rozwiązać najbardziej rozbudowany wariant problemu luk i wysp:

Sąsiedztwo oznacza niezmieniony status względem poprzedniego wiersza; flaga zmienia się dzięki LAG.
Flagi zmian są sumowane narastająco, tworząc klucz grupowania dla każdego okresu.
Agregacja za pomocą GROUP BY user_id, status, key pozwala uzyskać przedziały okresów.
LEAD służy do wyznaczania końców przedziałów półotwartych; flagę można rozszerzyć, aby rozdzielała także okresy po dużych przerwach w czasie.
Powtarzające się identyczne wiersze są automatycznie łączone, a liczba zmian wynika z tych samych flag.
Jeden uniwersalny szablon obejmuje liczby całkowite, daty i statusy — zmienia się tylko CASE.

To kończy kurs o problemach luk i wysp — jest to wiarygodny sygnał umiejętności na poziomie seniora podczas rozmów kwalifikacyjnych z SQL.

Często zadawane pytania

Czy lekcja „Wyspy przy zmianach dat i statusów” jest bezpłatna?

Tak — pełny tekst „Wyspy przy zmianach dat i statusów” 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 „Wyspy przy zmianach dat i statusów”?

Grupowanie kolejnych okresów o tym samym statusie — częsty problem dotyczący stanu subskrypcji Ć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 4 z 4.

Ile czasu zajmuje lekcja „Wyspy przy zmianach dat i statusów”?

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

← Powrót do SQL Interview Prep