SQL Interview Prep · Lekcja

Dynamiczne tabele przestawne z nieznanymi kolumnami

Generowanie kolumn tabeli przestawnej, gdy kategorie nie są znane z wyprzedzeniem.

Dynamiczne tabele przestawne z nieznanymi kolumnami to bezpłatna lekcja SQL Interview Prep 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 Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Trudne pytanie o pivotowanie

Każdy statyczny pivot, niezależnie od tego, czy jest realizowany za pomocą agregacji CASE, operatora SQL Server PIVOT czy funkcji Postgres crosstab, ma to samo ograniczenie: podczas pisania zapytania trzeba wymienić kolumny wynikowe.

Co jednak zrobić, gdy kategorie są nieznane, na przykład gdy nazwy produktów zmieniają się co tydzień albo potrzebna jest osobna kolumna dla każdego aktywnego miesiąca? To dynamiczne pivotowanie — pytanie na poziomie seniora, ponieważ zwykły SQL nie może zwrócić wyniku, którego lista kolumn jest ustalana w czasie wykonywania.

Dlaczego sam SQL nie może tego zrobić

SQL jest statycznie typowany na poziomie zestawu wynikowego: planer musi znać kolumny i ich typy przed wykonaniem zapytania. Pojedyncze zapytanie nie może powiedzieć: utwórz jedną kolumnę dla każdej znalezionej wartości.

Uniwersalna technika polega więc na wygenerowaniu tekstu SQL w dwóch krokach: najpierw należy pobrać różne kategorie, a następnie zbudować na ich podstawie ciąg zapytania pivotującego i go wykonać.

Krok 1: Zebranie kategorii

Pierwszy krok to zwykłe zapytanie wyświetlające różne wartości, które staną się kolumnami. Zwykle porządkuje się je, aby uzyskać stabilny układ kolumn.

Ten wynik jest przekazywany do etapu budowania ciągu. W rzeczywistym systemie wykonuje się to zapytanie, zapisuje zwrócone wiersze i na ich podstawie składa kolejne zapytanie.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

Krok 2: Zbudowanie listy kolumn

Następnie należy przekształcić te wartości w oddzieloną przecinkami listę wyrażeń CASE (lub nazw ujętych w nawiasy kwadratowe w przypadku PIVOT). Bazy danych udostępniają funkcje agregujące ciągi znaków, które pozwalają zrobić to bezpośrednio w SQL.

W Postgres jest to string_agg, w MySQL GROUP_CONCAT, a w SQL Server STRING_AGG lub starszy sposób z użyciem FOR XML PATH.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

Krok 3: Złożenie i wykonanie

Należy połączyć wygenerowany fragment z pełnym ciągiem zapytania, a następnie uruchomić je za pomocą dynamicznego wykonania: EXECUTE w PL/pgSQL, sp_executesql w SQL Server albo PREPARE/EXECUTE w MySQL.

To sedno dynamicznego pivotowania: SQL generuje SQL, a następnie go uruchamia.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

Pełny przykład w PostgreSQL

W Postgres trzy kroki można umieścić w bloku DO lub w funkcji. Należy zbudować listę kolumn za pomocą string_agg, wstawić ją do zapytania i uruchomić zapytanie za pomocą EXECUTE.

Ponieważ kolumny wyniku są nieznane aż do czasu wykonania, funkcja zwracająca taki wynik często używa RETURNS SETOF record albo zwraca wiersze jako json, które następnie rozwija wywołujący.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

MySQL z instrukcjami przygotowanymi

MySQL nie ma operatora pivotowania, dlatego dynamiczne pivotowanie polega na zbudowaniu ciągu z agregacją warunkową za pomocą GROUP_CONCAT, a następnie uruchomieniu go przez instrukcję przygotowaną.

GROUP_CONCAT ma limit długości (group_concat_max_len), o którym mogą wspomnieć osoby prowadzące rozmowę. Jeśli kategorii jest wiele, należy ten limit zwiększyć.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

Ryzyko SQL injection

Ponieważ wartości danych są łączone w wykonywalny kod SQL, dynamiczne pivotowanie wiąże się z ryzykiem wstrzyknięcia kodu. Jeśli wartość kategorii zawiera cudzysłów lub złośliwy tekst, może uszkodzić wygenerowane zapytanie albo przejąć nad nim kontrolę.

Zawsze należy bezpiecznie cytować identyfikatory i literały za pomocą funkcji pomocniczych danego silnika: format('%I', ...) i %L w Postgres oraz QUOTENAME w SQL Server. Nigdy nie należy wstawiać do ciągu surowych wartości.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

Zwracanie nieznanych kolumn

Drugą trudnością jest to, że wywołujący nie może z góry znać struktury wyniku. Typowe strategie akceptowane podczas rozmowy kwalifikacyjnej to:

Zwrócenie wierszy jako JSON i pozostawienie warstwie aplikacji rozwinięcia kluczy.
Zlecenie procedurze wypisania lub zbudowania zapytania, a następnie uruchomienie go w drugim kroku.
Wykonanie końcowego pivotowania w kodzie aplikacji (pandas, narzędzie BI), gdy kategorie są już znane.

Nie ma prostego sposobu na zwrócenie dowolnych kolumn w ramach jednego statycznego wywołania.

Przykład: pivotowanie według produktu

Załóżmy, że produkty pojawiają się i znikają, a raport wymaga osobnej kolumny przychodu dla każdego produktu, który obecnie występuje w sales. Nie można zakodować listy na stałe, więc należy ją wygenerować. Postgres pozwala zrobić to czytelnie: zbudować fragment CASE za pomocą string_agg i bezpiecznego cytowania, wstawić go do zapytania, a następnie wykonać je za pomocą EXECUTE.

Podczas rozmowy należy przedstawić kolejne kroki: wyszukać produkty, sformatować każdy z nich jako cytowaną kolumnę, złożyć zapytanie i je uruchomić. Ten sam schemat działa w każdym silniku; zmieniają się tylko funkcje pomocnicze.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

Kiedy unikać dynamicznego pivotowania

Dobrzy kandydaci wiedzą, kiedy nie robić tego w SQL. Dynamiczny SQL jest trudniejszy do odczytania, testowania, zabezpieczenia i buforowania. Często lepszą odpowiedzią jest:

Zwrócenie z SQL danych w długiej postaci i wykonanie pivotowania w aplikacji lub warstwie raportowej.
Jeśli zbiór kategorii jest niewielki i rzadko się zmienia, użycie statycznego pivotu oraz jego okazjonalna aktualizacja.

Dynamiczne pivotowanie należy stosować tylko w przypadku rzeczywiście otwartych i stale zmieniających się zbiorów kategorii.

Szybkie sprawdzenie

Sprawdź, czy znasz główny powód istnienia dynamicznego pivotowania.

Podsumowanie

Dynamiczne pivotowanie obsługuje nieznane zestawy kolumn:

Statyczne pivoty zawodzą, ponieważ kolumny wyniku muszą być ustalone przed wykonaniem zapytania.
Schemat jest następujący: pobrać różne kategorie, zbudować ciąg SQL pivotujący i wykonać go dynamicznie.
Do zbudowania listy kolumn należy użyć string_agg/GROUP_CONCAT/STRING_AGG.
Należy bezpiecznie cytować wartości (%I/%L, QUOTENAME), aby uniknąć SQL injection.
Często czytelniejszym rozwiązaniem jest zwrócenie danych w długiej postaci i wykonanie pivotowania w warstwie aplikacji.

Często zadawane pytania

Czy lekcja „Dynamiczne tabele przestawne z nieznanymi kolumnami” jest bezpłatna?

Tak — pełny tekst „Dynamiczne tabele przestawne z nieznanymi kolumnami” 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 „Dynamiczne tabele przestawne z nieznanymi kolumnami”?

Generowanie kolumn tabeli przestawnej, gdy kategorie nie są znane z wyprzedzeniem. Ć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 4 z 4.

Ile czasu zajmuje lekcja „Dynamiczne tabele przestawne z nieznanymi kolumnami”?

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