SQL Interview Prep · Lekcja

Średnie kroczące w przesuwanym oknie

Obliczanie średnich kroczących dla N okresów za pomocą BETWEEN PRECEDING AND CURRENT ROW

Średnie kroczące w przesuwanym oknie 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.

Pytanie o średnią kroczącą

Analitycy często słyszą pytanie: „Oblicz 7-dniową średnią kroczącą przychodu.” Średnia krocząca (zwana też ruchomą) wygładza zaszumione dane dzienne, uśredniając każdy punkt z jego niedawnymi sąsiadami.

Odpowiedź na poziomie rozmowy kwalifikacyjnej powinna zawierać funkcję okna AVG z jawnie określoną przesuwaną ramką. Kluczową umiejętnością jest dobranie granic ramki tak, aby okno prawidłowo przesuwało się zgodnie z kolejnością.

Podstawowy wzorzec

Średnia krocząca ma postać AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW). Ramka przesuwa się: dla każdego wiersza obejmuje bieżący wiersz oraz poprzednie n wierszy.

W przypadku 7-dniowego okna dla wierszy dziennych należy cofnąć się o 6 wierszy i uwzględnić bieżący, co daje łącznie 7 wierszy. Pomyłka o jeden jest najczęstszym błędem wychwytywanym przez osoby prowadzące rozmowę.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

Prawidłowe obliczanie rozmiaru okna

Rozmiar okna jest równy wartości preceding + 1 (dodatkowe 1 oznacza bieżący wiersz). Zatem:

okno 3-wierszowe: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
okno 7-wierszowe: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
okno 30-wierszowe: ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

Warto wypowiedzieć to działanie na głos podczas rozmowy, aby pokazać, że wybór jest przemyślany, a nie przypadkowy.

Okna wsteczne i wycentrowane

Powyższy przykład przedstawia średnią wsteczną: uwzględnia ona tylko wcześniejsze wartości, dzięki czemu jest przyczynowa i bezpieczna w panelach używanych do prognozowania.

Średnia wycentrowana uwzględnia wartości po obu stronach, na przykład ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING dla wycentrowanego okna 7-wierszowego. Okna wycentrowane wygładzają dane bardziej symetrycznie, ale w czasie rzeczywistym nie można ich obliczyć dla najnowszych wierszy. Należy wskazać, którego rodzaju wymaga dany przypadek użycia.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7
FROM daily_sales;

Efekt krawędzi okna

Na samym początku danych pełne okno nie jest jeszcze dostępne. Dla pierwszego wiersza 7-dniowej średniej wstecznej dostępny jest tylko 1 wiersz, więc AVG uśrednia tylko tę jedną wartość.

Oznacza to, że początkowe wiersze pokazują średnią rozgrzewkową obliczoną na podstawie mniejszej liczby wierszy. Podczas rozmowy kwalifikacyjnej często pada pytanie, jak sobie z tym poradzić. Są dwie możliwości: zaakceptować niepełne okno albo ukryć początkowe wiersze, wymagając pełnej liczności.

Ukrywanie niepełnych okien

Jeśli wymagane jest, aby do czasu uzyskania pełnego okna zwracana była wartość NULL, należy użyć COUNT(*) dla tej samej ramki i za pomocą wyrażenia CASE zastąpić wartości z niepełnych okien wartością NULL.

To dopracowany szczegół pokazujący, że uwzględniono poprawność na granicach, co pomija wielu kandydatów.

SELECT
  sale_date,
  CASE WHEN COUNT(*) OVER w = 7
       THEN AVG(amount) OVER w
  END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Ponowne użycie ramki za pomocą WINDOW

W poprzednim zapytaniu użyto nazwanej klauzuli WINDOW. Gdy ta sama ramka występuje wiele razy, zdefiniowanie jej raz jako WINDOW w AS (...) i odwoływanie się do niej za pomocą OVER w pozwala zachować zasadę DRY i czytelność zapytania.

Postgres, MySQL 8 i SQL Server obsługują nazwane okna. Użycie takiego okna podczas rozmowy kwalifikacyjnej sygnalizuje biegłość wykraczającą poza kopiowanie funkcji okienkowych.

SELECT
  sale_date,
  AVG(amount) OVER w AS avg_7d,
  SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Pułapka: dni kalendarzowe a wiersze

Istnieje tu subtelna pułapka: jeśli w tabeli brakuje dni, ROWS 6 PRECEDING obejmuje 7 ostatnich zarejestrowanych wierszy, które mogą obejmować więcej niż 7 dni kalendarzowych.

Aby obliczyć prawdziwą średnią 7-dniową opartą na kalendarzu i uwzględniającą luki, należy użyć RANGE z przesunięciem interwałowym albo najpierw połączyć dane z kompletną serią dat, tak aby każdy dzień miał swój wiersz. Wskazanie tej różnicy jest dokładnie wnioskiem dotyczącym ROWS i RANGE z poprzedniej lekcji.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS calendar_avg_7d
FROM daily_sales;

Średnie kroczące dla poszczególnych grup

Podobnie jak w przypadku sum narastających, średnie kroczące zwykle trzeba resetować dla każdej jednostki. Należy dodać PARTITION BY, aby każdy produkt lub sklep otrzymał własne okno kroczące, które nie przenika do innych grup.

Ramka i kolejność działają niezależnie wewnątrz każdej partycji, więc początkowe wiersze każdej grupy prawidłowo rozpoczynają własną fazę rozgrzewki.

SELECT
  product_id,
  sale_date,
  AVG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS product_ma_7d
FROM daily_sales;

Wygładzanie w praktyce

Po co to robić? Dzienny przychód jest nierównomierny, a średnia krocząca pozwala dostrzec trend. Częsta odpowiedź analityka polega na zestawieniu surowej wartości z jej wygładzoną linią, aby panel pokazywał zarówno sygnał, jak i szum.

Można również porównać krótką i długą średnią kroczącą (na przykład 7-dniową i 30-dniową), aby wykryć dynamikę — jest to odpowiednik przecięcia średnich kroczących w języku SQL.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma_7,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;

Lista kontrolna do rozmowy kwalifikacyjnej

Aby dobrze odpowiedzieć na pytanie o średnią kroczącą, należy omówić:

AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW).
Rozmiar okna = liczba wierszy preceding + 1.
Wybór między oknem wstecznym a wycentrowanym.
Rozgrzewkę dla niepełnego okna i sposób jej ukrycia.
ROWS a RANGE, gdy brakuje dni.
PARTITION BY do resetowania okna dla każdej jednostki.

Szybkie sprawdzenie

Wybierz ramkę dla 7-dniowej wstecznej średniej kroczącej obliczanej na podstawie wierszy dziennych.

Podsumowanie: średnie kroczące

Średnia krocząca ma postać AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW), gdzie rozmiar okna to liczba poprzedzających wierszy plus jeden. Należy wybrać okno wsteczne lub wycentrowane odpowiednio do przypadku użycia, obsłużyć wiersze rozgrzewkowe za pomocą warunku COUNT i przełączyć się na interwał RANGE, gdy znaczenie mają luki w kalendarzu.

Należy resetować obliczenia dla każdej jednostki za pomocą PARTITION BY i ponownie używać ramek dzięki nazwanej klauzuli WINDOW. Następnie zamienimy sumy skumulowane na wartości procentowe, obliczając narastający udział w całości.

Często zadawane pytania

Czy lekcja „Średnie kroczące w przesuwanym oknie” jest bezpłatna?

Tak — pełny tekst „Średnie kroczące w przesuwanym oknie” 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 „Średnie kroczące w przesuwanym oknie”?

Obliczanie średnich kroczących dla N okresów za pomocą BETWEEN PRECEDING AND CURRENT ROW Ć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 „Średnie kroczące w przesuwanym oknie”?

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