Coding Interview Prep · Lekcja

Sumy narastające z ramkami okienkowymi

Tworzenie sumy bieżącej za pomocą SUM OVER z uporządkowaną ramką

Sumy narastające z ramkami okienkowymi to bezpłatna lekcja Coding Interview Prep na CoddyKit. To lekcja 1 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 Coding Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.

Pytanie o sumę narastającą

Prawie każda rozmowa kwalifikacyjna dla analityka obejmuje pytanie w rodzaju: „Pokaż mi skumulowany przychód w czasie”. Suma narastająca to suma, która rośnie wiersz po wierszu, obejmując wszystkie wartości od początku aż do bieżącego wiersza.

Zanim pojawiły się funkcje okna, kandydaci rozwiązywali to za pomocą powolnego złączenia tabeli z samą sobą albo podzapytania skorelowanego. Współczesną, oczekiwaną odpowiedzią jest SUM(...) OVER (ORDER BY ...). Znajomość wersji wykorzystującej ramkę okna pokazuje, że rozumieją Państwo SQL pisany mniej więcej od 2012 roku.

Budowa uporządkowanej sumy okna

Suma narastająca to po prostu agregat przekształcony w funkcję okna. Zachowują Państwo SUM(amount), ale dodają klauzulę OVER z wyrażeniem ORDER BY.

To właśnie ORDER BY wewnątrz OVER nadaje sumie charakter narastający: informuje SQL, aby akumulował wiersze w tej kolejności. Bez ORDER BY funkcja SUM zsumowałaby całą partycję dla każdego wiersza, zamiast stopniowo zwiększać wynik.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Dlaczego ORDER BY implikuje ramkę

Oto szczegół, o który rekruterzy uwielbiają pytać: po dodaniu ORDER BY do agregatu okna SQL stosuje domyślną ramkę RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

To właśnie ta domyślna ramka tworzy sumę narastającą: każdy wiersz od początku partycji aż do bieżącego wiersza włącznie. Jeśli rozumieją Państwo tę wartość domyślną, rozumieją Państwo, dlaczego suma skumulowana „po prostu działa”.

Jawne określanie ramki

Ramkę można zapisać jawnie. Te dwa zapytania zwracają ten sam wynik, ale wersja jawna pokazuje rekruterowi, że wiedzą Państwo, co dzieje się pod spodem.

Zapisanie ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW jest najbezpieczniejszą jawną postacią sumy narastającej, ponieważ zlicza wiersze fizyczne i pozwala uniknąć niespodzianek związanych z grupowaniem wartości przez RANGE (omówionym w następnej lekcji).

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Przykład: dzienna sprzedaż

Wyobraźmy sobie sprzedaż z czterech dni: poniedziałek 100, wtorek 50, środa 200, czwartek 75. Suma narastająca rośnie od lewej do prawej.

Pon.: 100
Wt.: 100 + 50 = 150
Śr.: 150 + 200 = 350
Czw.: 350 + 75 = 425

Ostatni wiersz zawsze odpowiada sumie całkowitej. To szybka kontrola poprawności, o której można wspomnieć podczas rozmowy kwalifikacyjnej: ostatnia wartość sumy narastającej musi być równa wartości SUM(amount) dla całego zbioru.

Resetowanie dla każdej grupy za pomocą PARTITION BY

Rzeczywiste pytania zwykle dotyczą sumy narastającej dla każdego klienta lub dla każdego regionu, a nie jednej globalnej sumy. Należy dodać PARTITION BY, aby akumulacja rozpoczynała się od nowa na początku każdej partycji.

Sposób myślenia jest następujący: PARTITION BY dzieli wiersze na niezależne grupy, a wyrażenie ORDER BY wraz z ramką działa osobno w każdej z nich.

SELECT
  customer_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
  ) AS customer_running_total
FROM sales;

Pułapka rozstrzygania remisów

Jeśli dwa wiersze mają tę samą wartość ORDER BY (dwie sprzedaże z tego samego dnia), domyślna ramka RANGE traktuje je jako wiersze równorzędne i nadaje im tę samą sumę narastającą, uwzględniając obie kwoty.

Jeśli potrzebują Państwo ścisłego zwiększania wyniku wiersz po wierszu nawet przy remisach, należy przełączyć ramkę na ROWS i dodać do ORDER BY unikalny element rozstrzygający remis, na przykład sale_date, id. Rekruterzy celowo umieszczają zduplikowane daty, aby sprawdzić, czy Państwo to zauważą.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Narastające zliczanie

Logika kumulacyjna nie ogranicza się do funkcji SUM. Każdy agregat może działać jako funkcja okna, dlatego można tworzyć narastające zliczenie, narastającą średnią lub narastające maksimum.

Narastająca liczba zamówień to częsta miara na pulpicie: ile zamówień przyjęto do danego dnia?

SELECT
  order_date,
  COUNT(*) OVER (
    ORDER BY order_date
  ) AS orders_to_date
FROM orders;

Stary sposób: podzapytanie skorelowane

Rekruterzy czasami proszą o rozwiązanie problemu sumy narastającej bez funkcji okna, aby sprawdzić głębsze zrozumienie tematu. Klasycznym rozwiązaniem sprzed funkcji okna jest podzapytanie skorelowane, które ponownie sumuje każdy wcześniejszy wiersz.

To działa, ale ma złożoność O(n²): dla każdego wiersza ponownie skanuje tabelę. Warto o tym wspomnieć, aby pokazać, że rozumieją Państwo, dlaczego funkcje okna wyparły to rozwiązanie.

SELECT
  s.sale_date,
  s.amount,
  (SELECT SUM(s2.amount)
   FROM sales s2
   WHERE s2.sale_date <= s.sale_date) AS running_total
FROM sales s
ORDER BY s.sale_date;

Filtrowanie a wynik funkcji okna

Częste pytanie dodatkowe brzmi: „Pokaż tylko dni, w których suma narastająca przekroczyła 1000”. Nie można umieścić funkcji okna w WHERE, ponieważ ramka jest obliczana po wykonaniu WHERE.

Rozwiązaniem jest obliczenie sumy narastającej w CTE lub podzapytaniu, a następnie przefiltrowanie zapytania zewnętrznego. Ta sama zasada opakowywania zapytania dotyczy każdej funkcji okna.

WITH t AS (
  SELECT
    sale_date,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales
)
SELECT *
FROM t
WHERE running_total >= 1000;

Najważniejsze punkty na rozmowę

Przedstawiając rozwiązanie dotyczące sumy narastającej, warto omówić poniższe punkty, aby uzyskać pełną punktację:

SUM OVER (ORDER BY ...) to postać kumulacyjna.
Dodanie ORDER BY tworzy domyślną ramkę od UNBOUNDED PRECEDING do CURRENT ROW.
Należy użyć PARTITION BY, aby resetować wynik dla każdej grupy.
Należy dodać unikalny element rozstrzygający remis oraz ramkę ROWS, aby uniknąć problemu z powtarzającymi się wartościami.
Należy użyć CTE, aby filtrować według wyniku.

Szybki test

Sprawdźmy, czy rozumieją Państwo domyślną ramkę.

Podsumowanie: sumy narastające

Suma narastająca to uporządkowany agregat okna. SUM(amount) OVER (ORDER BY sale_date) akumuluje wiersze od początku partycji do bieżącego wiersza dzięki niejawnej ramce od UNBOUNDED PRECEDING-do-CURRENT ROW.

Aby resetować wynik dla każdej grupy, należy użyć PARTITION BY. Aby obsłużyć zduplikowane wartości sortowania, należy dodać element rozstrzygający remis oraz ramkę ROWS. Gdy trzeba filtrować według wartości kumulacyjnej, należy umieścić zapytanie w CTE. W następnej części przeanalizujemy różnicę między ROWS a RANGE, na którą ta lekcja tylko wskazała.

Często zadawane pytania

Czy lekcja „Sumy narastające z ramkami okienkowymi” jest bezpłatna?

Tak — pełny tekst „Sumy narastające z ramkami okienkowymi” 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 Coding Interview Prep, przejdź na CoddyKit PRO. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Sumy narastające z ramkami okienkowymi”?

Tworzenie sumy bieżącej za pomocą SUM OVER z uporządkowaną ramką Ćwiczysz Coding 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ąć Coding Interview Prep?

Nie wymagamy żadnego doświadczenia. Coding 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 1 z 4.

Ile czasu zajmuje lekcja „Sumy narastające z ramkami okienkowymi”?

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 Coding Interview Prep?

Tak. Każda lekcja Coding 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 Coding Interview Prep