Jak działa UNNEST w języku SQL? Przykłady zastosowania

Pracując z bazami danych, często spotykam się z koniecznością pracy z tablicami wielowartościowymi. Jednym z operacyjnych narzędzi, które ułatwia manipulację takimi danymi, jest funkcja UNNEST w SQL. Pozwala ona na „rozpakowanie” tablicy do postaci wierszy, co znacznie upraszcza agregacje oraz analizy. W tym artykule pokażę, jak działa UNNEST, jakie ma zastosowania i na co zwrócić uwagę przy jego używaniu.

Co to jest UNNEST?

Funkcja UNNEST w SQL służy do zamiany tablicy na zbiór pojedynczych wierszy. Przydaje się zwłaszcza wtedy, gdy w bazie danych przechowujemy wartości w postaci tablicy i chcemy pracować z nimi jak z normalnymi wierszami tabeli.

Podstawowa składnia UNNEST

Najprostsza wersja zapytania z wykorzystaniem UNNEST wygląda tak:

SELECT UNNEST(ARRAY[1, 2, 3, 4, 5]) AS liczba;

Wynikiem tego zapytania będzie:

liczba
1
2
3
4
5

UNNEST w połączeniu z tabelami

Często mamy do czynienia z kolumnami przechowującymi wartości w postaci tablic. Poniżej przykład, w którym mamy tabelę zamowienia zawierającą identyfikator zamówienia i listę jego produktów:

CREATE TABLE zamowienia (
    id SERIAL PRIMARY KEY,
    produkty TEXT[]
);

INSERT INTO zamowienia (produkty) VALUES
    (ARRAY['Laptop', 'Myszka', 'Klawiatura']),
    (ARRAY['Monitor']),
    (ARRAY['Drukarka', 'Papier']);

Teraz chcemy uzyskać listę wszystkich produktów wraz z identyfikatorem zamówienia. Tutaj użyjemy UNNEST w połączeniu z LATERAL:

SELECT z.id, p.produkt
FROM zamowienia z,
LATERAL UNNEST(z.produkty) AS p(produkt);

Wynik:

id	produkt
1	Laptop
1	Myszka
1	Klawiatura
2	Monitor
3	Drukarka
3	Papier

UNNEST z wieloma tablicami

Możliwe jest również „rozpakowanie” kilku tablic jednocześnie, jeśli mają one ten sam rozmiar:

SELECT * 
FROM UNNEST(ARRAY['Jan', 'Anna', 'Piotr'], ARRAY[30, 25, 40]) 
AS dane(imie, wiek);

Rezultat będzie wyglądać tak:

imie	wiek
Jan	30
Anna	25
Piotr	40

Główne zastosowania UNNEST

Funkcja UNNEST jest niezwykle przydatna w różnych scenariuszach:

• Przetwarzanie danych zapisanych jako tablice i konwersja ich na wiersze.
• Łączenie tablic z danymi w innych tabelach poprzez operatory JOIN.
• Analiza danych, gdzie wartości są przechowywane w strukturach tablicowych.
• Filtrowanie i grupowanie wartości w tablicach poprzez standardowe funkcje agregujące SQL.

O czym warto pamiętać?

Podczas korzystania z UNNEST warto mieć na uwadze kilka rzeczy:

1. Sparowanie wielu tablic musi uwzględniać ich długość. Jeśli długości się nie zgadzają, SQL zwróci błąd.
2. Użycie UNNEST może wpłynąć na wydajność. Przy dużych zbiorach danych lepiej wcześniej przemyśleć indeksację.
3. Łączenie z innymi tabelami wymaga LATERAL. Jeśli korzystam z UNNEST na kolumnie w tabeli, często muszę użyć LATERAL do poprawnego działania.

Podsumowanie

UNNEST w języku SQL to potężne narzędzie pozwalające na zamianę tablic na wiersze, co ułatwia ich przetwarzanie i analizę. Funkcja ta znajduje zastosowanie w analizie danych, raportowaniu i łączeniu z innymi tabelami. Warto jednak pamiętać o wpływie na wydajność i konieczności stosowania LATERAL, gdy pracuje się z tabelami. Mam nadzieję, że ten artykuł rozjaśnił działanie UNNEST i ułatwi jego wykorzystanie w praktycznych scenariuszach.

 

Inny ciekawy artykuł:

Jak działa ARRAY_AGG w języku SQL? Przykłady zastosowania