0Pricing
Coding Interview Prep · Lekcja

Składnia PIVOT i crosstab dostawców

SQL Server PIVOT i Postgres crosstab oraz ich ograniczenia.

Składnia PIVOT i crosstab dostawców to bezpłatna lekcja Coding 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 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.

Poza agregacją warunkową

Znają już Państwo przenośną tabelę przestawną opartą na CASE. Rekruterzy chcą jednak również wiedzieć, czy potrafią Państwo używać operatorów tabel przestawnych specyficznych dla danego dostawcy, gdy są dostępne.

SQL Server udostępnia dedykowany operator PIVOT. PostgreSQL oferuje funkcję crosstab w rozszerzeniu tablefunc. Znajomość obu rozwiązań i ich pułapek świadczy o praktycznym doświadczeniu.

Budowa operatora PIVOT w SQL Server

Operator PIVOT w SQL Server przyjmuje trzy elementy:

  • Funkcję agregującą zastosowaną do kolumny wartości.
  • Klauzulę FOR wskazującą kolumnę, której wartości staną się nowymi kolumnami.
  • Listę IN zawierającą wartości literalne, które mają zostać przekształcone w kolumny.

Operator musi zostać zastosowany do tabeli pochodnej udostępniającej dokładnie klucz, kolumnę rozpraszającą i wartość — nic więcej.

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

Niejawne GROUP BY

Subtelna pułapka operatora PIVOT, którą sprawdzają rekruterzy: grupowanie jest niejawne. SQL Server grupuje według każdej kolumny źródłowej, która NIE jest kolumną agregowaną ani kolumną wskazaną w klauzuli FOR.

Jeśli więc tabela pochodna przypadkowo zawiera dodatkową kolumnę, taką jak order_id, tabela przestawna również grupuje według niej, co daje znacznie więcej wierszy, niż oczekiwano. Zapytanie wewnętrzne należy zawsze ograniczyć do klucza, kolumny rozpraszającej i wartości.

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

Nazwy kolumn w nawiasach kwadratowych

W SQL Server nazwy kolumn utworzonych przez tabelę przestawną są literalnymi wartościami z danych ujętymi w nawiasy kwadratowe. Jeśli wartość zaczyna się od cyfry lub zawiera spacje, nawiasy są obowiązkowe.

W zewnętrznej klauzuli SELECT należy odwoływać się do nich za pomocą tej samej nazwy w nawiasach kwadratowych. Z tego samego powodu operator PIVOT nie może obsługiwać nieznanych wartości bez dynamicznego SQL: lista IN jest zapisana na stałe.

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

crosstab w PostgreSQL

PostgreSQL nie ma słowa kluczowego PIVOT. Zamiast niego rozszerzenie tablefunc udostępnia funkcję crosstab, która przyjmuje ciąg SQL i przekształca jego wynik.

Najpierw należy włączyć rozszerzenie. Funkcja crosstab oczekuje, że zapytanie źródłowe zwróci dokładnie trzy kolumny, w następującej kolejności: identyfikator wiersza, kategoria i wartość.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

Lista definicji kolumn

Najbardziej podatnym na błędy elementem funkcji crosstab jest końcowa lista definicji kolumn AS ct(...). Nazwy i typy kolumn wyjściowych trzeba zadeklarować samodzielnie, a ich liczba i kolejność muszą odpowiadać kategoriom.

Jeśli dla danego wiersza brakuje kategorii, funkcja crosstab wstawia wartości pozycyjnie, co może prowadzić do nieprawidłowego przypisania danych, chyba że użyją Państwo przedstawionej poniżej formy dwuargumentowej.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

Dwuargumentowa forma crosstab

Aby uniknąć nieprawidłowego przypisania danych, gdy w niektórych wierszach brakuje określonych kategorii, należy użyć formy dwuargumentowej. Drugie zapytanie zwraca pełną, uporządkowaną listę wartości kategorii, dzięki czemu funkcja crosstab dokładnie wie, do której kolumny należy przypisać każdą wartość.

Jest to solidna forma, której rekruterzy oczekują w przypadku rzadko występujących kategorii.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

MySQL nie obsługuje żadnego z tych rozwiązań

Jeśli rekruter zapyta o MySQL, odpowiedź jest prosta: MySQL nie ma operatora PIVOT ani funkcji crosstab. Jedyną możliwością jest tam agregacja warunkowa z użyciem CASE (lub skrócona składnia SUM(... ) + IF()).

Właśnie dlatego przenośny wzorzec oparty na CASE jest tak ceniony: to rozwiązanie wspólne dla wszystkich silników, działające wszędzie.

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

Przykład z rozwiązaniem: zliczanie statusów w SQL Server

Przykładowe wymaganie raportowe: „jeden wiersz na region, z kolumną zliczającą zamówienia dla każdego statusu”. W SQL Server należy przekazać do operatora PIVOT odpowiednio ograniczoną tabelę pochodną i użyć funkcji COUNT.

Ponieważ zliczana jest sama kolumna statusu, każdy wiersz z wartością różną od NULL w danym przedziale zostanie uwzględniony. Zewnętrzna klauzula SELECT wymienia każdy status jako kolumnę w nawiasach kwadratowych. Jest to zwięzła alternatywa dla zapisywania trzech wyrażeń COUNT(CASE ...).

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

Wspólne ograniczenia

Operatorzy PIVOT i crosstab mają to samo podstawowe ograniczenie co agregacja warunkowa: kolumny wyjściowe muszą być znane w momencie pisania zapytania.

  • SQL Server: lista IN zawiera wartości literalne.
  • Postgres crosstab: lista definicji kolumn zawiera wartości literalne.

Żaden z tych operatorów nie może wykrywać kategorii w czasie wykonywania. Wymaga to dynamicznego budowania ciągu SQL.

Którego rozwiązania należy użyć?

Dobra odpowiedź podczas rozmowy kwalifikacyjnej powinna uczciwie je porównać:

  • Agregacja z CASE: przenośna, czytelna i działająca w każdym silniku. Domyślny wybór.
  • SQL Server PIVOT: zwięzły przy wielu kolumnach, ale jego niejawne grupowanie może zaskoczyć.
  • Postgres crosstab: rozbudowany, lecz rozwlekły; wymaga rozszerzenia i listy definicji kolumn.

W razie wątpliwości należy wybrać agregację warunkową i wspomnieć o operatorach dostawcy jako alternatywach.

Szybkie sprawdzenie

Sprawdźmy najważniejsze zachowanie operatora SQL Server PIVOT, o które pytają rekruterzy.

Podsumowanie

Składnia tabel przestawnych dostawców w jednym miejscu:

  • SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])) z niejawnym GROUP BY obejmującym pozostałe kolumny.
  • Postgres: crosstab() z rozszerzenia tablefunc, wymagająca listy definicji kolumn; w przypadku rzadkich danych należy użyć formy dwuargumentowej.
  • MySQL: nie obsługuje żadnego z tych rozwiązań, należy użyć CASE.
  • We wszystkich trzech przypadkach kolumny muszą być znane w momencie pisania zapytania.

Często zadawane pytania

Czy lekcja „Składnia PIVOT i crosstab dostawców” jest bezpłatna?

Tak — pełny tekst „Składnia PIVOT i crosstab dostawców” 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 „Składnia PIVOT i crosstab dostawców”?

SQL Server PIVOT i Postgres crosstab oraz ich ograniczenia. Ć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 2 z 4.

Ile czasu zajmuje lekcja „Składnia PIVOT i crosstab dostawców”?

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. Tabela przestawna z agregacją warunkową
  2. Składnia PIVOT i crosstab dostawców
  3. Przekształcanie kolumn w wiersze
  4. Dynamiczne tabele przestawne z nieznanymi kolumnami
← Powrót do Coding Interview Prep