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),
               '<a>',
               ',') 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, но это вообще мрачный жёппий, ибо вусмерть медленно и весьма нетривиально.

Программизм, , , Февраль 19, 2009

6 комментариев

  1. Leonid
    26 Май 2009 16:41

    Огромная благодарность Вам, Ангел Космоса, просто помогаете жить!

  2. vlad
    7 Октябрь 2009 16:26

    CLR Aggregate Functions, но это вообще мрачный жёппий, ибо вусмерть медленно и весьма нетривиально

    требую аргументов, а ещё лучше сравнения разных способов агрегации

  3. alexey
    17 Ноябрь 2009 4:33

    спасибо огромное, через подзапрос FOR XML — то что надо! я обычно через функцию делаю, но тут надо было временную таблицу сагрегировать. а через курсор страшно влом было))

  4. Serge I
    21 Ноябрь 2009 10:48

    Решение через for xml можно записать проще:

    select GroupId,

    replace((select Item as 'data()' from Items where GroupId=I.GroupId for XML path('')),' ',',') x

    from Items I

    Group By GroupId

  5. Igor Che
    10 Сентябрь 2010 20:13

    здорово!!! актуально!!! спс...

  6. Unarrival
    20 Декабрь 2010 9:42

    >>Serge I 21 Ноябрь 2009 10:48

    >>Решение через for xml можно записать проще:

    >>select GroupId,

    >>replace((select Item as 'data()' from Items where >>GroupId=I.GroupId for XML path('')),' ',',') x

    >>from Items I

    >>Group By GroupId

    а если пробелы есть в значениях Item — разобъет 1 item

Leave a Reply

Name (required)


Mail (required)


Website



cheap asp hosting best price in directory. электронная цифровая подпись получить . проект электронная россия .