STREAM: примеры (SQL)
STREAMОписание функции
В Microsoft SQL Server не существует встроенной функции с именем STREAM. Вероятно, имеется в виду функция STRING_AGG, которая выполняет агрегацию строк, или функция STRING_SPLIT, которая разделяет строку на части. В данной статье рассматривается функция STRING_AGG, так как она часто ассоциируется с обработкой потоков строковых данных.
STRING_AGG — это агрегатная функция, доступная с SQL Server 2017 (и Azure SQL Database). Она объединяет строковые значения из группы в одну строку с указанным разделителем.
Синтаксис:STRING_AGG ( expression, separator ) [ <order_clause> ]
Аргументы:
- expression: Выражение любого типа данных, которое можно преобразовать в VARCHAR или NVARCHAR. Неагрегированные столбцы.
- separator: Разделитель, который добавляется между значениями. Может быть строковой константой или переменной.
- order_clause: Необязательная часть, которая определяет порядок объединения значений. Записывается как
WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] ).
Возвращаемое значение: Функция возвращает строку типа VARCHAR или NVARCHAR, содержащую все значения выражения, объединенные через разделитель. Если входные данные имеют тип NVARCHAR, возвращается NVARCHAR, иначе — VARCHAR. Для группы без строк возвращается NULL.
Простые примеры
Пример 1: Базовое использование с разделителем-запятой.
SELECT STRING_AGG(Name, ', ') AS Cities
FROM (VALUES ('Москва'), ('Санкт-Петербург'), ('Новосибирск')) AS T(Name);Cities Москва, Санкт-Петербург, Новосибирск
Пример 2: Использование WITHIN GROUP для упорядочивания.
SELECT STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name DESC) AS SortedCities
FROM (VALUES ('Москва'), ('Санкт-Петербург'), ('Новосибирск')) AS T(Name);SortedCities Санкт-Петербург, Новосибирск, Москва
Пример 3: Работа с NULL. NULL-значения игнорируются.
SELECT STRING_AGG(Name, '; ') AS WithNulls
FROM (VALUES ('А'), (NULL), ('Б'), (NULL), ('В')) AS T(Name);WithNulls А; Б; В
Похожие функции в MS SQL
- FOR XML PATH: Исторический метод для конкатенации строк до появления STRING_AGG. Более сложен в написании и менее производителен.
- STRING_SPLIT: Выполняет обратную операцию — разделяет строку на таблицу значений по разделителю. Появилась в SQL Server 2016.
- GROUP_CONCAT (в других СУБД): Аналог в MySQL и SQLite. В MS SQL для этого используется STRING_AGG.
STRING_AGG предпочтительнее использовать для агрегации строк в современных версиях SQL Server из-за простоты синтаксиса и лучшей производительности. FOR XML PATH остается вариантом для сложных сценариев или более старых версий.
Типичные ошибки
Ошибка 1: Попытка использовать STRING_AGG без GROUP BY для нескольких строк.
-- Неправильно, если в таблице несколько строк
SELECT STRING_AGG(Name, ', ') FROM Cities;-- Запрос выполнится, но вернет одну строку для всей таблицы. Часто это нежелательно без явного GROUP BY.
Ошибка 2: Использование некорректного типа данных для разделителя.
SELECT STRING_AGG(Name, 123) FROM Cities; -- Разделитель должен быть строкойСообщение об ошибке: Аргумент 2 для агрегатной функции string_agg имеет неверный тип.
Ошибка 3: Игнорирование максимального размера возвращаемой строки (ограничение VARCHAR(8000) или NVARCHAR(4000) по умолчанию).
-- Если результат превысит 8000 символов, будет усечение или ошибка в зависимости от уровня совместимости базы данных.Изменения в последних версиях
Функция STRING_AGG была введена в SQL Server 2017 и Azure SQL Database. В SQL Server 2019 и более поздних версиях были улучшения производительности и обработки больших строк.
Важное изменение: В уровнях совместимости базы данных 130 и выше (SQL Server 2016+), при превышении максимального размера возвращаемой строки, STRING_AGG вызывает ошибку вместо молчаливого усечения. Также, начиная с SQL Server 2019, функция лучше оптимизирована для работы с большими наборами данных.
Расширенные примеры
Пример 1: Группировка с STRING_AGG и JOIN.
SELECT
d.DepartmentName,
STRING_AGG(e.EmployeeName, ', ') WITHIN GROUP (ORDER BY e.HireDate) AS Employees
FROM Departments d
INNER JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName;DepartmentName | Employees Разработка | Иван Петров, Анна Сидорова Маркетинг | Ольга Иванова
Пример 2: Использование DISTINCT внутри STRING_AGG (требует вложенного запроса или CTE).
SELECT STRING_AGG(Name, ', ') AS UniqueCities
FROM (SELECT DISTINCT Name FROM Cities) AS T;UniqueCities Москва, Новосибирск, Санкт-Петербург
Пример 3: Комбинирование с STRING_SPLIT для обработки списков.
DECLARE @List NVARCHAR(100) = 'яблоко,груша,банан';
SELECT value AS Fruit FROM STRING_SPLIT(@List, ',');
-- Обратная операция:
SELECT STRING_AGG(value, '; ') AS NewList FROM STRING_SPLIT(@List, ',');Fruit яблоко груша банан NewList яблоко; груша; банан
Пример 4: Формирование JSON-подобного вывода.
SELECT
'[' + STRING_AGG('"' + ProductName + '"', ', ') WITHIN GROUP (ORDER BY ProductID) + ']' AS JsonArray
FROM Products
WHERE CategoryID = 1;JsonArray ["Молоко", "Хлеб", "Сыр"]
Аналоги в других СУБД и языках
- MySQL: Функция GROUP_CONCAT. Пример:
SELECT GROUP_CONCAT(Name SEPARATOR ', ') FROM cities; - PostgreSQL: Функция STRING_AGG (синтаксис аналогичен MS SQL) и array_agg с array_to_string.
- Oracle: Функция LISTAGG. Пример:
SELECT LISTAGG(Name, ', ') WITHIN GROUP (ORDER BY Name) FROM cities; - SQLite: Функция group_concat. Пример:
SELECT group_concat(Name, ', ') FROM cities; - Sybase ASE: Используется list (нестандартная) или аналогичные методы с XML.
- Язык программирования Python: Метод
str.join(). Пример:', '.join(['Москва', 'СПб'])
Основные отличия часто заключаются в именах функций и некоторых параметрах (например, обязательность ORDER BY в LISTAGG Oracle).