0Pricing
Coding Interview Prep · Lekcja

OVER, PARTITION BY i ORDER BY

Anatomia specyfikacji okna oraz sposób, w jaki partycje zerują obliczenia

OVER, PARTITION BY i ORDER BY 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.

Dlaczego osoby prowadzące rozmowy sięgają po funkcje okna

Funkcja okna wykonuje obliczenie na zbiorze wierszy powiązanych z bieżącym wierszem, nie redukując ich tak jak GROUP BY. Właśnie dlatego osoby prowadzące rozmowy tak chętnie sprawdzają ich znajomość: można zachować każdy wiersz szczegółowy, a jednocześnie umieścić obok niego agregat, rangę lub sumę narastającą.

  • GROUP BY zwraca jeden wiersz na grupę.
  • Funkcja okna zwraca każdy wiersz wejściowy wraz z dodatkową obliczoną kolumną.

Gdy osoba prowadząca rozmowę mówi „pokaż każdego pracownika oraz średnie wynagrodzenie w jego dziale w tym samym wierszu”, sprawdza, czy użyjesz funkcji okna zamiast self-join.

Budowa klauzuli OVER

Po każdej funkcji okna występuje klauzula OVER (...). Klauzula ma trzy opcjonalne elementy, a precyzyjne ich nazwanie robi dobre wrażenie podczas rozmowy kwalifikacyjnej:

  • PARTITION BY — dzieli wiersze na grupy; funkcja rozpoczyna działanie od nowa w każdej z nich.
  • ORDER BY — porządkuje wiersze wewnątrz każdej partycji; jest potrzebne do rangowania i sum narastających.
  • ramka — ogranicza zbiór wierszy uwzględnianych w obliczeniu (ROWS/RANGE).

Pusta klauzula OVER () traktuje cały zbiór wynikowy jako jedną partycję.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Funkcja okna a agregacja: ta sama funkcja, inny wynik

Dokładnie ta sama funkcja agregująca zachowuje się inaczej użyta jako funkcja okna. Poniższe dwa zapytania można porównać na poziomie koncepcji.

  • AVG(salary) z GROUP BY department zwraca jeden wiersz na dział.
  • AVG(salary) OVER (PARTITION BY department) zwraca każdego pracownika wraz ze średnią dla jego działu.

Wskazówka na rozmowę kwalifikacyjną: warto podkreślić, że wersja okienkowa nie wymaga GROUP BY i nie usuwa powtarzających się wierszy szczegółowych.

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY: Resetowanie obliczeń

PARTITION BY działa dla funkcji okienkowych tak jak GROUP BY dla agregacji, z tą różnicą, że nie scala wierszy. Każda unikalna wartość partycji otrzymuje własne, niezależne obliczenie.

W tym przykładzie numeracja wierszy rozpoczyna się od 1 dla każdego działu. Bez PARTITION BY numeracja przebiegałaby ciągle przez wszystkich pracowników.

  • Partycjonować można według jednej kolumny lub kilku kolumn.
  • Brak PARTITION BY oznacza jedną dużą partycję obejmującą cały zbiór.
SELECT
  department,
  name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

ORDER BY wewnątrz OVER

ORDER BY wewnątrz OVER to nie to samo co końcowe ORDER BY zapytania. Określa ono jedynie kolejność wierszy w obrębie każdej partycji, według której działa funkcja.

  • Funkcje rankingowe (ROW_NUMBER, RANK) wymagają tej klauzuli — potrzebują kolejności, według której będą przyznawać rangi.
  • Zwykłe funkcje agregujące działające na partycji jej nie potrzebują, chyba że chcą Państwo obliczać wartości narastająco.

Częstym błędem podczas rozmów kwalifikacyjnych jest mylenie okna ORDER BY z kolejnością prezentacji wyników.

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

Łączenie PARTITION BY i ORDER BY

Klasyczna funkcja okienkowa do rankingu łączy oba elementy: PARTITION BY tworzy grupy, a następnie ORDER BY ustala kolejność wewnątrz każdej grupy.

Poniższą specyfikację należy odczytać tak: „W obrębie każdego działu uporządkuj pracowników według malejącego wynagrodzenia i ponumeruj ich”. Najlepiej zarabiająca osoba w każdym dziale otrzyma numer wiersza 1.

Ta pojedyncza specyfikacja stanowi podstawę najczęstszych zadań rekrutacyjnych dotyczących funkcji okienkowych, w tym problemów typu top-N-per-group.

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

ORDER BY zmienia działanie funkcji agregującej

Oto subtelna kwestia, o którą często pytają rekruterzy: dodanie ORDER BY do agregacji okienkowej zmienia ją w obliczenie narastające, ponieważ zostaje zastosowana niejawna ramka („od początku partycji do bieżącego wiersza”).

  • SUM(x) OVER (PARTITION BY g) → ta sama suma grupy w każdym wierszu.
  • SUM(x) OVER (PARTITION BY g ORDER BY d) → suma narastająca do bieżącego wiersza.

Świadomość, że ORDER BY niejawnie dodaje ramkę, odróżnia kandydatów na poziomie średnio zaawansowanym od kandydatów początkujących.

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

Gdzie można używać funkcji okienkowych

Funkcje okienkowe mogą występować wyłącznie na liście SELECT oraz w klauzuli ORDER BY. Nie można ich używać w klauzulach WHERE, GROUP BY ani HAVING.

Wynika to z logicznej kolejności wykonywania: funkcje okienkowe są obliczane po wykonaniu WHERE, GROUP BY i HAVING. Wiersze są już wybrane, zanim funkcja okienkowa zacznie je przetwarzać.

Dlatego filtrowanie według rankingu wymaga podzapytania lub CTE — ta kwestia zostanie szczegółowo omówiona w późniejszej lekcji.

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Wiele funkcji okienkowych w jednym zapytaniu

W tym samym poleceniu SELECT można użyć kilku funkcji okienkowych, z których każda ma własną specyfikację albo korzysta ze wspólnej. Baza danych oblicza je podczas jednego przebiegu po danych podzielonych na partycje.

Jest to przydatne podczas rozmów kwalifikacyjnych, gdy trzeba jednocześnie wyświetlić rangę i średnią dla działu. Jeśli dwie funkcje korzystają z tej samej specyfikacji, niektóre dialekty pozwalają nadać jej nazwę za pomocą klauzuli WINDOW, aby uniknąć powtórzeń.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w  AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Przykład: wynagrodzenie a średnia dla działu

Częste pytanie analityczne brzmi: „Wyświetl każdego pracownika wraz z jego wynagrodzeniem, średnią dla działu i różnicą”. Jedno wyrażenie okienkowe wykonuje najważniejszą część pracy, a resztę zapewnia arytmetyka.

Proszę zauważyć, że nie ma tu GROUP BY i każdy wiersz pracownika zostaje zachowany. Wartość dept_avg powtarza się dla wszystkich osób w tym samym dziale, co umożliwia porównanie wiersz po wierszu.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Częste błędy, na które zwracają uwagę rekruterzy

Proszę unikać następujących pułapek, gdy pojawiają się funkcje okienkowe:

  • Umieszczanie funkcji okienkowej w WHERE lub HAVING — jest to niedozwolone; należy użyć podzapytania.
  • Pomijanie ORDER BY przy funkcji rankingowej — wyniki stają się przypadkowe.
  • Zakładanie, że PARTITION BY zmniejsza liczbę wierszy — nigdy tego nie robi.
  • Mylenie okna ORDER BY z końcową kolejnością wyników.
  • Dodawanie ORDER BY do agregacji okienkowej bez świadomości, że zmienia się ona w sumę narastającą.

Szybki test

Sprawdź, czy rozumiesz specyfikację okna.

Podsumowanie: specyfikacja okna

Znają już Państwo budowę OVER (...):

  • Funkcje okienkowe zachowują każdy wiersz, obliczając wartości na podstawie powiązanych wierszy.
  • PARTITION BY tworzy grupy i rozpoczyna obliczenia od nowa; nigdy nie usuwa wierszy.
  • ORDER BY ustala kolejność wierszy w obrębie partycji; funkcje rankingowe go wymagają, a w przypadku agregacji zmienia je w obliczenia narastające.
  • Funkcje okienkowe są dozwolone tylko w SELECT i ORDER BY — nigdy w WHERE/HAVING.

Następnie przypiszą Państwo deterministyczne numery porządkowe za pomocą ROW_NUMBER.

Często zadawane pytania

Czy lekcja „OVER, PARTITION BY i ORDER BY” jest bezpłatna?

Tak — pełny tekst „OVER, PARTITION BY i ORDER BY” 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 „OVER, PARTITION BY i ORDER BY”?

Anatomia specyfikacji okna oraz sposób, w jaki partycje zerują obliczenia Ć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 „OVER, PARTITION BY i ORDER BY”?

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

  1. OVER, PARTITION BY i ORDER BY
  2. ROW_NUMBER do unikatowej numeracji
  3. RANK a DENSE_RANK przy remisach
  4. Filtrowanie wyniku funkcji okienkowej
← Powrót do Coding Interview Prep