Группировка с агрегацией в строку

#sql #sql_server

Есть таблица (group_id int, text varchar(max), sum decimal(19,2))

данные

1 | qwe | 3.00

1 | asd | 5.00

2 | zxc | 10.00

2 | 123 | 15.00


Нужно индексированное представление, которое вернет

1 | qwe,asd | 8.00

2 | zxc,123 | 25.00

    

Ответы

Ответ 1

В данном случае обычное представление можно создать, индексированное - нет.

Любой способ, которым можно выполнить конкатенацию в SqlServer попадёт под ограничения
(ссылка, см. раздел Дополнительные требования).

Дело в том, что если в индексированном представлении используется агрегация, то оно
не только должно "уметь" агрегировать данные, при добавлении или изменении строк в
таблицах, на которых оно основано, но также и деагрегировать их, при удалении строк.
И с целью улучшения производительности это делается инкрементально, а не полным перестроением
индекса.

С функциями наподобие sum() и count() это можно сделать легко и однозначно. Если
добавили строк в таблицу, то значения их [sum] добавятся к соответствующим строкам
индексированного представления. Если удалили строки, то их [sum] вычтутся.

С конкатенацией нельзя такого сделать. Допустим у нас есть строки asd, qwe и d,q
в одной группе. Конкатенируя их через запятую мы получим в индексированном представлении
строку asd,qwe,d,q.

Теперь, допустим, удаляется строка с d,q. Как определить в каком месте в asd,qwe,d,q
нужно убрать d,q? Однозначно - никак. Поэтому такие вещи и запрещены.

Альтернатива - дополнительная постоянная таблица, которая будет обновляться (например,
триггером) при изменении данных в основной таблице.


Ответ 2

SELECT group_id, GROUP_CONCAT(text SEPARATOR ','), sum(``sum``) 
FROM table 
GROUP BY group_id;


Ответ 3

select group_id
,left(
    (
        select [text] + ','
        from table t2
        where t2.group_id = t1.group_id
        for xml path('')
    )
    ,len (
        (
            select [text] + ','
            from table t2
            where t2.group_id = t1.group_id
            for xml path('')
        )
    ) - 1
) as s123
, sum([sum]) as [sum]
from table t1
group by group_id