0Pricing
SQL Interview Prep · Lekcja

Uporządkowane zdarzenia i okna czasowe

Zapewnianie kolejności etapów i ich wystąpienia w określonym limicie czasu za pomocą funkcji okna.

Uporządkowane zdarzenia i okna czasowe 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.

Dlaczego kolejność i czas mają znaczenie

Podstawowy lejek z poprzedniej lekcji sprawdza tylko, czy użytkownik wykonał każdy krok. Bardziej dociekliwa osoba rekrutująca zapyta: czy kroki nastąpiły we właściwej kolejności i w rozsądnym czasie?

Użytkownik, który dokonał zakupu w poniedziałek, a stronę marketingową odwiedził w piątek, nie przeszedł przez Twój lejek. Kolejność i czas przekształcają naiwny lejek oparty na flagach w wiarygodne rozwiązanie.

Pomysł pierwszego znacznika czasu dla każdego użytkownika

Aby analizować kolejność, zapisz pierwszy moment, w którym każdy użytkownik wykonał dany krok. Najwcześniejszą wizytę, najwcześniejszą rejestrację i najwcześniejszy zakup.

Wtedy poprawna konwersja oznacza, że first_signup_time >= first_visit_time i analogicznie dla kolejnych kroków. MIN(event_time) pogrupowane według kroku dostarcza tych punktów odniesienia.

SELECT
  user_id,
  MIN(CASE WHEN event_name = 'visit'    THEN event_time END) AS first_visit,
  MIN(CASE WHEN event_name = 'signup'   THEN event_time END) AS first_signup,
  MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS first_purchase
FROM events
GROUP BY user_id;

Wymaganie kolejności kroków

Mając pierwszy znacznik czasu dla każdego kroku, wymuszenie kolejności sprowadza się do porównania. Użytkownik rzeczywiście przeszedł do kroku 3 tylko wtedy, gdy każdy znacznik czasu jest niepusty i rośnie monotonicznie.

Zwróć uwagę, że znacznik czasu o wartości NULL (oznaczający, że krok nigdy nie nastąpił) naturalnie powoduje niepowodzenie porównania — dokładnie tego oczekujemy.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS signup_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS converted_in_order
FROM t
WHERE visit_t IS NOT NULL
  AND signup_t  >= visit_t
  AND purchase_t >= signup_t;

Dodawanie przedziału czasu

Większość lejków ma termin graniczny: „konwersja w ciągu 7 dni od pierwszej wizyty”. Dodaj ograniczenie przedziału między pierwszym krokiem a krokiem końcowym.

Operacje arytmetyczne na datach różnią się zależnie od dialektu. W Postgres można napisać visit_t + INTERVAL '7 days', a w MySQL użyć DATE_ADD(visit_t, INTERVAL 7 DAY). Zawsze należy określić używany dialekt.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS purchased_within_7d
FROM t
WHERE purchase_t >= visit_t
  AND purchase_t <  visit_t + INTERVAL '7 days';

Dlaczego pierwszy znacznik czasu, a nie dowolny

Subtelna kwestia rekrutacyjna: czy przedział czasu powinien zaczynać się od pierwszej wizyty użytkownika, czy od jego ostatniej wizyty przed rejestracją? Zależy to od pytania dotyczącego produktu.

  • Przedziały first-touch mierzą czas od początkowego zainteresowania do konwersji.
  • Przedziały last-touch mierzą czas potrzebny na konwersję po ostatniej wizycie.

Należy zapytać osobę rekrutującą, które znaczenie ma na myśli; świadomy wybór pokazuje doświadczenie.

Uporządkowane zdarzenia za pomocą LEAD

W przypadku złożonych ścieżek wieloetapowych doskonale sprawdzają się funkcje okna. Uporządkuj zdarzenia każdego użytkownika według czasu, a następnie użyj LEAD, aby sprawdzić następne zdarzenie i potwierdzić, że jest oczekiwanym kolejnym krokiem.

Takie podejście obsługuje ścieżki, w których między krokami występują niezwiązane zdarzenia.

SELECT
  user_id,
  event_name,
  event_time,
  LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
FROM events;

Dopasowanie oczekiwanego następnego kroku

Rozbuduj rozwiązanie o LEAD: zachowaj wiersze, w których po 'visit' bezpośrednio następuje 'signup'. Pozwala to znaleźć rzeczywiste przejścia sekwencyjne, a nie tylko współwystępowanie zdarzeń.

Można łączyć takie kontrole przejść, aby sprawdzać całą uporządkowaną ścieżkę krok po kroku.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event
  FROM events
)
SELECT COUNT(DISTINCT user_id) AS visit_then_signup
FROM seq
WHERE event_name = 'visit' AND next_event = 'signup';

Czas między kolejnymi krokami

Osoby rekrutujące uwielbiają pytanie: „ile czasu zajmuje każdy krok?”. Użyj LEAD dla znacznika czasu i odejmij wartości. Różnica między kolejnymi zdarzeniami to czas spędzony na danym etapie.

Agreguj medianę lub średnią dla każdego przejścia, aby znaleźć najwolniejszy etap lejka.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
  FROM events
)
SELECT
  event_name,
  AVG(EXTRACT(EPOCH FROM (next_time - event_time)) / 3600.0) AS avg_hours_to_next
FROM seq
WHERE next_time IS NOT NULL
GROUP BY event_name;

Przypadek brzegowy: identyczne znaczniki czasu

Co się stanie, jeśli dwa zdarzenia mają dokładnie ten sam event_time? Wtedy signup_t >= visit_t jest prawdziwe, nawet jeśli zdarzenia nastąpiły jednocześnie, a uporządkowanie wyłącznie według czasu jest niejednoznaczne.

  • Należy świadomie wybrać >= lub > i wyjaśnić dlaczego.
  • Dodaj element rozstrzygający remis, taki jak identyfikator sekwencji zdarzeń, do ORDER BY, aby działanie funkcji okna było deterministyczne.

Samodzielne wspomnienie o tym robi dobre wrażenie na osobach rekrutujących.

SELECT user_id, event_name,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS step_seq
FROM events;

Łączenie kolejności i przedziału w jednym zapytaniu

Oto kompletny lejek, w którym kroki występują we właściwej kolejności i mieszczą się w określonym przedziale czasu. Opiera się on na pierwszej wizycie, wymaga, aby pierwsze wystąpienie każdego kolejnego kroku nastąpiło po poprzednim, oraz ogranicza całą ścieżkę do 7 dni.

To rozwiązanie odróżnia kandydata, który rozumie lejki, od osoby, która potrafi jedynie zliczać flagi.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS v,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS s,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS p
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE v IS NOT NULL)                                   AS visited,
  COUNT(*) FILTER (WHERE s >= v AND s < v + INTERVAL '7 days')            AS signed_up,
  COUNT(*) FILTER (WHERE s >= v AND p >= s AND p < v + INTERVAL '7 days') AS purchased
FROM t;

Uwagi dotyczące różnych dialektów

Dwie kwestie związane z przenośnością, o których warto pamiętać podczas programowania na żywo:

  • FILTER (WHERE ...) przy agregatach jest standardem SQL i działa w Postgres; w MySQL lub starszych silnikach należy użyć SUM(CASE WHEN ... THEN 1 ELSE 0 END).
  • Składnia interwałów różni się zależnie od silnika: Postgres + INTERVAL '7 days', MySQL DATE_ADD(d, INTERVAL 7 DAY), SQL Server DATEADD(day, 7, d).

Należy jasno określić przyjęte założenie; osoba rekrutująca rzadko przejmuje się tym, którego dialektu używasz, o ile wiesz, że różnią się między sobą.

Szybkie sprawdzenie

Należy policzyć użytkowników, którzy w odpowiedniej kolejności przeszli przez etapy wizyta - rejestracja - zakup, w ciągu 7 dni od pierwszej wizyty. Które podejście jest poprawne?

Podsumowanie: zdarzenia w określonej kolejności i okna czasowe

Najważniejsze wnioski:

  • Należy przechwycić pierwszy znacznik czasu dla każdego kroku za pomocą MIN(CASE ...).
  • Sekwencję należy wymusić, wymagając, aby czas każdego kroku był nie wcześniejszy niż czas poprzedniego kroku.
  • Ścieżkę należy ograniczyć za pomocą interwału, podając składnię właściwą dla danego dialektu.
  • Należy używać LEAD/LAG do sprawdzania przejść i czasu między kolejnymi krokami.
  • Remisy z tym samym znacznikiem czasu należy rozstrzygać za pomocą dodatkowego kryterium w ORDER BY.

Dalej: przejście od lejków do eksperymentów i obliczanie metryk dla poszczególnych wariantów.

Często zadawane pytania

Czy lekcja „Uporządkowane zdarzenia i okna czasowe” jest bezpłatna?

Tak — pełny tekst „Uporządkowane zdarzenia i okna czasowe” 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 „Uporządkowane zdarzenia i okna czasowe”?

Zapewnianie kolejności etapów i ich wystąpienia w określonym limicie czasu za pomocą funkcji okna. Ć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 „Uporządkowane zdarzenia i okna czasowe”?

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. Budowanie wieloetapowego lejka
  2. Uporządkowane zdarzenia i okna czasowe
  3. Przypisywanie do testu A/B i metryki
  4. Wzrost, istotność i zabezpieczenia w SQL
← Powrót do SQL Interview Prep