SQL Interview Prep · Lekcja

N kolejnych wierszy spełniających warunek

Klasyczny wzorzec okna: „trzy kolejne dni ze sprzedażą powyżej X”.

N kolejnych wierszy spełniających warunek to bezpłatna lekcja SQL 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 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.

Klasyczne zadanie z LeetCode

To jedno z najczęściej zadawanych pytań na rozmowach rekrutacyjnych dotyczących SQL: „Znajdź wszystkie daty, w których sprzedaż przekroczyła próg przez co najmniej trzy kolejne dni”, albo ulubione zadanie z LeetCode: „Zwróć stadion, dla którego występują co najmniej 3 kolejne wiersze z frekwencją powyżej 100”.

Struktura jest zawsze taka sama: wiersz spełnia warunek tylko wtedy, gdy znajduje się w ciągu N kolejnych wierszy spełniających ten warunek. W tej lekcji pokazano dwa przejrzyste rozwiązania oraz pułapkę, w którą wpada większość kandydatów.

Dane przykładowe

Korzystamy z dziennej tabeli sales. Warunkiem jest amount > 100. Musimy zwrócić każdy dzień należący do serii co najmniej 3 kolejnych dni kalendarzowych, z których każdy spełnia ten warunek.

sale_date — jeden wiersz na dzień
amount — łączna sprzedaż z danego dnia

Kluczowy niuans: wiersze muszą być kolejne w sekwencji, a w wersjach opartych na datach także kolejne w kalendarzu.

SELECT * FROM sales ORDER BY sale_date;
-- sale_date  | amount
-- 2024-03-01 |  120
-- 2024-03-02 |  150
-- 2024-03-03 |  130
-- 2024-03-04 |   90
-- 2024-03-05 |  200

Podejście 1: filtrowanie, potem wyspa

Solidne podejście polega na tym, aby najpierw zachować tylko wiersze spełniające warunek, następnie pogrupować pozostałe wiersze w kolejne wyspy, a na końcu zachować wyspy o długości co najmniej N.

Pierwszym krokiem jest filtr WHERE. Drugi krok ponownie wykorzystuje kotwicę z metody luk i wysp. Ponieważ najpierw przeprowadziliśmy filtrowanie, wyspa oznacza tutaj ciąg kolejnych dni spełniających warunek.

WITH qualifying AS (
  SELECT sale_date
  FROM sales
  WHERE amount > 100
)
SELECT * FROM qualifying ORDER BY sale_date;

Wyznaczanie kotwic dla spełniających warunek ciągów

Należy ponumerować wiersze spełniające warunek według daty i odjąć numer, aby uzyskać kotwicę wyspy. Wiersze, które są kolejne w kalendarzu i wszystkie spełniają warunek, będą miały tę samą kotwicę; każdy dzień niespełniający warunku został usunięty, więc przerywa ciąg dokładnie w odpowiednim miejscu.

WITH qualifying AS (
  SELECT sale_date
  FROM sales
  WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
)
SELECT sale_date, sale_date - rn AS grp
FROM numbered;

Zachowywanie wystarczająco długich wysp

Należy grupować według kotwicy, zliczać wiersze i zachować tylko grupy, dla których COUNT(*) >= 3. Jeśli rekruter chce otrzymać z powrotem poszczególne daty spełniające warunek, należy połączyć zachowane kotwice z ponumerowanymi wierszami.

WITH qualifying AS (
  SELECT sale_date FROM sales WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
),
islands AS (
  SELECT sale_date - rn AS grp, COUNT(*) AS len
  FROM numbered
  GROUP BY sale_date - rn
  HAVING COUNT(*) >= 3
)
SELECT n.sale_date
FROM numbered n
JOIN islands i ON n.sale_date - n.rn = i.grp
ORDER BY n.sale_date;

Podejście 2: przesuwane okno COUNT

Bardziej eleganckie podejście, gdy N jest małe i stałe, polega na użyciu ramki okna do zliczenia, ile sąsiednich wierszy również spełnia warunek. Jeśli dowolne okno N kolejnych wierszy zawierające dany wiersz składa się wyłącznie z wierszy spełniających warunek, ten wiersz należy do wyniku.

Najpierw należy dodać flagę logiczną, a następnie sumować tę flagę w przesuwanych ramkach.

SELECT sale_date, amount,
  CASE WHEN amount > 100 THEN 1 ELSE 0 END AS ok
FROM sales;

Sumowanie w trzech ramkach

Dla serii o długości dokładnie 3 wiersz spełniający warunek należy do wyniku, jeśli suma w 3-wierszowym oknie kończącym się na tym wierszu, wyśrodkowanym na nim lub zaczynającym się od niego wynosi 3. Należy obliczyć trzy sumy kroczące i sprawdzić, czy którakolwiek z nich jest równa 3.

Jest to technika wykorzystana w rozwiązaniu zadania LeetCode 601 (Human Traffic of Stadium).

WITH flagged AS (
  SELECT sale_date, amount,
    CASE WHEN amount > 100 THEN 1 ELSE 0 END AS ok
  FROM sales
),
w AS (
  SELECT *,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS s_end,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s_mid,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS s_start
  FROM flagged
)
SELECT sale_date, amount
FROM w
WHERE ok = 1 AND (s_end = 3 OR s_mid = 3 OR s_start = 3);

Pułapka związana z lukami w kalendarzu

Podejście oparte na sumie okna używa ROWS, które zlicza sąsiednie wiersze wyniku, a nie sąsiednie dni kalendarzowe. Jeśli dzień niespełniający warunku został wcześniej odfiltrowany, dwa wiersze mogą być sąsiednie w wyniku, ale nie być kolejne w kalendarzu.

Wniosek: należy zastosować przesuwane okno do pełnej serii dziennej (nie filtrować jej wcześniej) albo użyć metody kotwicy dat, która z natury uwzględnia luki w kalendarzu. Warto wyraźnie wspomnieć o tym kompromisie podczas rozmowy rekrutacyjnej.

Uogólnienie dla dowolnej wartości N

Podejście 1 (filtrowanie, potem wyspa) uogólnia się bez problemu: wystarczy zmienić HAVING COUNT(*) >= N. To jego główna przewaga nad sumą wielu okien, która wraz ze wzrostem N wymaga dodawania kolejnych ramek.

Dla parametryzowanej lub dużej wartości N należy preferować metodę wysp — wymaga ona zmiany jednego progu zamiast N−1 ręcznie zapisanych okien.

-- only the threshold changes for N = 5
HAVING COUNT(*) >= 5

Wybór podejścia

Krótka wskazówka decyzyjna do wypowiedzenia na głos:

Filtrowanie, potem wyspa: uwzględnia luki w kalendarzu, uogólnia się dla dowolnej wartości N i zwraca pełne ciągi — to bezpieślny wybór domyślny.
Suma przesuwanego okna: elegancka dla ustalonej, małej wartości N w gęstej serii dziennej, ale należy uważać na pułapkę ROWS kontra kalendarz.

Wymienienie obu podejść, a następnie uzasadnienie wyboru jednego z nich, jest dokładnie tym, co doceniają rekruterzy na stanowiska od mid do senior.

Pełne rozwiązanie

Przenośna odpowiedź dla dowolnej wartości N, która uwzględnia kolejność dni kalendarzowych i zwraca daty spełniające warunek:

WITH qualifying AS (
  SELECT sale_date FROM sales WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
),
islands AS (
  SELECT sale_date - rn AS grp, COUNT(*) AS len
  FROM numbered
  GROUP BY sale_date - rn
  HAVING COUNT(*) >= 3
)
SELECT n.sale_date
FROM numbered n
JOIN islands i ON n.sale_date - n.rn = i.grp
ORDER BY n.sale_date;

Szybki test

Należy znaleźć subtelny błąd.

Podsumowanie

Dla N kolejnych wierszy spełniających warunek:

Filtrowanie, potem wyspa: zachować wiersze spełniające warunek, wyznaczyć kotwicę za pomocą date - ROW_NUMBER(), pogrupować je i użyć HAVING COUNT(*) >= N. Podejście uogólnia się i uwzględnia luki w kalendarzu.
Suma przesuwanego okna: oznaczyć wiersze flagą i sumować ją w stałych ramkach obejmujących N wierszy; rozwiązanie jest eleganckie, ale w przypadku wstępnie przefiltrowanych danych należy uważać na różnicę między ROWS a kalendarzem.

Dalej: obliczanie bieżącej aktywnej serii użytkownika na dziś.

Często zadawane pytania

Czy lekcja „N kolejnych wierszy spełniających warunek” jest bezpłatna?

Tak — pełny tekst „N kolejnych wierszy spełniających warunek” 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 „N kolejnych wierszy spełniających warunek”?

Klasyczny wzorzec okna: „trzy kolejne dni ze sprzedażą powyżej X”. Ć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 3 z 4.

Ile czasu zajmuje lekcja „N kolejnych wierszy spełniających warunek”?

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

Wykrywanie kolejnych dni kalendarzowych
Najdłuższa passa na użytkownika
N kolejnych wierszy spełniających warunek
Bieżąca aktywna passa na dziś

← Powrót do SQL Interview Prep