0Pricing
SQL Interview Prep · Lekcja

Agregaty dla grup bez GROUP BY

Używanie podzapytania skorelowanego do obliczania maksimum grupy obok szczegółowych wierszy

Agregaty dla grup bez GROUP BY 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.

Problem szczegółów i agregatu

Klasyczne zadanie rekrutacyjne brzmi: „Pokaż każdy wiersz wraz z agregatem dla jego grupy.” Na przykład wyświetl każdego pracownika wraz z maksymalnym wynagrodzeniem w jego dziale w tym samym wierszu.

Zwykłe GROUP BY scala wiersze, więc nie może zachować szczegółów poszczególnych pracowników. Potrzebne są jednocześnie wiersze szczegółowe i liczba na poziomie grupy.

Podzapytanie skorelowane elegancko rozwiązuje ten problem: oblicza agregat grupy dla każdego wiersza szczegółowego, nie scalając żadnych wierszy.

Dlaczego zwykłe GROUP BY tutaj nie działa

Jeśli napiszą Państwo SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, otrzymają Państwo jeden wiersz na dział, tracąc imiona poszczególnych pracowników.

Dodanie name do SELECT bez dodania go do GROUP BY wywołuje klasyczny błąd „kolumna musi występować w GROUP BY”.

Rekruter sprawdza, czy rozumieją Państwo, że GROUP BY zmniejsza krotność wyników. Aby zachować wiersze szczegółowe, należy obliczyć agregat w inny sposób.

Podzapytanie skorelowane na ratunek

Proszę umieścić agregat grupy na liście SELECT jako podzapytanie skorelowane. Dla każdego wiersza pracownika zapytanie wewnętrzne uruchamia funkcję MAX ograniczoną do działu tego pracownika.

Korelacja e2.dept_id = e1.dept_id wiąże agregat z właściwą grupą, a zapytanie zewnętrzne nadal zwraca jeden wiersz na pracownika.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT MAX(e2.salary)
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;

Porównywanie każdego wiersza z jego grupą

Gdy agregat grupy znajduje się już w zapytaniu, można porównać z nim każdy wiersz. Częste pytanie brzmi: „Znajdź pracowników zarabiających powyżej średniej dla ich działu.”

W tym przypadku skorelowana funkcja AVG znajduje się w WHERE, więc każdy pracownik jest porównywany ze średnią dla własnego działu.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

Obliczanie różnicy względem grupy

Można również pokazać, jak bardzo każdy wiersz różni się od agregatu swojej grupy. Odjęcie skorelowanej średniej daje różnicę dla poszczególnych wierszy.

Proszę zauważyć, że to samo podzapytanie skorelowane można ponownie wykorzystać w wielu wyrażeniach SELECT; silnik oblicza je dla każdego wiersza za każdym razem, gdy się pojawia.

SELECT e1.name,
       e1.salary,
       e1.salary - (SELECT AVG(e2.salary)
                    FROM employees e2
                    WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;

Znajdowanie najlepiej zarabiającej osoby w każdej grupie

Aby zwrócić tylko najlepiej zarabiającą osobę w każdym dziale, należy porównać każde wynagrodzenie ze skorelowaną funkcją MAX i zachować pasujące wiersze.

Ten wzorzec zwraca remisy: jeśli dwóch pracowników ma najwyższe wynagrodzenie w dziale, pojawią się obaj. Obsługa remisów jest częstym pytaniem dodatkowym rekrutera.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

Alternatywa w postaci funkcji okna

Nowoczesny SQL oferuje wygodniejsze narzędzie: funkcje okna. MAX(salary) OVER (PARTITION BY dept_id) oblicza agregat grupy bez scalania wierszy i bez ponownego skanowania za pomocą podzapytania skorelowanego.

Rekruterzy doceniają możliwość przedstawienia obu rozwiązań i wyjaśnienia, że wersja z funkcją okna zwykle działa lepiej, ponieważ skanuje tabelę tylko raz.

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;

Kompromisy: podzapytanie skorelowane a funkcja okna

Oba podejścia zwracają wyniki o takim samym kształcie, ale różnią się pod następującymi względami:

  • Podzapytanie skorelowane: przenośne i działające nawet w bardzo starych silnikach, ale ponownie obliczane dla każdego wiersza.
  • Funkcja okna: pojedyncze przejście, znacznie większa szybkość na dużych tabelach, ale wymaga obsługi funkcji okna przez SQL.

Proszę powiedzieć, które rozwiązanie zostałoby wybrane i dlaczego. W przypadku jednorazowego zapytania na małej tabeli oba rozwiązania są odpowiednie; w analizach na dużą skalę należy preferować funkcję okna.

Przykład: zamówienia powyżej średniej klienta

Zastosujmy ten wzorzec do zamówień. Należy wyświetlić zamówienia, których kwota przekracza średnią wartość zamówień złożonych przez danego klienta.

Skorelowana funkcja AVG jest ograniczona przez o2.customer_id = o1.customer_id, dzięki czemu każde zamówienie ma własny punkt odniesienia klienta.

SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

Uwaga na wartości NULL i puste grupy

Jeśli grupa zawiera tylko jeden wiersz, jej średnia jest równa wartości tego wiersza, więc salary > avg jest fałszywe i wiersz zostaje odrzucony. Należy uprzedzić o tym przypadku brzegowym.

Wartości NULL w kolumnie wynagrodzeń są pomijane przez AVG i MAX, zgodnie z semantyką agregatów SQL. Jeśli wszystkie wartości w grupie są NULL, agregat również ma wartość NULL, a porównania dają UNKNOWN i wykluczają wiersz. Uwzględnienie tych przypadków wyróżnia wyczerpującą odpowiedź.

Zliczanie pozycji w rankingu w obrębie grupy

Pozycję w rankingu w obrębie grupy można wyrazić za pomocą skorelowanej funkcji COUNT. Aby znaleźć pozycję wynagrodzenia każdego pracownika w jego dziale, należy policzyć, ilu współpracowników zarabia więcej.

Pozycja 1 oznacza najlepiej zarabiającą osobę. Dodanie 1 zamienia liczbę osób zarabiających więcej w pozycję numerowaną od 1, a korelacja ogranicza obliczenia do danego działu.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT COUNT(*) + 1
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
          AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;

Szybkie sprawdzenie

Proszę wybrać powód, dla którego podzapytanie skorelowane jest lepsze od zwykłego GROUP BY w tym zadaniu.

Podsumowanie: agregaty grup bez GROUP BY

Najważniejsze wnioski:

  • Podzapytanie skorelowane umieszcza agregat na poziomie grupy w każdym wierszu szczegółowym, nie scalając tych wierszy.
  • Należy użyć go w SELECT, aby wyświetlić agregat, albo w WHERE, aby porównać każdy wiersz z jego grupą.
  • Wzorzec = MAX(...) zwraca wszystkie wiersze z remisem na najwyższej pozycji.
  • Funkcja okna z PARTITION BY wykonuje to samo w jednym przejściu i zwykle lepiej skaluje się wraz ze wzrostem danych.

Podczas rozmowy rekrutacyjnej warto przedstawić oba rozwiązania i uzasadnić wybór jednego z nich.

Często zadawane pytania

Czy lekcja „Agregaty dla grup bez GROUP BY” jest bezpłatna?

Tak — pełny tekst „Agregaty dla grup bez GROUP 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 SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Agregaty dla grup bez GROUP BY”?

Używanie podzapytania skorelowanego do obliczania maksimum grupy obok szczegółowych wierszy Ć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 „Agregaty dla grup bez GROUP 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 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. Anatomia podzapytania skorelowanego
  2. Agregaty dla grup bez GROUP BY
  3. Skorelowane EXISTS i NOT EXISTS
  4. Przepisywanie podzapytań skorelowanych jako złączeń
← Powrót do SQL Interview Prep