Новое в стандарте и реализациях языка SQL

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

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

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

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

Консоль

Выполнить

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

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,'');

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