Получить профессию в Skypro
Объединяем значения PostgreSQL в массив: пример с оценками
Быстрый ответ
Ознакомимся с основными методами преобразования значений в массивы в PostgreSQL:
-- Простая агрегация в массив
SELECT array_agg(название_колонки) FROM название_таблицы;Создаём массив для каждой группы из связных значений:
-- Группировка и агрегация в массив
SELECT групповая_колонка, array_agg(название_колонки) FROM название_таблицы GROUP BY групповая_колонка;Составляем отсортированные массивы, когда последовательность элементов важна:
-- Отсортированные массивы – используйте разумно!
SELECT групповая_колонка, array_agg(название_колонки ORDER BY колонка_сортировки) FROM название_таблицы GROUP BY групповая_колонка;Эти запросы — ваш надёжный инструментарий для создания и структурирования массивов в PostgreSQL.
Управление связью «Студент-Оценка»
Предположим, у нас в наличии таблицы Студент и Оценка. Наша задача — сгруппировать оценки учащихся в массивы:
-- Знакомимся с нашими таблицами.
CREATE TABLE Student (
Id SERIAL PRIMARY KEY,
Name VARCHAR(100)
);
CREATE TABLE Grade (
Id SERIAL PRIMARY KEY,
StudentId INT REFERENCES Student(Id),
Mark INT
);Составляем оценки в единый массив для каждого студента:
-- Объединяем оценки (студентам это должно прийтись по душе).
SELECT s.Name AS StudentName, array_agg(g.Mark ORDER BY g.Mark) AS Marks FROM Student s
LEFT JOIN Grade g ON s.Id = g.StudentId
GROUP BY s.Id, s.Name; -- Не забудьте про группировку по имени!Подзапросы для усиленного управления
Если стандартные соединения не справляются, на помощь приходят подзапросы, позволяющие создавать массивы:
-- Почти как в игре операции с данными.
SELECT s.Name AS StudentName,
(SELECT array_agg(Mark) FROM Grade WHERE StudentId = s.Id AND Mark > 75) AS TopMarks
FROM Student s;Здесь выбираются только высокие оценки. Без исключений, пора стараться учиться ещё усерднее!
Агрегация текстовых данных и их трансформация
Если вам интересно работать со строками, то STRING_AGG() вам приглянется:
-- Добавим разнообразие, работая со строками.
SELECT групповая_колонка, STRING_AGG(название_колонки::text, ', ' ORDER BY колонка_сортировки) FROM название_таблицы GROUP BY групповая_колонка;А вот как можно превратить массив в текстовую строку:
-- Как по волшебству преобразуем массив в строку.
SELECT групповая_колонка, array_to_string(array_agg(название_колонки), ', ') FROM название_таблицы GROUP BY групповая_колонка;Выбирайте подходящий для вашей задачи инструмент!
Визуализация
В PostgreSQL агрегирование преобразует множество разрозненных значений в упорядоченный комплект:
Неупорядоченная таблица данных (📚): [Запись 1 (📜), Запись 2 (📜), Запись 3 (📜)]Функция ARRAY_AGG() выступает в качестве личного архивariusа:
-- Магическое упорядочивание, ARRAY_AGGardium Dataosa!
SELECT ARRAY_AGG(записи) FROM записная_книжка;Результат будет выглядеть так:
Упакованный массив (📦): [📜, 📜, 📜]
# ARRAY_AGG() — это истинная Мари Кондо в мире данных.Поддержание порядка в данных
С помощью ORDER BY внутри array_agg() элементы сохраняют заданную последовательность:
-- Все в хронологическом порядке, словно в дневнике.
SELECT UserId, array_agg(Amount ORDER BY TransDate) AS OrderedAmounts
FROM Transactions
GROUP BY UserId;Продвинутая работа с null-значениями и дубликатами
Нулевые значения и дубликаты могут все испортить. По умолчанию array_agg() их включает. Но можно избавиться от них:
-- УХОДИТЕ, нулевые значения!
SELECT групповая_колонка, array_agg(название_колонки) FILTER (WHERE название_колонки IS NOT NULL) FROM название_таблицы GROUP BY групповая_колонка;Простите, дублированные элементы:
-- Дубликаты, уходим отсюда!
SELECT групповая_колонка, array_agg(DISTINCT название_колонки) FROM название_таблицы GROUP BY групповая_колонка;Чистый массив — залог успешного анализа данных!
Полезные материалы
- PostgreSQL: Документация: 9.21. Функции агрегации — Официальная документация PostgreSQL: подробное описание функций агрегации, ваш надёжный путеводитель в мире обработки данных.