SQL Academy · Lekcja

UNNEST i agregowanie

Zamieniaj tablice na wiersze i odwrotnie

UNNEST i agregowanie to bezpłatna lekcja SQL Academy 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 Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Czym jest UNNEST

PostgreSQL pozwala przechowywać tablice w pojedynczej kolumnie. Czasami jednak trzeba pracować z każdym elementem osobno — właśnie do tego służy UNNEST.

UNNEST to funkcja zwracająca zbiór, która rozwija tablicę do zbioru wierszy — po jednym wierszu na element. Można ją traktować jako przeciwieństwo agregacji: zamiast scalać wiele wierszy w jeden, rozbija jedną wartość na wiele wierszy.

Podstawowy przykład UNNEST

Najprostsze użycie UNNEST polega na bezpośrednim przekazaniu literału tablicowego. Każdy element staje się osobnym wierszem w zbiorze wyników.

W tym przykładzie zwykła tablica liczb całkowitych zostaje rozwinięta do pojedynczych wierszy:

SELECT UNNEST(ARRAY[10, 20, 30, 40]) AS value;

UNNEST z tablicami tekstowymi

UNNEST działa z dowolnym typem tablicowym, w tym z tekstem. Jest to przydatne, gdy kolumna przechowuje tagi, kategorie lub dane zapisane w tablicy PostgreSQL w stylu danych rozdzielanych przecinkami.

SELECT UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit;

UNNEST z kolumny tabeli

Pełna użyteczność UNNEST ujawnia się podczas stosowania go do rzeczywistej kolumny tabeli. Każdy wiersz tabeli może zawierać tablicę o innej długości, a UNNEST rozwinie wszystkie te tablice do pojedynczych wierszy.

W tym przykładzie tabela products ma kolumnę tablicową tekstową tags:

CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[]
);

INSERT INTO products (name, tags) VALUES
  ('Laptop',  ARRAY['electronics', 'computing', 'portable']),
  ('Shirt',   ARRAY['clothing', 'casual']),
  ('Blender', ARRAY['kitchen', 'electronics']);

SELECT name, UNNEST(tags) AS tag
FROM products;

Zliczanie wystąpień tagów

Po rozwinięciu kolumny tablicowej można traktować wyniki jak dowolne inne wiersze i stosować funkcje agregujące. W tym przykładzie zliczamy, ile produktów jest powiązanych z każdym tagiem.

Wzorzec wygląda następująco: UNNEST w podzapytaniu lub CTE, a następnie GROUP BY według wartości uzyskanej po rozwinięciu:

SELECT tag, COUNT(*) AS product_count
FROM (
  SELECT UNNEST(tags) AS tag
  FROM products
) AS expanded
GROUP BY tag
ORDER BY product_count DESC;

UNNEST z numeracją porządkową

Czasami znaczenie ma pozycja elementu w tablicy. PostgreSQL udostępnia WITH ORDINALITY, które dodaje numer wiersza do każdego rozwiniętego elementu, dzięki czemu można poznać jego pierwotny indeks w tablicy (zaczynający się od 1).

SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);

Używanie numeracji porządkowej w tabeli

WITH ORDINALITY jest szczególnie przydatne podczas przechowywania uporządkowanych list w tablicy. Przykładem może być tabela playlisty, w której kolejność utworów ma znaczenie:

CREATE TABLE playlists (
  id     SERIAL PRIMARY KEY,
  title  TEXT,
  tracks TEXT[]
);

INSERT INTO playlists (title, tracks) VALUES
  ('Morning Mix', ARRAY['Song A', 'Song B', 'Song C']);

SELECT p.title, track, position
FROM playlists p,
     UNNEST(p.tracks) WITH ORDINALITY AS t(track, position)
ORDER BY p.id, position;

Filtrowanie po UNNEST

Ponieważ UNNEST zamienia elementy tablicy na wiersze, można je filtrować za pomocą zwykłej klauzuli WHERE. Pozwala to znaleźć wszystkie wiersze, których tablica zawiera określoną wartość, bez używania operatora zawierania tablicy.

SELECT DISTINCT name
FROM products,
     UNNEST(tags) AS tag
WHERE tag = 'electronics';

Ponowne agregowanie do tablicy

Odwrotnością UNNEST jest ARRAY_AGG. Po rozwinięciu wierszy oraz ich przekształceniu lub odfiltrowaniu można ponownie zebrać wyniki w tablicę. Ten cykl — rozwinięcie, przetworzenie i ponowna agregacja — jest często stosowanym idiomem PostgreSQL.

SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags
FROM (
  SELECT UNNEST(ARRAY['cherry', 'apple', 'banana']) AS tag
) AS t;

Usuwanie zduplikowanych elementów tablicy

Praktycznym zastosowaniem cyklu rozpakowania i agregacji jest usuwanie zduplikowanych elementów z tablicy. Należy rozpakować tablicę za pomocą UNNEST, zastosować DISTINCT, a następnie ponownie zebrać elementy za pomocą ARRAY_AGG:

SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM UNNEST(ARRAY['sql', 'database', 'sql', 'postgresql', 'database']) AS tag;

Łączenie wielu kolumn tablicowych

W klauzuli FROM można równolegle rozpakować wiele tablic. PostgreSQL dopasowuje elementy według pozycji. Jeśli tablice mają różne długości, krótsza tablica zwraca wartości NULL dla dodatkowych pozycji dłuższej tablicy.

SELECT key, value
FROM UNNEST(
  ARRAY['name',    'city',      'role'],
  ARRAY['Alice',   'Istanbul',  'DBA']
) AS t(key, value);

Sprawdzenie wiedzy

Proszę sprawdzić swoją wiedzę na temat UNNEST i agregowania tablic w PostgreSQL.

Podsumowanie lekcji

W tej lekcji poznano sposób przekształcania tablic w wiersze i z powrotem za pomocą wbudowanych funkcji PostgreSQL:

UNNEST — rozwija tablicę do postaci jednego wiersza na element; działa zarówno z literałami, jak i kolumnami tabel.
WITH ORDINALITY — dołącza indeks pozycji do każdego rozpakowanego elementu, dzięki czemu można poznać jego pierwotną kolejność w tablicy.
Filtrowanie — po rozpakowaniu można używać WHERE tak samo jak w przypadku zwykłych wierszy.
ARRAY_AGG — odwrotność UNNEST; ponownie zbiera wiersze w tablicę, opcjonalnie z użyciem ORDER BY lub DISTINCT.
Równoległe UNNEST — wiele tablic w klauzuli FROM jest rozwijanych obok siebie, a elementy są dopasowywane według pozycji.

Opanowanie wzorca rozpakowanie–przetwarzanie–ponowna agregacja pozwala obsługiwać dane tablicowe z pełną ekspresywnością SQL.

Często zadawane pytania

Czy lekcja „UNNEST i agregowanie” jest bezpłatna?

Tak — pełny tekst „UNNEST i agregowanie” 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 „UNNEST i agregowanie”?

Zamieniaj tablice na wiersze i odwrotnie Ć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 3 z 4.

Ile czasu zajmuje lekcja „UNNEST i agregowanie”?

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

← Powrót do SQL Academy