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

Сьогодні розберемося, як витискати максимум користі з масивів у запитах: збирати значення в групи, фільтрувати по вмісту і навіть сортувати прямо в масивах. Це не теорія заради теорії — такі штуки реально зустрічаються у звітах, аналітиці, персоналізації і купі реальних кейсів. Все просто, якщо зрозуміти принцип — і саме цим зараз займемося.

Агрегування даних з масивами

Робота з масивами особливо розкривається, коли треба згрупувати дані. Замість того, щоб отримувати кілька рядків — ми збираємо потрібні значення в один акуратний масив. Це спрощує аналіз, робить результати компактнішими і часто рятує від зайвих підзапитів. Давай подивимось, як це працює на практиці.

Приклад 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: сортування значень всередині масиву

Іноді треба відсортувати елементи всередині масиву. Наприклад, давай відсортуємо масив інтересів студентів по алфавіту.

-- У PostgreSQL 17 немає вбудованої функції array_sort. Сортуємо масив через unnest + array_agg:
SELECT
    name,
    ARRAY(SELECT unnest(interests) AS x ORDER BY x) 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;

Результат:

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