5 Способов Выполнить Агрегацию Строк В Ms Sql

Иногда возникает необходимость агрегировать строки в SQL-запросе, то есть по следующему набору данных:

ГРУПП_КОНКАТ() :

 SELECT GroupId, GROUP_CONCAT(Item SEPARATOR ",") AS ItemList
 FROM Items

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

 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

Это основано на трюке ВЫБИРАТЬ вар = вар + ',' + col FROM где .

Да, вы можете сделать это, и это работает:

 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 не ниже 2000. Да, есть еще способ агрегировать строки через Агрегатные функции CLR , но это вообще мрачный ужас, ибо убийственно медленно и несоответственно сложности задачи.

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

Жду комментариев и критики.

И еще: если кто знает, как сделать подсветку кода, как это сделал я дома - рассказывать.

Я пока не вижу другого способа, кроме вставки скриншотов.

Теги: #sql #ms sql-сервер #агрегация строк #Transact-SQL #извращения #Чулан

• Область Применения Программного Обеспечения Для Восстановления В Управлении Файлами

  19 Oct, 24

• Как Небольшие Компании Могут Сотрудничать С Крупными Корпорациями

  19 Oct, 24

• Почему Мы Не Берем На Работу Новичков Или 5 Мифов Об Обучающих Платформах

  19 Oct, 24

• Шард Всемогущий: Как Масштабировать Субд Для Высоконагруженной Системы

  19 Oct, 24

• «Я Не Могу Себе Представить, Почему Мне Это Не Нравится. Турбо С++ Не Запускает Программу..» (С) Виктор Сергеевич

  19 Oct, 24

• Меньше Да Лучше: Mozilla Firefox «Худеет», Чтобы Работать Быстрее Конкурентов

  19 Oct, 24

• Опасные Периферийные Устройства: Понимание Thunderspy

  19 Oct, 24

• Краш-Тест Запуска 3.0, Бета-Версия. Киев

  19 Oct, 24

• Март — Месяц Ошибок Php.

  19 Oct, 24

• Поведенческие Технологии В Сети Рлэ.

  19 Oct, 24