SQL Interview Prep · Lekcja

Tabela przestawna z agregacją warunkową

Przenośny wzorzec CASE wewnątrz SUM, służący do przekształcania wierszy w kolumny.

Tabela przestawna z agregacją warunkową to bezpłatna lekcja SQL 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 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.

Konfiguracja zadania rekrutacyjnego

Jedno z najczęstszych zadań na rozmowach rekrutacyjnych dotyczących raportowania polega na tym, aby zamienić wiersze na kolumny. Masz długą tabelę, taką jak sales(region, quarter, amount), a rekruter oczekuje szerokiego raportu z jedną kolumną na każdy kwartał.

Przenośna, niezależna od dialektu odpowiedź, którą należy podać, to agregacja warunkowa: wyrażenie CASE umieszczone wewnątrz funkcji agregującej, takiej jak SUM. Po opanowaniu tego wzorca można pivotować dane w dowolnej bazie danych, nawet takiej, która nie ma słowa kluczowego PIVOT.

Forma długa a forma szeroka

Przed pivotowaniem należy nazwać oba kształty danych. Forma długa przechowuje jeden fakt w każdym wierszu: każda para region/kwartał znajduje się w osobnym wierszu. Forma szeroka rozkłada kategorię na wiele kolumn.

Forma długa: łatwa do rozszerzania, trudna do odczytania obok siebie.
Forma szeroka: świetna do raportu przeznaczonego dla człowieka.

Pivot przekształca formę długą w szeroką. Rekruterzy lubią ten temat, ponieważ sprawdza, czy rozumiesz agregację, a nie tylko składnię.

-- Long form (the input)
region | quarter | amount
-------+---------+-------
East   | Q1      | 100
East   | Q2      | 150
West   | Q1      | 200
West   | Q2      | 250

Podstawowy wzorzec

Sposób jest następujący: dla każdej kolumny wyniku należy napisać CASE, który zwraca wartość, gdy wiersz pasuje do tej kolumny, a w przeciwnym razie zwraca NULL. Następnie należy opakować go w funkcję agregującą, aby każda grupa została sprowadzona do jednego wiersza na klucz.

Należy odczytywać to jako: zsumuj kwotę, ale tylko dla wierszy Q1. Ponieważ SUM ignoruje NULL, niepasujące wiersze nie wnoszą żadnego wkładu.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

Dlaczego SUM ignoruje NULL

Ten wzorzec działa dzięki jednej zasadzie, o którą rekruterzy często dopytują: funkcje agregujące pomijają wartości NULL. CASE bez klauzuli ELSE zwraca NULL, gdy żadna gałąź nie pasuje, więc SUM(CASE WHEN ... THEN amount END) dodaje tylko wybrane wiersze.

Gdyby użyto ELSE 0, rozwiązanie nadal działałoby dla SUM (dodanie zera niczego nie zmienia), ale zepsułoby działanie AVG, MIN i COUNT.

-- Both produce the same SUM result:
SUM(CASE WHEN quarter = 'Q1' THEN amount END)
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END)

Przykład z rozwiązaniem: raport kwartalny

Oto pełne zapytanie dla przykładowych danych. Każdy region staje się jednym wierszem, a każdy kwartał — jedną kolumną.

To właśnie GROUP BY region redukuje cztery wiersze wejściowe do dwóch wierszy wyjściowych. Bez niego otrzymaliby Państwo po jednym wierszu na każdy wiersz wejściowy, w większości z wartościami NULL.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

-- Result:
-- region | q1  | q2
-- East   | 100 | 150
-- West   | 200 | 250

Wybór właściwej funkcji agregującej

Funkcja agregująca, którą opakowują Państwo wyrażenie CASE, musi odpowiadać pytaniu:

SUM, gdy każda komórka sumuje wartości.
MAX lub MIN, gdy każda para region/kwartał ma dokładnie jedną wartość i chcą ją Państwo jedynie wyświetlić.
COUNT, gdy każda komórka zlicza pasujące wiersze.

Rekruterzy często pytają o wariant z COUNT: ile zamówień przypada na każdy status w każdym miesiącu?

SELECT
  month,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY month;

MAX dla komórek z jedną wartością

Gdy każda para klucz/kategoria zawiera pojedynczą wartość (czyli mamy prawdziwą tabelę krzyżową, a nie sumę), należy użyć MAX lub MIN. Obie funkcje zwracają jedyną wartość różną od NULL i pomijają wartości NULL z niedopasowanych gałęzi.

To bezpieczny wybór podczas przekształcania atrybutów zamiast sumowania kwot, na przykład przy przekształcaniu tabeli ustawień klucz/wartość do postaci jednego wiersza na encję.

-- Turn key/value rows into one wide row per user
SELECT
  user_id,
  MAX(CASE WHEN attr = 'city'  THEN value END) AS city,
  MAX(CASE WHEN attr = 'plan'  THEN value END) AS plan
FROM user_attributes
GROUP BY user_id;

Obsługa wyjściowych komórek NULL

Jeśli region nie miał sprzedaży w drugim kwartale, jego komórka q2 będzie miała wartość NULL. Rekruter może poprosić o wyświetlenie w jej miejsce wartości 0. Należy opakować całą funkcję agregującą w COALESCE.

COALESCE należy umieścić na zewnątrz funkcji agregującej, a nie wewnątrz wyrażenia CASE, aby podstawiać wartość tylko wtedy, gdy cała grupa nie zawiera pasujących wierszy.

SELECT
  region,
  COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN amount END), 0) AS q1,
  COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN amount END), 0) AS q2
FROM sales
GROUP BY region;

Dodawanie kolumny sumy całkowitej

Częsta prośba uzupełniająca brzmi: dodać sumę wszystkich kolumn tabeli przestawnej. Nie trzeba dodawać kolumn po nazwie. Zwykłe SUM(amount) w tej samej grupie zwróci sumę wiersza, ponieważ całkowicie ignoruje filtrowanie za pomocą CASE.

Pokazuje to rekruterowi, że rozumieją Państwo, iż każda funkcja agregująca w klauzuli SELECT jest obliczana niezależnie dla tej samej grupy.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(amount) AS total
FROM sales
GROUP BY region;

Skrócona składnia agregacji filtrowanej

PostgreSQL i standard SQL obsługują składnię FILTER (WHERE ...), która pozwala czytelniej zapisywać agregację warunkową. Kod jest łatwiejszy do odczytania i nie zawiera dodatkowego szablonu CASE.

Warto wspomnieć o tym podczas rozmowy kwalifikacyjnej, aby pokazać szerszą znajomość SQL, ale należy pamiętać, że MySQL i SQL Server tego nie obsługują, dlatego CASE pozostaje rozwiązaniem przenośnym.

-- Postgres / standard SQL
SELECT
  region,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY region;

Najważniejsze ograniczenie

Agregacja warunkowa ma jedno ograniczenie, o które rekruterzy z pewnością dopytają: każdą kolumnę wyjściową trzeba wypisać ręcznie. Jeśli kwartały lub kategorie nie są znane z wyprzedzeniem, to statyczne zapytanie nie może się dostosować.

Ten problem nazywa się dynamiczną tabelą przestawną i wymaga generowanego SQL. W przypadku stałego, znanego zestawu kategorii agregacja warunkowa pozostaje jednak prostym i przenośnym rozwiązaniem.

Szybkie sprawdzenie

Sprawdźmy, czy rozumieją Państwo wzorzec agregacji warunkowej.

Podsumowanie

Agregacja warunkowa to przenośna tabela przestawna, którą zaakceptuje każdy rekruter:

Jedno wyrażenie CASE na każdą kolumnę wyjściową, opakowane w funkcję agregującą.
SUM dla sum, MAX/MIN dla komórek z pojedynczą wartością, COUNT dla zliczeń.
Działa, ponieważ funkcje agregujące ignorują wartość NULL z niedopasowanych gałęzi.
Należy użyć COALESCE, aby zamienić puste komórki na 0.
Ograniczenie: kolumny muszą być zapisane na stałe, co prowadzi do omówienia dynamicznych tabel przestawnych.

Często zadawane pytania

Czy lekcja „Tabela przestawna z agregacją warunkową” jest bezpłatna?

Tak — pełny tekst „Tabela przestawna z agregacją warunkową” 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 „Tabela przestawna z agregacją warunkową”?

Przenośny wzorzec CASE wewnątrz SUM, służący do przekształcania wierszy w kolumny. Ć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 1 z 4.

Ile czasu zajmuje lekcja „Tabela przestawna z agregacją warunkową”?

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

Tabela przestawna z agregacją warunkową
Składnia PIVOT i crosstab dostawców
Przekształcanie kolumn w wiersze
Dynamiczne tabele przestawne z nieznanymi kolumnami

← Powrót do SQL Interview Prep