0Pricing
SQL Interview Prep · Lekcja

Przekształcanie kolumn w wiersze

Odwracanie szerokich tabel za pomocą UNPIVOT lub UNION ALL.

Przekształcanie kolumn w wiersze to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 3 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.

Problem odwrotny

Unpivotowanie jest lustrzanym odbiciem tworzenia tabeli przestawnej: szeroką tabelę należy przekształcić z powrotem z kolumn w wiersze. Rekruterzy pytają o to, gdy dane przychodzą w arkuszowym układzie, ale do analizy trzeba je znormalizować.

Przykład: tabela zawierająca kolumny q1, q2, q3, q4 dla każdego regionu musi zostać przekształcona w wiersze postaci (region, quarter, amount). Taka postać długa jest preferowana przez agregowanie, łączenie i tworzenie wykresów.

-- Wide input we want to unpivot
region | q1  | q2  | q3  | q4
-------+-----+-----+-----+----
East   | 100 | 150 | 120 | 180
West   | 200 | 250 | 210 | 260

Przenośny wzorzec UNION ALL

Odpowiedzią niezależną od dialektu jest UNION ALL: należy napisać jedną klauzulę SELECT dla każdej kolumny źródłowej, a każda z nich powinna zwracać etykietę literalną i wartość danej kolumny.

Należy użyć UNION ALL, a nie UNION, aby uniknąć kosztu usuwania duplikatów i zachować każdy wiersz, nawet gdy dwie komórki mają tę samą wartość.

SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL
SELECT region, 'Q2', q2 FROM wide_sales
UNION ALL
SELECT region, 'Q3', q3 FROM wide_sales
UNION ALL
SELECT region, 'Q4', q4 FROM wide_sales;

Dlaczego UNION ALL, a nie UNION

To klasyczna pułapka podczas rozmowy kwalifikacyjnej. UNION usuwa zduplikowane wiersze z całego wyniku. Jeśli regiony East i West miały po 100 w pierwszym kwartale, zwykłe UNION połączy identyczne wiersze i utracą Państwo dane.

UNION ALL konkatenauje wyniki bez usuwania duplikatów, czego wymaga unpivotowanie. Jest również szybsze, ponieważ nie wymaga sortowania ani funkcji hash do usuwania duplikatów.

-- UNION would wrongly merge identical (region, quarter, amount) rows
-- UNION ALL keeps every row, always the correct choice here

Zgodność typów kolumn

Każda gałąź wyrażenia UNION ALL musi zwracać taką samą liczbę kolumn o zgodnych typach i w tej samej kolejności. Nazwy kolumn pochodzą z pierwszej klauzuli SELECT.

Jeśli kolumny szerokiej tabeli różnią się typem (na przykład jedna ma typ int, a inna decimal), silnik wybiera wspólny typ. Jeśli typy są rzeczywiście niezgodne, należy jawnie wykonać rzutowanie, aby unia nie zakończyła się błędem.

SELECT region, 'revenue' AS metric, CAST(revenue AS decimal(12,2)) AS val FROM t
UNION ALL
SELECT region, 'units',   CAST(units   AS decimal(12,2))        FROM t;

UNPIVOT w SQL Server

SQL Server ma dedykowany operator UNPIVOT, który jest bardziej zwięzły niż UNION ALL. Należy podać nazwę nowej kolumny wartości, nazwę nowej kolumny etykiety oraz wymienić kolumny źródłowe, które mają zostać scalone.

Jedno ważne zachowanie: operator UNPIVOT usuwa wiersze, w których wartość wynosi NULL. Rekruterzy sprawdzają, czy znają Państwo ten efekt uboczny.

SELECT region, quarter, amount
FROM wide_sales
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
) AS u;

UNPIVOT usuwa wartości NULL

Jeśli region ma wartość NULL w kolumnie q3, operator UNPIVOT w SQL Server po prostu pomija ten wiersz w wyniku. Jeśli potrzebują Państwo wiersza dla każdej kolumny, niezależnie od wartości NULL, należy wrócić do UNION ALL, które je zachowuje.

Podczas rozmowy kwalifikacyjnej warto wyraźnie wskazać ten kompromis: natywny operator UNPIVOT jest zwięzły, ale powoduje utratę wierszy z wartościami NULL; UNION ALL jest rozwlekły, lecz kompletny.

-- UNPIVOT: q3 NULL for East -> no (East, Q3) row produced
-- UNION ALL: (East, 'Q3', NULL) row IS produced

PostgreSQL: LATERAL VALUES

PostgreSQL nie ma operatora UNPIVOT, ale poręcznym idiomem jest CROSS JOIN LATERAL z listą VALUES. Każdy szeroki wiersz jest rozwijany względem małej tabeli wbudowanej zawierającej pary (etykieta, wartość).

Jest to rozwiązanie czytelniejsze niż długi ciąg UNION ALL i odczytuje tabelę źródłową tylko raz.

SELECT w.region, v.quarter, v.amount
FROM wide_sales w
CROSS JOIN LATERAL (VALUES
  ('Q1', w.q1),
  ('Q2', w.q2),
  ('Q3', w.q3),
  ('Q4', w.q4)
) AS v(quarter, amount);

Jednokrotny odczyt tabeli

Warto wspomnieć o kwestii wydajności: naiwne użycie UNION ALL skanuje szeroką tabelę raz dla każdej gałęzi (cztery skanowania dla czterech kwartałów). Forma LATERAL VALUES oraz operator UNPIVOT w SQL Server odczytują źródło raz.

W przypadku dużych tabel ma to znaczenie. Jeśli trzeba użyć UNION ALL, optymalizator i tak może wielokrotnie skanować tabelę, dlatego warto wspomnieć o LATERAL lub UNPIVOT jako wydajniejszych opcjach.

Filtrowanie pustych komórek

W przypadku UNION ALL lub LATERAL wiersze z wartościami NULL są zachowywane. Jeśli wymagane są tylko wypełnione komórki, należy dodać filtr. Naśladuje to działanie operatora UNPIVOT w SQL Server.

Decyzja o zachowaniu lub odrzuceniu wartości NULL zależy od wymagań, dlatego przed napisaniem kodu należy doprecyzować je z rekruterem.

SELECT region, quarter, amount
FROM (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
) t
WHERE amount IS NOT NULL;

Przykład: agregowanie po unpivotowaniu

Częste pytanie uzupełniające brzmi: „z szerokiej tabeli kwartalnej podaj łączny przychód dla każdego regionu ze wszystkich kwartałów”. Po przekształceniu do długiej postaci agregowanie jest proste: pojedyncza funkcja SUM z grupowaniem według regionu.

To pokazuje prawdziwy powód, dla którego najpierw należy wykonać unpivotowanie. Sumowanie czterech osobnych kolumn jest podatne na błędy, natomiast SUM(amount) GROUP BY region w długiej postaci skaluje się do dowolnej liczby kwartałów.

WITH long_sales AS (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
  UNION ALL SELECT region, 'Q3', q3 FROM wide_sales
  UNION ALL SELECT region, 'Q4', q4 FROM wide_sales
)
SELECT region, SUM(amount) AS total
FROM long_sales
GROUP BY region;

Kiedy stosować unpivotowanie

Rozpoznaj sygnały wskazujące na unpivotowanie w treści zadania:

  • Dane wejściowe zawierają powtarzające się kolumny, które w rzeczywistości są wartościami (miesiącami, latami lub metrykami).
  • Należy agregować, łączyć lub wizualizować te wartości na wykresie.
  • Należy znormalizować zdenormalizowane dane arkuszowe podczas importu.

Długa postać niemal zawsze jest właściwym kształtem danych do dalszej pracy z SQL, dlatego unpivotowanie często stanowi pierwszy krok.

Szybkie sprawdzenie

Upewnij się, że znasz najczęstszą pułapkę związaną z unpivotowaniem.

Podsumowanie

Unpivotowanie zamienia kolumny na wiersze:

  • Przenośne: jedno SELECT dla każdej kolumny, połączone za pomocą UNION ALL (nigdy zwykłego UNION).
  • SQL Server: natywne UNPIVOT, zwięzłe, ale pomija wartości NULL.
  • Postgres: CROSS JOIN LATERAL (VALUES ...), czyli pojedyncze skanowanie.
  • Należy wyrównać liczbę i typy kolumn we wszystkich gałęziach oraz odfiltrować wartości NULL, jeśli wymaga tego treść pytania.

Często zadawane pytania

Czy lekcja „Przekształcanie kolumn w wiersze” jest bezpłatna?

Tak — pełny tekst „Przekształcanie kolumn w wiersze” 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 „Przekształcanie kolumn w wiersze”?

Odwracanie szerokich tabel za pomocą UNPIVOT lub UNION ALL. Ć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 3 z 4.

Ile czasu zajmuje lekcja „Przekształcanie kolumn w wiersze”?

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

  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 SQL Interview Prep