SQL Academy · Lekcja

Wzorce crosstab (PostgreSQL crosstab())

Generuj prawdziwe tabele przestawne za pomocą funkcji crosstab() z rozszerzenia tablefunc.

Wzorce crosstab (PostgreSQL crosstab()) to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 4 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 Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Dlaczego właściwy crosstab?

Tabele przestawne z CASE wymagają wyliczenia każdej docelowej kolumny. W przypadku rzeczywiście szerokich tabel przestawnych, na przykład z jedną kolumną na produkt, właściwym narzędziem jest funkcja crosstab() z rozszerzenia tablefunc.

Włączanie rozszerzenia

tablefunc jest dostarczane razem z pakietem contrib PostgreSQL:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Podstawowa sygnatura crosstab

crosstab przyjmuje ciąg SQL z trzema kolumnami (row_key, category, value) i zwraca row_key oraz po jednej kolumnie na kategorię:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders
    GROUP BY user_id, status
    ORDER BY user_id, status
  $$
) AS ct (
  user_id BIGINT,
  paid    INT,
  pending INT,
  cancelled INT
);

Dlaczego należy zadeklarować kolumny wynikowe

SQL jest typowany statycznie — planista musi znać kolumny wynikowe w momencie parsowania. Dlatego należy określić schemat w klauzuli AS, wraz z typami danych.

crosstab z dwoma argumentami (z zestawem kategorii)

W przypadku rzadkich danych należy dostarczyć listę kategorii osobno, aby brakujące wartości stały się NULL zamiast powodować nieprawidłowe dopasowanie kolumn:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders GROUP BY user_id, status
    ORDER BY user_id
  $$,
  $$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
  user_id BIGINT, paid INT, pending INT, cancelled INT
);

Kiedy CASE jest lepszy od crosstab

W przypadku znanego, niewielkiego zestawu kategorii rozwiązanie CASE/FILTER jest prostsze — bez rozszerzenia i bez pułapek wariantu z dwoma argumentami. crosstab należy użyć, gdy:

kategorii jest wiele,
kategorie są ładowane dynamicznie,
generują Państwo dane dla zewnętrznego odbiorcy tabel przestawnych.

Dynamiczne tabele przestawne

W przypadku kategorii nieznanych w czasie działania należy wygenerować SQL w aplikacji albo użyć PL/pgSQL wraz z format() + EXECUTE.

-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
                          status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;

Tworzenie szerokiego formatu dla arkuszy kalkulacyjnych

Raporty dla analityków często wymagają szerokiego formatu. Należy wygenerować go w SQL albo przekazać dane w formacie długim i pozwolić narzędziu BI utworzyć tabelę przestawną.

Unpivot: operacja odwrotna

Aby przejść z formatu szerokiego → długiego, należy użyć UNION ALL albo funkcji jsonb_each_text() PostgreSQL:

SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
     jsonb_each_text(to_jsonb(monthly_wide) - 'id');

Wydajność

crosstab() wykonuje wewnętrzny SQL raz, a następnie tworzy tabelę przestawną w pamięci. Wąskim gardłem jest to samo, co w przypadku zwykłego zapytania GROUP BY.

Ograniczenia crosstab

PostgreSQL nie ma natywnego słowa kluczowego PIVOT, w przeciwieństwie do Oracle i SQL Server. crosstab() jest rozwiązaniem zastępczym.

Podsumowanie

W przypadku większości tabel przestawnych właściwym rozwiązaniem jest CASE/FILTER. crosstab() jest narzędziem przeznaczonym do sytuacji, gdy kategorii jest wiele albo nie są znane z wyprzedzeniem.

Szybkie sprawdzenie

Które rozszerzenie udostępnia funkcję PostgreSQL crosstab()?

Często zadawane pytania

Czy lekcja „Wzorce crosstab (PostgreSQL crosstab())” jest bezpłatna?

Tak — pełny tekst „Wzorce crosstab (PostgreSQL crosstab())” 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 Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Wzorce crosstab (PostgreSQL crosstab())”?

Generuj prawdziwe tabele przestawne za pomocą funkcji crosstab() z rozszerzenia tablefunc. Ćwiczysz SQL Academy 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 Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy 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 4 z 4.

Ile czasu zajmuje lekcja „Wzorce crosstab (PostgreSQL crosstab())”?

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 Academy?

Tak. Każda lekcja SQL Academy 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

UNION, INTERSECT, EXCEPT
UNION ALL a UNION (koszt usuwania duplikatów)
Wyrażenia CASE i zapytania przestawne
Wzorce crosstab (PostgreSQL crosstab())

← Powrót do SQL Academy