UNNEST и агрегация
Превращайте массивы в строки и обратно
«UNNEST и агрегация» — бесплатный урок SQL Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Что такое UNNEST
PostgreSQL позволяет хранить массивы в одном столбце. Но иногда необходимо работать с каждым элементом отдельно — именно для этого используется UNNEST.
UNNEST — это функция, возвращающая набор, которая разворачивает массив в набор строк, по одной строке на элемент. Представьте ее как противоположность агрегированию: вместо объединения множества строк в одну она превращает одно значение во множество строк.
Пример базового использования UNNEST
Проще всего использовать UNNEST, передав ему литерал массива напрямую. Каждый элемент становится отдельной строкой в наборе результатов.
Здесь обычный массив целых чисел разворачивается в отдельные строки:
SELECT UNNEST(ARRAY[10, 20, 30, 40]) AS value;UNNEST с текстовыми массивами
UNNEST работает с любым типом массива, включая текстовый. Это полезно, когда у Вас есть столбец, в котором хранятся теги, категории или данные в стиле списка, разделенного запятыми, в виде массива PostgreSQL.
SELECT UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit;UNNEST из столбца таблицы
Настоящая сила UNNEST проявляется при работе с реальным столбцом таблицы. В каждой строке таблицы может находиться массив разной длины, и UNNEST развернет все массивы в отдельные строки.
В этом примере таблица products содержит столбец текстового массива 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;Подсчет вхождений тегов
После разворачивания столбца-массива Вы можете обрабатывать результаты как любые другие строки и применять агрегатные функции. Здесь подсчитывается, сколько товаров связано с каждым тегом.
Шаблон таков: UNNEST во вложенном запросе или CTE, затем GROUP BY по развернутому значению:
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 с порядковыми номерами
Иногда положение элемента в массиве имеет значение. PostgreSQL предоставляет WITH ORDINALITY, чтобы добавить порядковый номер к каждому развернутому элементу и сохранить информацию о его исходном индексе в массиве (начиная с 1).
SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);Использование порядковых номеров в таблице
WITH ORDINALITY особенно полезен, когда в массиве хранятся упорядоченные списки. Например, в таблице списков воспроизведения, где важен порядок дорожек:
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;Фильтрация после UNNEST
Поскольку UNNEST превращает элементы массива в строки, их можно фильтровать обычным предложением WHERE. Это позволяет найти все строки, массив которых содержит определенное значение, без использования оператора проверки содержимого массива.
SELECT DISTINCT name
FROM products,
UNNEST(tags) AS tag
WHERE tag = 'electronics';Обратное агрегирование в массив
Обратной операцией к UNNEST является ARRAY_AGG. После разворачивания строк и их преобразования или фильтрации можно собрать результаты обратно в массив. Этот шаблон — развернуть, обработать и снова агрегировать — является распространенным идиоматическим приемом PostgreSQL.
SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags
FROM (
SELECT UNNEST(ARRAY['cherry', 'apple', 'banana']) AS tag
) AS t;Удаление дубликатов из массива
Практическое применение преобразования «массив–строки–массив» — удаление дубликатов из массива. Сначала примените UNNEST к массиву, затем DISTINCT, а после этого снова соберите элементы с помощью ARRAY_AGG:
SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM UNNEST(ARRAY['sql', 'database', 'sql', 'postgresql', 'database']) AS tag;Объединение нескольких столбцов-массивов
Можно параллельно применить UNNEST к нескольким массивам в предложении FROM. PostgreSQL сопоставляет элементы по позициям. Если длины массивов различаются, короткий массив подставляет пустые значения для дополнительных позиций длинного массива.
SELECT key, value
FROM UNNEST(
ARRAY['name', 'city', 'role'],
ARRAY['Alice', 'Istanbul', 'DBA']
) AS t(key, value);Проверка знаний
Проверьте, насколько хорошо Вы понимаете работу UNNEST и агрегирование массивов в PostgreSQL.
Итоги урока
В этом уроке Вы научились преобразовывать массивы в строки и обратно с помощью встроенных функций PostgreSQL:
- UNNEST — разворачивает массив, создавая по одной строке для каждого элемента; работает как с литералами, так и со столбцами таблиц.
- WITH ORDINALITY — добавляет каждому развернутому элементу индекс позиции, чтобы можно было определить его исходный порядок в массиве.
- Фильтрация — после разворачивания можно использовать WHERE так же, как и для обычных строк.
- ARRAY_AGG — обратная операция по отношению к UNNEST: собирает строки обратно в массив, при необходимости применяя ORDER BY или DISTINCT.
- Параллельный UNNEST — несколько массивов в предложении FROM разворачиваются рядом и сопоставляются по позициям.
Освоив этот шаблон «разворачивание–обработка–повторное агрегирование», Вы сможете работать с данными-массивами, используя всю выразительную мощь SQL.
Часто задаваемые вопросы
Урок «UNNEST и агрегация» бесплатный?
Да — полный текст урока «UNNEST и агрегация» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «UNNEST и агрегация»?
Превращайте массивы в строки и обратно Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.
Сколько времени занимает урок «UNNEST и агрегация»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.