Функция STRING_AGG

Агрегация текстовых данных

Рассмотрим такую задачу.

Получить список кораблей Японии труда не составляет:

SELECT name FROM Ships s JOIN Classes c ON s.class=c.class
WHERE country='Japan'
ORDER BY name;

В MySQL есть замечательная агрегатная функция GROUP_CONCAT, которая решает поставленную задачу:

SELECT GROUP_CONCAT(name) ships_list FROM Ships s JOIN Classes c ON s.class=c.class
WHERE country='Japan'
ORDER BY name;

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

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

SELECT country, GROUP_CONCAT(name) ships_list
FROM Ships s JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country, name;

Для SQL Server решение нашей задачи можно получить менее естественным способом - через представление результата выборки в форме XML:

SELECT STUFF(
(SELECT ','+name AS 'data()' FROM Ships s JOIN Classes c ON s.class=c.class
WHERE country='Japan'
ORDER BY name FOR XML PATH('')
),1,1,'');

Группировка по стране еще добавит сложности. Но мы не будем этого делать, поскольку в SQL Server, начиная с версии 2017, появилась функция STRING_AGG, позволяющая конкатенировать строки. Эта функция имеет два обязательных аргумента - строковое выражение, которое и будет использоваться для сцепления, и разделитель.

SELECT country, STRING_AGG(name,',') ships_list
FROM Ships s JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country;

Из представленного результата видно, что корабли в списке не отсортированы. Сортировка в стиле GROUP_CONCAT здесь не работает. Чтобы задать порядок сортировки, используется необязательное предложение WITHIN GROUP:

SELECT country, STRING_AGG(name,',') WITHIN GROUP (ORDER BY name) ships_list
FROM Ships s JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country;

При использовании функции STRING_AGG соединяемые значения преобразуются к типу данных VARCHAR (NVARCHAR).

Типом результата будет VARCHAR(8000) или NVARCHAR(4000), если среди соединяемых значений не будет значений типа VARCHAR(MAX) или NVARCHAR(MAX). В последнем случае результат будет иметь тип VARCHAR(MAX) (или NVARCHAR(MAX) соответственно).

В нижеследующем примере будет получена ошибка, поскольку длина результата - 8011 - превышает значение 8000:

• 8000 (значение ‘a’ будет дополнено пробелами, т.к. используется тип постоянной длины CHAR),
• 1 символ на запятую-разделитель,
• 10 (длина значения ‘bbbbbbbbbb’).

declare @a char(8000), @b varchar(10);
select @a='a',@b=replicate('b',10);
select string_agg(x,',') res
from (values(@b),(@a)) X(x);

Однако если, скажем, для переменной @b мы используем тип VARCHAR(MAX), то код выполнится без ошибки:

declare @a char(8000), @b varchar(max);
select @a='a',@b=replicate('b',10);
select string_agg(x,',') res
from (values(@b),(@a)) X(x);

Рекомендуемые упражнения:  133, 139

На предыдущей странице для SQL Server решалась задача вывода отсортированного списка кораблей каждой страны. Приведем здесь примеры решения этой задачи, для некоторых других популярных СУБД.

MySQL

Как уже упоминалось, в MySQL для этой цели служит функция GROUP_CONCAT:

SELECT country, GROUP_CONCAT(name ORDER BY name) ships_list
    FROM Ships s JOIN Classes c ON s.class=c.class
    GROUP BY country
    ORDER BY country;

Результаты:

Как видно по результатам, разделителем списка по умолчанию является запятая. Однако вы можете указать в качестве разделителя любую последовательность символов (а также пустую строку) с помощью необязательного предложения SEPARATOR. В примере ниже используется трехсимвольный разделитель " | “.

SELECT country, GROUP_CONCAT(name ORDER BY name SEPARATOR ' | ') ships_list
    FROM Ships s JOIN Classes c ON s.class=c.class
    GROUP BY country
    ORDER BY country;

PostgreSQL

В PostgreSQL также имеется функция STRING_AGG. Следующее ранее приведенное решение для SQL Server будет работать в PostgreSQL:

SELECT country, STRING_AGG(name, ',') ships_list
FROM Ships s JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country;

Однако для вывода отсортированного списка здесь используется необязательное предложение ORDER BY во втором аргументе функции:

SELECT country, STRING_AGG(name, ',' ORDER BY name) ships_list
FROM Ships s JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country;

Для тех версий PostgreSQL, которые не поддерживают функцию STRING_AGG, эту задачу можно решить следующим образом:

SELECT country, ARRAY_TO_STRING(ARRAY_AGG(name ORDER BY name),',') ships_list
    FROM Ships s JOIN Classes c ON s.class=c.class
    GROUP BY country
    ORDER BY country;

Здесь функция ARRAY_AGG формирует массив из имен кораблей, упорядочив их по возрастанию. Функция ARRAY_TO_STRING преобразует массив в строку, используя в нашем случае запятую в качестве разделителя элементов массива. Как и для MySQL, в качестве разделителя мы можем использовать любую строку символов. Но здесь разделитель является обязательным элементом, т.е. значения по умолчанию нет.

Oracle

Для Oracle решение похоже на решение для SQL Server, только функция агрегации называется LISTAGG, а не STRING_AGG:

SELECT country, LISTAGG(name,',') WITHIN GROUP (ORDER BY name) ships_list
    FROM Ships s JOIN Classes c ON s.class=c.class
    GROUP BY country
    ORDER BY country;

Есть и другие синтаксические отличия. Так, разделитель в LISTAGG, в отличие от STRING_AGG, является необязательным параметром. Если его опустить, выполняется конкатенация строк, т.е. по умолчанию разделителем является пустая строка (’’). Зато предложение WITHIN в Oracle является обязательным.

“Есть многое на свете, друг Горацио…”.