Tabela przestawna w SQL: CASE WHEN, COUNT i pułapki NULL

W Excelu przeciągam pole do kolumn i po chwili mam tabelę przestawną. W SQL ten sam efekt mogę zbudować kilkoma wyrażeniami CASE WHEN. Trudność pojawia się później: jedna metoda dostawy znika z podsumowania, pusta komórka wygląda jak zero, a suma kolumn przestaje pasować do liczby zamówień. Sam układ tabeli nie potwierdza poprawności obliczeń.

Przejdę przez przykład, w którym jeden wiersz oznacza zamówienie, a wynik pokazuje metody dostawy osobno dla każdego roku. Używam ośmiu syntetycznych rekordów i SQLite. Celowo dodaję brakującą metodę, nową kategorię, zerową kwotę oraz nieznane kwoty. Dzięki temu każde uproszczenie od razu widać w wyniku, zamiast odkrywać je dopiero w miesięcznym raporcie.

Do wykonania ćwiczenia wystarczą podstawy SQL. Kod uruchamiaj kolejno w nowej lokalnej bazie. Nie wymaga dodatkowego rozszerzenia ani polecenia PIVOT. Agregacja warunkowa to przydatna technika również wtedy, gdy silnik ma własną specjalną składnię tabel przestawnych.

Najpierw ustalam, co ma oznaczać komórka

Wierszem wyniku będzie rok, kolumną metoda dostawy, a komórką liczba zamówień. To trzy osobne decyzje. Gdy zamienię liczbę zamówień na sumę kwot, układ pozostanie podobny, lecz obsługa braków będzie wymagać innego wyjaśnienia. Nie traktuję miary jako drobnego ustawienia kosmetycznego na końcu pracy.

Jeden rekord wejściowy odpowiada tu jednemu zamówieniu, identyfikowanemu przez klucz główny. Jeżeli Twoje źródło zawiera pozycje zamówień, najpierw ustal sposób przejścia do poziomu zamówienia. Policzenie wierszy po połączeniu z pozycjami może zwiększyć wynik. Dodanie DISTINCT bez zrozumienia relacji czasem maskuje problem, ale nie wyjaśnia, skąd się wziął.

Metoda dostawy ma w przykładzie jedną wartość na zamówienie. Jeżeli rzeczywisty proces dopuszcza kilka przesyłek różnymi metodami, potrzebujesz decyzji: liczysz zamówienia, przesyłki czy wystąpienia metody? Bez tej definicji dwa poprawne technicznie zapytania mogą odpowiadać na inne pytania. Zaczynam od znaczenia, dopiero potem wybieram agregację.

Przygotowuję dane z celowymi przypadkami brzegowymi

-- example: fixture
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_year INTEGER NOT NULL,
    shipping_mode TEXT,
    amount INTEGER
);
INSERT INTO orders VALUES
    (1, 2023, 'Standard', 100),
    (2, 2023, 'Standard', NULL),
    (3, 2023, 'Express', 0),
    (4, 2023, NULL, 50),
    (5, 2023, 'Drone', 70),
    (6, 2024, 'Standard', 200),
    (7, 2024, 'Express', 300),
    (8, 2024, 'Express', NULL);

Rok zapisuję jako gotową liczbę, aby skupić się na przestawianiu danych. W prawdziwej tabeli często wyprowadzisz go z daty. Sposób wyciągania roku zależy od silnika, więc nie podaję YEAR jako uniwersalnego polecenia. Ustal także, czy raport używa daty zamówienia, wysyłki czy doręczenia. Zmiana tego wyboru może przenieść rekord do innego okresu.

Kwoty są umownymi jednostkami jednej waluty i służą tylko ćwiczeniu. Zero oznacza znaną wartość zero; NULL oznacza brak kwoty. Osobno występuje NULL w metodzie dostawy. Brak kategorii i brak miary są dwoma problemami, które mogą dotyczyć różnych wierszy. Zastąpienie wszystkich braków jednym zerem zgubiłoby tę informację.

Oglądam kategorie i format długi

-- example: long
SELECT order_year, shipping_mode, COUNT(*) AS orders_count
FROM orders
GROUP BY order_year, shipping_mode
ORDER BY order_year, shipping_mode;

W 2023 roku widzę cztery grupy: brak metody z jednym zamówieniem, Drone z jednym, Express z jednym i Standard z dwoma. W 2024 są dwie grupy: Express z dwoma oraz Standard z jednym. Razem pięć plus trzy daje osiem rekordów źródłowych. Na tym etapie nie ma jeszcze powodu, żeby którakolwiek metoda zniknęła.

To format długi: nazwy kategorii pozostają wartościami w kolumnie. Jest wygodny do dalszych filtrów, wykresów i zasilania narzędzi BI. Nowa metoda dostawy tworzy kolejny wiersz, bez zmiany listy kolumn. Jeśli odbiorca i tak buduje własną tabelę przestawną, taki wynik może być lepszym materiałem wejściowym niż gotowa szeroka tabela.

Przed ustaleniem kategorii sprawdzam również pisownię. Standard, STANDARD i Standard z końcową spacją mogą pochodzić z różnych zapisów tego samego pojęcia. Nie zakładam automatycznie, że silnik połączy je zgodnie z intencją biznesową. Normalizację opisuję jako oddzielną regułę przygotowania danych, z kontrolą wartości przed i po zmianie.

CASE wybiera wartość, COUNT ją liczy

-- example: case-rows
SELECT order_id,
       CASE WHEN shipping_mode = 'Standard' THEN 1 END AS marker
FROM orders
WHERE order_year = 2023
ORDER BY order_id;

Dla identyfikatorów 1 i 2 marker wynosi jeden. Dla pozostałych trzech wierszy jest NULL. Gdy żaden warunek CASE nie pasuje i pomijam ELSE, wynikiem jest NULL. Regułę opisuje dokumentacja wyrażenia CASE. Na razie nie ma tu żadnego liczenia: każdy wiersz otrzymał tylko wynik wyrażenia.

Dopiero COUNT zlicza markery różne od NULL. Mogę zwrócić jeden, identyfikator albo inny gwarantowanie niepusty element. Wybieram stałą jeden, bo czytelnie pokazuje intencję liczenia pasujących rekordów. Gdybym zwrócił kwotę, pasujące zamówienie z nieznaną kwotą zostałoby pominięte. Warunek i wartość zwracana po THEN wspólnie określają wynik.

Buduję pivot, który zachowuje wszystkie zamówienia

-- example: pivot
SELECT order_year,
       COUNT(CASE WHEN shipping_mode = 'Standard' THEN 1 END) AS standard,
       COUNT(CASE WHEN shipping_mode = 'Express' THEN 1 END) AS express,
       COUNT(CASE WHEN shipping_mode IS NULL THEN 1 END) AS unknown_mode,
       COUNT(CASE WHEN shipping_mode NOT IN ('Standard', 'Express') THEN 1 END) AS other_mode,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_year
ORDER BY order_year;

Kategorie są rozłączne i obejmują wszystkie osiem rekordów. Dla każdego roku suma czterech kolumn zgadza się z total_orders. NULL nie trafia do warunku NOT IN, dlatego ma własny przypadek IS NULL. Drone trafia do innych metod. Bez tych dwóch kolumn pierwszy wiersz pokazywałby tylko trzy z pięciu zamówień.

Grupuję wyłącznie po roku. Gdybym dodał shipping_mode do GROUP BY, każda metoda utworzyłaby osobny wiersz i szeroki wynik straciłby zamierzony układ. Dla każdego wyrażenia warto więc wskazać jego rolę: rok definiuje grupę, CASE wybiera jej podzbiór, COUNT daje liczbę. To wystarcza do zrozumienia całego mechanizmu.

Dlaczego ELSE 0 psuje warunkowy COUNT

-- example: count-trap
SELECT
    COUNT(CASE WHEN shipping_mode = 'Standard' THEN 1 END) AS correct_count,
    COUNT(CASE WHEN shipping_mode = 'Standard' THEN 1 ELSE 0 END) AS wrong_count,
    SUM(CASE WHEN shipping_mode = 'Standard' THEN 1 ELSE 0 END) AS alternative_count
FROM orders
WHERE order_year = 2023;

Otrzymuję dwa, pięć i dwa. COUNT liczy zarówno jedynki, jak i zera, ponieważ obie wartości są różne od NULL. SUM dodaje je arytmetycznie, więc wersja z jedynkami i zerami daje tutaj poprawną liczbę pasujących wierszy. Dokumentacja agregacji wyjaśnia różnicę między liczeniem wartości a ich sumowaniem.

Te dwa poprawne warianty mają jednak inne zachowanie dla całkowicie pustego wejścia. Globalny COUNT zwraca zero, a SUM zwraca NULL. Gdy dodajesz COALESCE, określasz sposób prezentacji takiego wyniku. Nie traktuj zatem mechanicznej zamiany COUNT na SUM jako operacji bez żadnych konsekwencji, szczególnie w zapytaniach generujących gotowy raport.

Liczba zamówień i suma kwot potrzebują innych kontroli

-- example: amounts
SELECT order_year,
       COUNT(CASE WHEN shipping_mode = 'Standard' THEN 1 END) AS standard_orders,
       COUNT(CASE WHEN shipping_mode = 'Standard' THEN amount END) AS known_amounts,
       SUM(CASE WHEN shipping_mode = 'Standard' THEN amount END) AS known_total
FROM orders
GROUP BY order_year
ORDER BY order_year;

Dla 2023 wynik wynosi dwa zamówienia, jedna znana kwota i suma sto. Dla 2024: jedno zamówienie, jedna znana kwota i suma dwieście. Suma sto nie jest dowodem, że oba zamówienia z 2023 miały łącznie wartość sto. To wyłącznie suma dostępnych kwot. Druga kwota nadal pozostaje nieznana, więc pokazuję kompletność obok wartości.

Jeżeli wszystkie pasujące kwoty są nieznane, SUM bez ELSE zwróci NULL. Jeśli dopiszesz ELSE 0, obecność w grupie innych metod może zmienić wynik takiego przypadku na zero. Wtedy wygląd sumy zależy od rekordów spoza analizowanej metody. Właśnie dlatego preferuję osobne liczniki zamówień oraz znanych kwot, zamiast próbować wyjaśnić wszystko jedną liczbą.

Porównywanie sum Standard między latami wymaga jeszcze sprawdzenia pokrycia. Większa suma dostępnych kwot może wynikać z tego, że w jednym roku zapisano je częściej. Nie wyciągam z takiej różnicy od razu wniosku o wzroście popytu. Pokazuję liczbę brakujących wartości i dopiero wtedy ustalam, jakie porównanie jest uczciwe wobec danych.

Rok bez rekordów nie pojawi się sam

-- example: calendar
WITH years(order_year) AS (VALUES (2023), (2024), (2025))
SELECT y.order_year,
       COUNT(o.order_id) AS total_orders,
       COUNT(CASE WHEN o.shipping_mode = 'Standard' THEN 1 END) AS standard,
       COUNT(CASE WHEN o.order_id IS NOT NULL AND o.shipping_mode IS NULL THEN 1 END) AS unknown_mode
FROM years AS y
LEFT JOIN orders AS o ON o.order_year = y.order_year
GROUP BY y.order_year
ORDER BY y.order_year;

Lista lat jest tutaj oczekiwanym zakresem raportu. LEFT JOIN zachowuje 2025, mimo że brak pasującego zamówienia. Wynik dla tego roku to trzy zera. Liczę klucz zamówienia, więc sztuczny wiersz z pustą prawą stroną połączenia nie zostaje potraktowany jak zamówienie. Użycie COUNT(*) dałoby w tym miejscu jeden.

Podobny problem dotyczy brakującej metody. Samo sprawdzenie o.shipping_mode IS NULL uznałoby pustą prawą stronę za zamówienie bez metody. Dodaję więc warunek istnienia niepustego klucza. Zasady LEFT JOIN opisują, skąd bierze się taki wiersz. Przed rozbudową przykładu warto przejść przez rodzaje połączeń.

W tym ćwiczeniu źródło jest kompletne, a w 2025 nie ma zamówień. W rzeczywistym procesie równie pusty wynik może powstać po nieudanym imporcie. Samo zapytanie nie dowodzi, że firma nie odnotowała aktywności. Zanim opiszę zero jako zdarzenie biznesowe, sprawdzam dostępność danych. Brak zamówień i brak otrzymanego pliku wymagają od odbiorcy innych działań.

Udział procentowy wymaga widocznego mianownika

-- example: share
WITH years(order_year) AS (VALUES (2023), (2024), (2025))
SELECT y.order_year,
       100.0 * COUNT(CASE WHEN o.shipping_mode = 'Standard' THEN 1 END)
       / NULLIF(COUNT(o.order_id), 0) AS standard_share_percent
FROM years AS y
LEFT JOIN orders AS o ON o.order_year = y.order_year
GROUP BY y.order_year
ORDER BY y.order_year;

Standard stanowi 40 procent zamówień w 2023 i około 33,33 procent w 2024. Dla 2025 wynik jest NULL, bo udział w zerowej liczbie zamówień nie jest określony. Mnożnik 100.0 zapewnia obliczenie z częścią ułamkową. NULLIF zamienia zerowy mianownik na NULL, jasno pokazując intencję obsługi pustego okresu.

W mianowniku uwzględniam wszystkie zamówienia, także te bez metody oraz z inną metodą. Gdybym ograniczył go do Standard i Express, udział Standard w 2023 wyniósłby około 66,67 procent. Oba rachunki da się wykonać poprawnie, lecz odpowiadają na inne pytania. Opis miary powinien ujawniać populację, do której odnoszę procent.

Stała lista kolumn nie jest dynamicznym pivotem

Dodanie nowego rekordu z metodą Locker nie utworzy automatycznie kolumny locker. Nasze zapytanie ma stałą listę wyrażeń SELECT. Locker zwiększy other_mode, co chroni sumę kontrolną, ale nie zapewni osobnego szczegółu. Jeżeli każda metoda ma mieć własną kolumnę, ktoś musi zmienić definicję wyniku lub wygenerować ją w kontrolowany sposób.

Przy często zmieniających się kategoriach wolę oddać format długi i przestawić dane w warstwie raportowej. Odbiorca może wtedy sam zdecydować, co pokazać. Nie każda analiza wymaga dynamicznego SQL. Stały zestaw kolumn bywa wręcz zaletą przy eksporcie, którego schemat jest umową z kolejnym systemem. Wtedy nowe kategorie powinny powodować widoczny sygnał do przeglądu.

Niektóre silniki oferują inne rozwiązania. PostgreSQL udostępnia funkcję crosstab w rozszerzeniu tablefunc, z własnymi zasadami definiowania wyniku. Nie uruchamiamy jej w tym ćwiczeniu. Wybór funkcji silnika nie usuwa jednak pytań o brakujące kategorie, kompletność kwot i znaczenie pustej komórki. Te decyzje pozostają po stronie autora raportu.

Uważam na filtry i nakładające się kategorie

Załóżmy, że chcesz pokazać wyłącznie Standard, zachowując wszystkie lata z kalendarza. Dopisanie warunku na metodę z prawej tabeli do WHERE po LEFT JOIN może usunąć również lata bez dopasowania. Wiersz z pustą prawą stroną nie spełni porównania z napisem Standard. Dlatego umieszczam ograniczenie we właściwym miejscu: w warunku połączenia albo wewnątrz agregacji, zależnie od tego, które liczniki mają pozostać dostępne.

Nie ma jednej mechanicznej reguły, żeby każdy filtr zawsze przenosić do ON. Filtr na rok raportu dotyczy kalendarza, a filtr na metodę dotyczy zamówień. Najpierw ustalam, czy usuwam okres z raportu, czy tylko ograniczam rekordy liczone w jego komórce. Następnie sprawdzam wynik dla roku pustego oraz roku, który zawiera wyłącznie inne metody dostawy. Te dwa przypadki szybko ujawniają różnicę.

Osobno kontroluję rozłączność kategorii. Kolumny Standard i Express nie mogą policzyć tego samego rekordu w naszym modelu. Natomiast kolumny „kwota dodatnia” i „metoda Standard” mogą się nakładać. Suma takich kolumn nie powinna być porównywana z liczbą wszystkich zamówień. Podobny wygląd tabeli nie oznacza, że każde jej podsumowanie ma tę samą interpretację.

Jeżeli odbiorca potrzebuje właśnie nakładających się flag, opisuję je jako niezależne liczniki. Jeden rekord może świadomie wystąpić w kilku kolumnach. Nie poprawiam wtedy zapytania tylko po to, żeby suma wyglądała elegancko. Zmieniam opis kontroli: każdą flagę sprawdzam na jej własnym warunku, a wspólnej sumy nie przedstawiam jako liczby unikalnych zamówień.

Porównuję okresy i wybieram użyteczny rozmiar raportu

Rok z trzema zamówieniami i rok z pięcioma to wygodne ćwiczenie składni, bez podstaw do wniosków o rzeczywistym rynku. W raporcie produkcyjnym najpierw pytam o pełność okresów. Trzy miesiące bieżącego roku nie są równoważne dwunastu miesiącom poprzedniego. Nawet poprawnie policzone udziały mogą zależeć od sezonu oraz zmian w dostępnych metodach dostawy.

Sprawdzam także, czy sposób klasyfikacji pozostawał stały. Jeśli nową metodę uruchomiono w połowie roku, wcześniejsze zera mają konkretne wyjaśnienie. Jeżeli zmieniła się jedynie nazwa, rozdzielenie jej na dwie kategorie może stworzyć pozorny spadek jednej usługi i wzrost drugiej. Mapowanie nazw powinno mieć opisaną regułę, szczególnie gdy raport odtwarza historię po kolejnej aktualizacji.

Szeroka tabela dobrze działa przy kilku stabilnych kolumnach. Kilkadziesiąt metod na małym ekranie wymusza przewijanie i utrudnia porównanie. Wtedy rozważam format długi, filtr kategorii albo osobny widok szczegółowy. Liczba kolumn powinna wynikać z zadania odbiorcy. Możliwość dopisania następnego CASE nie jest jeszcze powodem, żeby go dopisywać.

Przy dużej tabeli sprawdzam plan wykonania oraz czas odpowiedzi w używanym silniku, na reprezentatywnych danych. Nasz mały przykład potwierdza logikę, ale nie mierzy wydajności. Ograniczenie zakresu dat może pomóc, o ile odpowiada definicji raportu. Nie obiecuję, że określony zapis agregacji zawsze będzie szybszy od innego bez pomiaru w danym środowisku.

Mój sprawdzian przed oddaniem tabeli

Najpierw porównuję liczbę źródłowych zamówień z sumą wszystkich kategorii dla każdego roku. Potem kontroluję osobno braki i inne metody. Rosnąca kolumna innych metod może oznaczać nową usługę albo zmianę zapisu w źródle. Nie chowam jej tylko dlatego, że odbiorca zamówił dwie główne kolumny. Pokazuję ograniczenie i ustalam dalszą klasyfikację.

Następnie dokładam do kopii danych jedno zamówienie z nową metodą, jedno z NULL i jeden rok bez rekordów. Sprawdzam także grupę, w której wszystkie kwoty są nieznane, oraz grupę z kwotą zero. Oczekiwany wynik zapisuję przed wykonaniem zapytania. Dzięki temu nie uznaję automatycznie każdej otrzymanej liczby za poprawną tylko dlatego, że nie pojawił się błąd.

Twoje ćwiczenie: dodaj w 2024 zamówienie numer dziewięć z metodą Locker i kwotą 40. Łączna liczba zamówień tego roku powinna wzrosnąć do czterech, inne metody do jednego, a udział Standard spaść do 25 procent. Liczba i suma zamówień Standard pozostają bez zmian. Wyjaśnij każdą z tych różnic własnymi słowami.

Na koniec zapisz definicję komórki, zakres okresów oraz sposób traktowania braków obok zapytania. Czytelnik powinien wiedzieć, czy patrzy na liczbę zamówień, sumę znanych kwot czy udział w określonej populacji. Tabela przestawna ułatwia porównanie danych dopiero wtedy, gdy te znaczenia są jasne. Sam prostokąt z liczbami tego za nas nie załatwi.

Film: tabela przestawna w SQL

Do artykułu jest przypisany poniższy polski materiał. Przykład tekstowy ma własny mały zbiór danych, dzięki któremu możesz odtworzyć wszystkie liczby i przypadki brzegowe bez zewnętrznego pliku.