5 cпособов сделать агрегацию строк в SQL Server'е
Иногда возникает необходимость осуществить агрегацию строк в SQL запросе, то есть, по такому набору данных:
GroupId
Item
1
AAA
2
IS
5
OMG
2
WHAT
2
THE
1
This
получить примерно такой:
GroupId
ItemList
1
AAA,This
2
IS,WHAT,THE
5
OMG
MySQL, например, для таких целей обладает встроенной функцией GROUP_CONCAT():
SELECT GroupId, GROUP_CONCAT(Item SEPARATOR ",") AS ItemList FROM Items В MS SQL Server'e такой функции нету, поэтому приходится извращаться. Перед тем, как приступить, сделаем скрипт для создания тестовой таблицы:
CREATE TABLE Items(GroupId INT, Item NVARCHAR(10)) INSERT INTO Items(GroupId, Item) SELECT 1 AS GroupId, 'AAA' AS Item UNION ALL SELECT 2, 'IS' UNION ALL SELECT 5, 'OMG' UNION ALL SELECT 2, 'WHAT' UNION ALL SELECT 2, 'THE' UNION ALL SELECT 1, 'This' Итак, начнем. Самый тупой прямолинейный способ - создать временную таблицу и собирать в нее промежуточные результаты агрегации, пробегая по таблице Items курсором. Этот способ очень медленно работает и код его страшен. Любуйтесь:
DECLARE @Aggregated TABLE (GroupId INT, ItemList NVARCHAR(100)) DECLARE ItemsCursor CURSOR READ_ONLY FOR SELECT GroupId, Item FROM Items DECLARE @CurrentGroupId INT DECLARE @CurrentItem NVARCHAR(10) DECLARE @CurrentItemList NVARCHAR(100) OPEN ItemsCursor FETCH NEXT FROM ItemsCursor INTO @CurrentGroupId, @CurrentItem WHILE @@FETCH_STATUS = 0 BEGIN SET @CurrentItemList = (SELECT ItemList FROM @Aggregated WHERE GroupId = @CurrentGroupId) IF @CurrentItemList IS NULL INSERT INTO @Aggregated(GroupId, ItemList) VALUES(@CurrentGroupId, @CurrentItem) ELSE UPDATE @Aggregated SET ItemList = ItemList + ',' + @CurrentItem WHERE GroupId = @CurrentGroupId FETCH NEXT FROM ItemsCursor INTO @CurrentGroupId, @CurrentItem END CLOSE ItemsCursor DEALLOCATE ItemsCursor SELECT GroupId, ItemList FROM @Aggregated Есть более красивый способ, не использующий временных таблиц. Он основан на трюке SELECT @var = @var + ',' + col FROM smwhere. Да, так можно и это работает:
CREATE FUNCTION ConcatItems(@GroupId INT) RETURNS NVARCHAR(100) AS BEGIN DECLARE @ItemList varchar(8000) SET @ItemList = '' SELECT @ItemList = @ItemList + ',' + Item FROM Items WHERE GroupId = @GroupId RETURN SUBSTRING(@ItemList, 2, 100) END GO SELECT GroupId, dbo.ConcatItems(GroupId) ItemList FROM Items GROUP BY GroupId Немного лучше, но все же костылевато. В случае, когда нам известно, что максимальное количество агрегируемых строк ограничего, можно использовать следующий способ (этот запрос основан на предположении, что не существует группы с более чем четырьмя элементами в ней):
SELECT GroupId, CASE Item2 WHEN '' THEN Item1 ELSE CASE Item3 WHEN '' THEN Item1 + ',' + Item2 ELSE CASE Item4 WHEN '' THEN Item1 + ',' + Item2 + ',' + Item3 ELSE Item1 + ',' + Item2 + ',' + Item3 + ',' + Item4 END END END AS ItemList FROM ( SELECT GroupId, MAX(CASE ItemNo WHEN 1 THEN Item ELSE '' END) AS Item1, MAX(CASE ItemNo WHEN 2 THEN Item ELSE '' END) AS Item2, MAX(CASE ItemNo WHEN 3 THEN Item ELSE '' END) AS Item3, MAX(CASE ItemNo WHEN 4 THEN Item ELSE '' END) AS Item4 FROM ( SELECT GroupId, Item, ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Item) ItemNo FROM Items ) AS OrderedItems GROUP BY GroupId ) AS AlmostAggregated Да, много кода. Но зато ни одного лишнего объекта в БД - просто один чистый селект. Это иногда важно. Тем не менее, существует способ обойти и ограничение на размер группы, оставшись при этом в рамках одного запроса. Мы будем собирать все элементы группы в XML-поле, которое затем сконвертим к строковому типу и заменим теги между элементами на запятые:
SELECT GroupId, REPLACE(SUBSTRING(ItemListWithTags, 4, LEN(ItemListWithTags)-7), '', ',') AS ItemList FROM ( SELECT GroupId, CAST(XmlItemList AS NVARCHAR(200)) ItemListWithTags FROM ( SELECT GroupId, (SELECT Item AS A FROM Items ii WHERE ii.GroupId = GroupIds.GroupId FOR XML PATH('')) AS XmlItemList FROM (SELECT DISTINCT GroupId FROM Items) AS GroupIds ) AS subq1 ) AS subq2 В общем, работает не очень шустро, зато всегда. И конечно, нужен SQL Server не ниже 2005. Да, еще есть способ агрегировать строки через CLR Aggregate Functions, но это вообще мрачный жёппий, ибо вусмерть медленно и весьма нетривиально.
SELECT GroupId, GROUP_CONCAT(Item SEPARATOR ",") AS ItemList FROM Items В MS SQL Server'e такой функции нету, поэтому приходится извращаться. Перед тем, как приступить, сделаем скрипт для создания тестовой таблицы:
CREATE TABLE Items(GroupId INT, Item NVARCHAR(10)) INSERT INTO Items(GroupId, Item) SELECT 1 AS GroupId, 'AAA' AS Item UNION ALL SELECT 2, 'IS' UNION ALL SELECT 5, 'OMG' UNION ALL SELECT 2, 'WHAT' UNION ALL SELECT 2, 'THE' UNION ALL SELECT 1, 'This' Итак, начнем. Самый тупой прямолинейный способ - создать временную таблицу и собирать в нее промежуточные результаты агрегации, пробегая по таблице Items курсором. Этот способ очень медленно работает и код его страшен. Любуйтесь:
DECLARE @Aggregated TABLE (GroupId INT, ItemList NVARCHAR(100)) DECLARE ItemsCursor CURSOR READ_ONLY FOR SELECT GroupId, Item FROM Items DECLARE @CurrentGroupId INT DECLARE @CurrentItem NVARCHAR(10) DECLARE @CurrentItemList NVARCHAR(100) OPEN ItemsCursor FETCH NEXT FROM ItemsCursor INTO @CurrentGroupId, @CurrentItem WHILE @@FETCH_STATUS = 0 BEGIN SET @CurrentItemList = (SELECT ItemList FROM @Aggregated WHERE GroupId = @CurrentGroupId) IF @CurrentItemList IS NULL INSERT INTO @Aggregated(GroupId, ItemList) VALUES(@CurrentGroupId, @CurrentItem) ELSE UPDATE @Aggregated SET ItemList = ItemList + ',' + @CurrentItem WHERE GroupId = @CurrentGroupId FETCH NEXT FROM ItemsCursor INTO @CurrentGroupId, @CurrentItem END CLOSE ItemsCursor DEALLOCATE ItemsCursor SELECT GroupId, ItemList FROM @Aggregated Есть более красивый способ, не использующий временных таблиц. Он основан на трюке SELECT @var = @var + ',' + col FROM smwhere. Да, так можно и это работает:
CREATE FUNCTION ConcatItems(@GroupId INT) RETURNS NVARCHAR(100) AS BEGIN DECLARE @ItemList varchar(8000) SET @ItemList = '' SELECT @ItemList = @ItemList + ',' + Item FROM Items WHERE GroupId = @GroupId RETURN SUBSTRING(@ItemList, 2, 100) END GO SELECT GroupId, dbo.ConcatItems(GroupId) ItemList FROM Items GROUP BY GroupId Немного лучше, но все же костылевато. В случае, когда нам известно, что максимальное количество агрегируемых строк ограничего, можно использовать следующий способ (этот запрос основан на предположении, что не существует группы с более чем четырьмя элементами в ней):
SELECT GroupId, CASE Item2 WHEN '' THEN Item1 ELSE CASE Item3 WHEN '' THEN Item1 + ',' + Item2 ELSE CASE Item4 WHEN '' THEN Item1 + ',' + Item2 + ',' + Item3 ELSE Item1 + ',' + Item2 + ',' + Item3 + ',' + Item4 END END END AS ItemList FROM ( SELECT GroupId, MAX(CASE ItemNo WHEN 1 THEN Item ELSE '' END) AS Item1, MAX(CASE ItemNo WHEN 2 THEN Item ELSE '' END) AS Item2, MAX(CASE ItemNo WHEN 3 THEN Item ELSE '' END) AS Item3, MAX(CASE ItemNo WHEN 4 THEN Item ELSE '' END) AS Item4 FROM ( SELECT GroupId, Item, ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Item) ItemNo FROM Items ) AS OrderedItems GROUP BY GroupId ) AS AlmostAggregated Да, много кода. Но зато ни одного лишнего объекта в БД - просто один чистый селект. Это иногда важно. Тем не менее, существует способ обойти и ограничение на размер группы, оставшись при этом в рамках одного запроса. Мы будем собирать все элементы группы в XML-поле, которое затем сконвертим к строковому типу и заменим теги между элементами на запятые:
SELECT GroupId, REPLACE(SUBSTRING(ItemListWithTags, 4, LEN(ItemListWithTags)-7), '', ',') AS ItemList FROM ( SELECT GroupId, CAST(XmlItemList AS NVARCHAR(200)) ItemListWithTags FROM ( SELECT GroupId, (SELECT Item AS A FROM Items ii WHERE ii.GroupId = GroupIds.GroupId FOR XML PATH('')) AS XmlItemList FROM (SELECT DISTINCT GroupId FROM Items) AS GroupIds ) AS subq1 ) AS subq2 В общем, работает не очень шустро, зато всегда. И конечно, нужен SQL Server не ниже 2005. Да, еще есть способ агрегировать строки через CLR Aggregate Functions, но это вообще мрачный жёппий, ибо вусмерть медленно и весьма нетривиально.