Примеры сложных запросов с массивами: агрегирование, фильтрация, сортировка

Сегодня мы разберёмся, как извлекать максимум пользы из массивов в запросах: собирать значения в группы, фильтровать по содержимому и даже сортировать прямо внутри массивов. Это не теория ради теории — такие приёмы встречаются в отчётах, аналитике, персонализации и множестве реальных сценариев. Всё просто, если понять принцип — и именно этим мы сейчас займёмся.

Агрегирование данных с массивами

Работа с массивами особенно раскрывается, когда нужно сгруппировать данные. Вместо того чтобы получать несколько строк — мы собираем нужные значения в один аккуратный массив. Это упрощает анализ, делает выводы компактнее и часто избавляет от лишних подзапросов. Давайте посмотрим, как это работает на практике.

Пример 1: группировка данных в массивы с array_agg()

Когда вы хотите собрать значения из нескольких строк в одной группе в массив, на помощь приходит array_agg(). Это, пожалуй, самая полезная функция для обработки массивов при агрегации.

-- У нас есть таблица students с полями id, name и course
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- Вставляем несколько строк
INSERT INTO students (name, course) VALUES
('Алиса', 'Математика'),
('Боб', 'Математика'),
('Чарли', 'Физика'),
('Дэйв', 'Физика'),
('Эмма', 'Математика');

-- Группируем студентов по курсам в массивы
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

Результат:

Группировать значения в массивы удобно, если вы хотите передать данные в формате, который можно легко разобрать, например, в JSON.

Пример 2: создание вложенных массивов

А что, если у нас есть ещё одна таблица, и мы хотим собрать данные из двух таблиц в массивы? Например, таблица courses с информацией о преподавателях.

-- Создаём таблицу преподавателей
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- Вставляем данные
INSERT INTO courses (name, teacher) VALUES
('Математика', 'Проф. Мин'),
('Физика', 'Проф. Петерсон');

-- Вложенный запрос для создания массивов
SELECT
    c.name AS course_name,
    array_agg(s.name) AS students,
    c.teacher
FROM
    courses c
LEFT JOIN
    students s
ON
    c.name = s.course
GROUP BY
    c.name, c.teacher;

Результат:

Теперь у нас есть удобная таблица, показывающая курсы, их преподавателей и студентов в формате массивов.

Фильтрация данных с массивами

Сами по себе массивы — уже мощный инструмент, но настоящая магия начинается, когда мы учимся фильтровать данные на их основе. Нужно выбрать только тех пользователей, у которых в списке интересов есть конкретное слово? Или заказы, где каждая цена превышает заданный порог? Всё это можно сделать прямо в SQL — без лишней логики на стороне приложения.

Пример 1: фильтрация строк по элементам массива

Допустим, мы хотим найти все строки, где массив содержит определённое значение, например, мы ищем студентов, записанных на курс математики.

-- Фильтруем студентов, записанных на курсы с помощью `ANY`
SELECT *
FROM students
WHERE course = ANY(ARRAY['Математика', 'Физика']);

Здесь ANY позволяет нам указать массив значений, и запрос вернёт строки, где course соответствует хотя бы одному из значений в массиве.

Пример 2: Проверка на пересечение массивов

Теперь предположим, что у нас есть таблица student_interests, где указаны интересы студентов в виде массивов. Мы хотим найти студентов, чьи интересы пересекаются с нашими критериями.

-- Создаём таблицу с интересами студентов
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- Вставляем данные
INSERT INTO student_interests (name, interests) VALUES
('Алиса', ARRAY['программирование', 'музыка']),
('Боб', ARRAY['спорт', 'программирование']),
('Чарли', ARRAY['чтение', 'фотография']),
('Эмма', ARRAY['музыка', 'спорт']);

-- Ищем студентов, интересующихся программированием или музыкой
SELECT *
FROM student_interests
WHERE interests && ARRAY['программирование', 'музыка'];

Оператор && проверяет пересечение двух массивов. Если хотя бы один элемент из массива слева совпадает с массивом справа, строка проходит фильтр.

Результат:

Сортировка массивов

Иногда порядок значений внутри массива имеет значение — особенно если вы собираете массив из разных строк или хотите подготовить данные к отображению. PostgreSQL позволяет отсортировать элементы прямо в запросе, без дополнительной обработки.

Пример 1: сортировка значений внутри массива

Иногда нужно отсортировать элементы внутри массива. Например, давайте отсортируем массив интересов студентов по алфавиту.

-- Сортируем элементы массива с помощью функции `array_sort()`
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

Результат:

Пример 2: сортировка строк по длине массива

А теперь предположим, что мы хотим упорядочить студентов по количеству их интересов — от самых увлечённых до самых "скучных".

-- Сортируем строки по длине массива
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

Результат:

Хотя у всех студентов одинаковое количество интересов в примере, похожий запрос можно модифицировать для больших таблиц.

2

Задача

SQL SELF, 36 уровень, 3 лекция

Недоступна

Группировка значений в массивы

Группировка значений в массивы

Открыть