Zmiany okres do okresu
Obliczanie wzrostu miesiąc do miesiąca i różnic dzień do dnia za pomocą LAG
Zmiany okres do okresu 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.
Gwarantowane pytanie dla analityka
Jeśli ubiegają się Państwo o stanowisko analityka danych, należy spodziewać się pytań: „Oblicz wzrost miesiąc do miesiąca” lub „Jaka jest zmiana dzień do dnia?” Tego niemal nie da się uniknąć.
Podstawą jest LAG: pobranie wartości z poprzedniego okresu, a następnie obliczenie zmiany bezwzględnej lub procentowej. W tej lekcji pokażemy wzorce użycia LAG dla zmian względem poprzedniego okresu, które rekruterzy sprawdzają podczas rozmów.
Zmiana bezwzględna za pomocą LAG
Najprostsza wersja to surowa różnica między bieżącym okresem a poprzednim. Pobierz poprzednią wartość za pomocą LAG i odejmij ją bezpośrednio w wyrażeniu.
Pierwszy wiersz w uporządkowanym wyniku nie ma poprzednika, dlatego jego zmiana wynosi NULL. To poprawne zachowanie, a nie błąd: rzeczywiście nie ma wcześniejszego okresu, z którym można by go porównać.
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_sales
ORDER BY month;Zmiana procentowa
Rekruterzy zazwyczaj oczekują wyrażenia wzrostu w procentach. Wzór to (bieżąca wartość - poprzednia wartość) / poprzednia wartość, pomnożone przez 100. Wyraź go bezpośrednio za pomocą LAG.
Należy uważać na kolejność działań i nawiasy; niewłaściwie umieszczony nawias to klasyczny błąd popełniany pod presją.
SELECT
month,
revenue,
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month) AS pct_growth
FROM monthly_sales
ORDER BY month;Unikanie dzielenia całkowitoliczbowego
Wielu kandydatów wpada w następującą pułapkę: jeśli revenue jest liczbą całkowitą, wyrażenie (110 - 100) / 100 daje wynik 0 w bazach danych wykonujących dzielenie całkowitoliczbowe.
Najpierw pomnóż przez 100.0 (literał typu float) albo użyj CAST, aby rzutować wartość na typ dziesiętny i wymusić działania zmiennoprzecinkowe. Wspomnienie o tym z własnej inicjatywy pokazuje dbałość o szczegóły.
SELECT
month,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 2
) AS pct_growth
FROM monthly_sales;Czytelniej: oblicz raz w CTE
Dwukrotne wywoływanie LAG (dla licznika i mianownika) jest powtarzalne i łatwo popełnić w nim literówkę. Częstą refaktoryzacją jest jednokrotne obliczenie poprzedniej wartości w CTE, a następnie wykonanie działań arytmetycznych w zewnętrznym zapytaniu.
Taki zapis lepiej sprawdza się podczas rozmowy przy tablicy i zapobiega rozbieżnościom między dwoma wywołaniami LAG.
WITH t AS (
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev
FROM monthly_sales
)
SELECT
month,
revenue,
ROUND(100.0 * (revenue - prev) / prev, 2) AS pct_growth
FROM t;Zabezpieczenie przed dzieleniem przez zero
Jeśli wartość z poprzedniego okresu może wynosić 0, wzór na zmianę procentową spowoduje dzielenie przez zero i zakończy się błędem. Użyj NULLIF(prev, 0), aby mianownik stał się równy NULL, a wynik przyjął wartość NULL zamiast powodować wyjątek.
Ostrożne podejście do dzielenia to drobny szczegół, na który rekruterzy zwracają uwagę u kandydatów myślących o środowisku produkcyjnym.
SELECT
month,
revenue,
100.0 * (revenue - prev) / NULLIF(prev, 0) AS pct_growth
FROM (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev
FROM monthly_sales
) s;Zmiana okres do okresu w obrębie grup
Większość rzeczywistych pytań dotyczy konkretnego zakresu: wzrostu miesiąc do miesiąca dla produktu lub dla regionu. Dodaj PARTITION BY, aby każda seria w grupie była porównywana wyłącznie z samą sobą.
Najwcześniejszy miesiąc w każdej grupie otrzymuje ponownie wartość NULL; porównanie nigdy nie odbywa się między różnymi produktami.
SELECT
product_id,
month,
revenue,
revenue - LAG(revenue) OVER (
PARTITION BY product_id
ORDER BY month
) AS mom_change
FROM product_sales;Zmiana rok do roku z przesunięciem
W przypadku zmiany rok do roku dla danych miesięcznych, w których wiersze dzieli jeden miesiąc, należy porównać wartość z wartością sprzed 12 wierszy. Argument przesunięcia pozwala zrobić to bezpośrednio: LAG(revenue, 12).
Zakłada to jeden wiersz na miesiąc i brak luk. Jeśli niektórych miesięcy może brakować, należy zamiast tego połączyć dane na podstawie jawnie określonej daty — to istotny szczegół, który warto wyraźnie zaznaczyć.
SELECT
month,
revenue,
revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_change
FROM monthly_sales
ORDER BY month;Agreguj przed porównaniem
Surowe tabele zdarzeń nie są wcześniej sumowane według miesięcy. Realistyczne rozwiązanie najpierw agreguje dane do jednego wiersza na okres, a następnie stosuje LAG do zagregowanego wyniku, zwykle za pośrednictwem CTE.
Próba zastosowania LAG do nieagregowanych wierszy porównuje pojedyncze transakcje, a nie sumy miesięczne, co jest częstym błędem logicznym.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS mth,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT mth, revenue,
revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
FROM monthly
ORDER BY mth;Interpretacja pierwszego NULL
Rekruterzy czasami pytają: „Dlaczego wzrost w pierwszym wierszu ma wartość NULL i czy jest to akceptowalne?”. Właściwa odpowiedź brzmi: nie ma poprzedniego okresu, więc zmiana jest niezdefiniowana, a NULL poprawnie to reprezentuje.
Jeśli firma oczekuje zamiast tego wartości 0, można użyć LAG(revenue, 1, 0) lub COALESCE, ale tylko wtedy, gdy odpowiada to zamierzonemu znaczeniu.
Oznaczanie kierunku wzrostu
Rekruterzy czasami rozszerzają pytanie: „Proszę również oznaczyć, czy każdy miesiąc przyniósł wzrost, spadek, czy pozostał bez zmian”. Porównanie za pomocą LAG należy umieścić w wyrażeniu CASE.
Porównanie z poprzednią wartością daje czytelną dla człowieka kolumnę trendu, uzupełniającą liczbową zmianę, i poprawnie obsługuje NULL lub domyślną etykietę dla pierwszego okresu, który nie ma punktu odniesienia.
SELECT
month,
revenue,
CASE
WHEN revenue > LAG(revenue) OVER (ORDER BY month) THEN 'up'
WHEN revenue < LAG(revenue) OVER (ORDER BY month) THEN 'down'
ELSE 'flat'
END AS trend
FROM monthly_sales
ORDER BY month;Szybkie sprawdzenie
Wskaż częsty błąd w zapytaniach obliczających zmianę procentową.
Podsumowanie
Zmiana względem poprzedniego okresu to LAG połączone z działaniami arytmetycznymi:
- Zmiana bezwzględna:
value - LAG(value) OVER (ORDER BY period). - Zmiana procentowa:
100.0 * (value - prev) / NULLIF(prev, 0). - Wymuś działania zmiennoprzecinkowe, zabezpiecz się przed dzieleniem przez zero i najpierw zagreguj dane do jednego wiersza na okres.
- Określ zakres za pomocą
PARTITION BY; do zmiany rok do roku użyj przesunięcia.
W następnej części: dzielenie wierszy na koszyki i poziomy za pomocą NTILE.
Często zadawane pytania
Czy lekcja „Zmiany okres do okresu” jest bezpłatna?
Tak — pełny tekst „Zmiany okres do okresu” 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 „Zmiany okres do okresu”?
Obliczanie wzrostu miesiąc do miesiąca i różnic dzień do dnia za pomocą LAG Ć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 „Zmiany okres do okresu”?
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
- LAG i LEAD dla sąsiednich wierszy
- Zmiany okres do okresu
- NTILE do tworzenia przedziałów
- FIRST_VALUE, LAST_VALUE i krawędzie ramki