Сортировка результатов функции string_agg() в PostgreSQL

Быстрый ответ

Для выполнения сортировки результата агрегации строк, используйте оператор ORDER BY внутри функции STRING_AGG():

STRING_AGG(column, ', ' ORDER BY column)

Например, чтобы составить отсортированный список фамилий last_names из таблицы employees, выполните:

SELECT STRING_AGG(last_name, ', ' ORDER BY last_name) FROM employees;

Предварительная сортировка с помощью подзапроса

Если вам необходима более сложная сортировка или упорядочивание по нескольким столбцам, используйте подзапрос для предварительной сортировки данных:

SELECT STRING_AGG(last_name, ', ' ORDER BY order_column)
FROM (
    SELECT last_name, order_column
    FROM employees
    ORDER BY order_column 
) as better_order_than_Starbucks;

Такой подход позволяет осуществить сортировку перед объединением строк. И не забывайте отслеживать обновления PostgreSQL — они могут расширить возможности SQL-операций!

Тонкости предварительной сортировки

Сортировка типов данных различной природы

Убедитесь, что столбцы с различными типами данных явно приведены к одному типу для исключения ошибок вида unsortable column.

Подробнее об этом расскажет наш спикер на видео

Сложная сортировка

Для работы со сложной логикой сортировки используйте операторы CASE или примените вложенную сортировку:

SELECT STRING_AGG(last_name, ', ' ORDER BY CASE WHEN condition THEN order_column1 ELSE order_column2 END) 
FROM employees;

Сортировка по шаблонам

Если вам необходимо сортировать данные в соответствии с определенным шаблоном, используйте подзапрос или общее табличное выражение (Common Table Expression, CTE) для осуществления необходимой фильтрации и подбора нужных строк для итоговой выборки.

Следите за обновлениями PostgreSQL

PostgreSQL развивается так же быстро, как Пикачу с помощью Камня Молнии. Следите за нововведениями и обновлениями синтаксиса. Мы ловим возможности string_agg(), а не сквиртла.

Клауза WITHIN GROUP

С версии PostgreSQL 9.0 стала доступной клауза WITHIN GROUP, позволяющая упорядочивать данные перед их группировкой:

SELECT STRING_AGG(column, ', ' ORDER BY sort_column) WITHIN GROUP (ORDER BY sort_column) 
FROM table 
GROUP BY id;

Использование WITHIN GROUP делает запрос максимально ляконичным и понятным.

Визуализация

Неупорядоченные данные сравнимы с хаотическим движением поезда (🚂), перевозящего цепочку вагонов (📊):

🚂===>📊📉📈📊📗📘📕

Используя STRING_AGG() с ORDER BY, мы получаем возможность упорядочивания вагонов по необходимости:

STRING_AGG(column, ',' ORDER BY sort_column)

И вот что получится в итоге:

🚂===>📊📊📊📊📗📘📕

Это принципиально так же, как если бы вы использовали кнопку 'Сортировать' в неупорядоченной таблице Excel, но с помощью string_agg().

Советы и рекомендации, которые обычно упускает из виду SQL-волшебник

Псевдонимы для эффективной работы

Используйте псевдонимы таблиц для упрощения сложных запросов и более «плавного» перемещения по данным.

Владение мастерством использования GROUP BY

Научитесь использовать клаузу GROUP BY для получения структурированных агрегированных результатов.

Проверка кода с помощью SQL fiddle

Заведите дружбу с SQL fiddle, который поможет вам проверить и исправить ошибки в SQL-запросах. Ведь два мудреца всегда лучше, чем один!

Различия в SQL-диалектах

Обратите внимание на то, что синтаксис SQL может отличаться в различных системах управления базами данных. То, что работает в PostgreSQL, может вызывать проблемы в SQL Server или MySQL.

Полезные материалы

1. PostgreSQL: Документация: 16: 9.21. Агрегатные функции
2. Как сохранить и вернуть объект JavaScript с подмассивом в нормализованном SQL – Stack Overflow
3. STRING_AGG (Transact-SQL) – SQL Server | Microsoft Learn
4. Конкатенация строковых значений в Transact-SQL – Simple Talk
5. SQL GROUP BY | Промежуточный SQL – Mode
6. Шифрование – Каков аналог технологии Always Encrypted SQL Server 2016 в MySQL? – Database Administrators Stack Exchange