Optymalny sposób łączenia / agregowania ciągów


102

Znajduję sposób na agregowanie ciągów z różnych wierszy w jeden wiersz. Chcę to zrobić w wielu różnych miejscach, więc dobrze byłoby mieć funkcję ułatwiającą to. Wypróbowałem rozwiązania wykorzystujące COALESCEi FOR XML, ale one po prostu tego nie robią.

Agregacja ciągów mogłaby zrobić coś takiego:

id | Name                    Result: id | Names
-- - ----                            -- - -----
1  | Matt                            1  | Matt, Rocks
1  | Rocks                           2  | Stylus
2  | Stylus

Rzuciłem okiem na funkcje agregujące zdefiniowane w CLR jako zamiennik COALESCEi FOR XML, ale najwyraźniej SQL Azure nie obsługuje elementów zdefiniowanych w CLR, co jest dla mnie uciążliwe, ponieważ wiem, że możliwość ich użycia rozwiązałaby wiele problemów problemy dla mnie.

Czy jest jakieś możliwe obejście lub podobnie optymalna metoda (która może nie być tak optymalna jak CLR, ale hej , wezmę to, co mogę uzyskać), której mogę użyć do zagregowania moich rzeczy?


W jaki sposób for xmlnie działa to dla Ciebie?
Mikael Eriksson

4
To działa, ale przyjrzałem się planowi wykonania i każdy z nich for xmlpokazuje 25% wykorzystania pod względem wydajności zapytań (większość zapytań!)
Mat

2
Istnieją różne sposoby wykonania for xml pathzapytania. Niektórzy szybciej niż inni. Może to zależeć od twoich danych, ale te, które używają, distinctsą z mojego doświadczenia wolniejsze niż używanie group by. A jeśli używasz .value('.', nvarchar(max))do uzyskania połączonych wartości, powinieneś zmienić to na.value('./text()[1]', nvarchar(max))
Mikael Eriksson,

3
Twoja zaakceptowana odpowiedź przypomina moją odpowiedź na stackoverflow.com/questions/11137075/… która moim zdaniem jest szybsza niż XML. Nie daj się zwieść kosztom zapytań, potrzebujesz dużej ilości danych, aby zobaczyć, która jest szybsza. XML jest szybsze, co dzieje się @ MikaelEriksson za odpowiedź na tym samym pytaniem . Wybierz podejście XML
Michael Buen,

2
Zagłosuj na natywne rozwiązanie tego problemu tutaj: connect.microsoft.com/SQLServer/feedback/details/1026336
JohnLBevan

Odpowiedzi:


67

ROZWIĄZANIE

Definicja optymalnego może się różnić, ale oto sposób łączenia ciągów z różnych wierszy przy użyciu zwykłego języka Transact SQL, co powinno działać dobrze na platformie Azure.

;WITH Partitioned AS
(
    SELECT 
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM dbo.SourceTable
),
Concatenated AS
(
    SELECT 
        ID, 
        CAST(Name AS nvarchar) AS FullName, 
        Name, 
        NameNumber, 
        NameCount 
    FROM Partitioned 
    WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, 
        CAST(C.FullName + ', ' + P.Name AS nvarchar), 
        P.Name, 
        P.NameNumber, 
        P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C 
                ON P.ID = C.ID 
                AND P.NameNumber = C.NameNumber + 1
)
SELECT 
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

WYJAŚNIENIE

Podejście sprowadza się do trzech kroków:

  1. Ponumeruj wiersze, używając OVERi PARTITIONgrupując je i porządkując zgodnie z potrzebami konkatenacji. Wynik to PartitionedCTE. W każdej partycji przechowujemy liczbę wierszy, aby później filtrować wyniki.

  2. Używając rekurencyjnego CTE ( Concatenated) iteruj po numerach wierszy ( NameNumberkolumn) dodając Namewartości do FullNamekolumny.

  3. Odfiltruj wszystkie wyniki oprócz tych z najwyższym NameNumber.

Należy pamiętać, że aby zapytanie było przewidywalne, należy zdefiniować zarówno grupowanie (na przykład w scenariuszu wiersze z tym samym IDsą konkatenowane), jak i sortowanie (przyjąłem, że przed konkatenacją po prostu sortujesz ciąg alfabetycznie).

Szybko przetestowałem rozwiązanie na SQL Server 2012 z następującymi danymi:

INSERT dbo.SourceTable (ID, Name)
VALUES 
(1, 'Matt'),
(1, 'Rocks'),
(2, 'Stylus'),
(3, 'Foo'),
(3, 'Bar'),
(3, 'Baz')

Wynik zapytania:

ID          FullName
----------- ------------------------------
2           Stylus
3           Bar, Baz, Foo
1           Matt, Rocks

5
Sprawdziłem zużycie czasu w ten sposób z xmlpath i osiągnąłem około 4 milisekund vs około 54 milisekund. więc sposób xmplath jest lepszy, szczególnie w dużych przypadkach. Napiszę kod porównawczy w osobnej odpowiedzi.
QMaster,

Jest znacznie lepsze, ponieważ takie podejście działa tylko dla maksymalnie 100 wartości.
Romano Zumbé

@ romano-zumbé Użyj MAXRECURSION, aby ustawić limit CTE na cokolwiek potrzebujesz.
Serge Belov

1
O dziwo, CTE było dla mnie znacznie wolniejsze. sqlperformance.com/2014/08/t-sql-queries/ ... porównuje kilka technik i wydaje się zgadzać z moimi wynikami.
Nickolay

To rozwiązanie dla stołu z ponad 1 milionem rekordów nie działa. Mamy też limit rekursywnej głębi
Ardalan Shahgholi,

51

Czy metody używające FOR XML PATH, jak poniżej, są naprawdę takie wolne? Itzik Ben-Gan pisze, że ta metoda ma dobre wyniki w swojej książce T-SQL Querying (moim zdaniem pan Ben-Gan jest źródłem godnym zaufania).

create table #t (id int, name varchar(20))

insert into #t
values (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus')

select  id
        ,Names = stuff((select ', ' + name as [text()]
        from #t xt
        where xt.id = t.id
        for xml path('')), 1, 2, '')
from #t t
group by id

Nie zapomnij umieścić indeksu w tej idkolumnie, gdy rozmiar tabeli stanie się problemem.
milivojeviCH

1
A po przeczytaniu, jak działają rzeczy / dla ścieżki xml ( stackoverflow.com/a/31212160/1026 ), jestem przekonany, że jest to dobre rozwiązanie pomimo XML w nazwie :)
Nickolay

1
@slackterman Zależy od liczby obsługiwanych rekordów. Myślę, że XML jest niewystarczający przy niskich zliczeniach w porównaniu z CTE, ale przy wyższych liczebnościach zmniejsza ograniczenie działu rekurencji i jest łatwiejszy w nawigacji, jeśli zostanie wykonany poprawnie i zwięźle.
GoldBishop

Metody FOR XML PATH wybuchają, jeśli masz w swoich danych emotikony lub znaki specjalne / zastępcze !!!
devinbost

1
Ten kod daje w wyniku tekst zakodowany w xml ( &przełączony na &itd.). Tutajfor xml podano bardziej poprawne rozwiązanie .
Frédéric

33

Dla tych z nas, którzy to znaleźli i nie używają Azure SQL Database:

STRING_AGG()w PostgreSQL, SQL Server 2017 i Azure SQL
https://www.postgresql.org/docs/current/static/functions-aggregate.html
https://docs.microsoft.com/en-us/sql/t-sql/ funkcje / string-agg-transact-sql

GROUP_CONCAT()w MySQL
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

(Podziękowania dla @Brianjorden i @milanio za aktualizację platformy Azure)

Przykładowy kod:

select Id
, STRING_AGG(Name, ', ') Names 
from Demo
group by Id

SQL Fiddle: http://sqlfiddle.com/#!18/89251/1


1
Właśnie go przetestowałem i teraz działa dobrze z Azure SQL Database.
Mediolan,

5
STRING_AGGzostał przesunięty z powrotem do 2017 r. Nie jest dostępny w 2016 r.
Morgan Thrapp

1
Dziękuję Aamir i Morgan Thrapp za zmianę wersji SQL Server. Zaktualizowano. (W czasie pisania twierdzono, że jest obsługiwany w wersji 2016.)
Hrobky

25

Chociaż odpowiedź @serge jest poprawna, ale porównałem jego zużycie czasu z xmlpath i stwierdziłem, że xmlpath jest tak szybszy. Napiszę kod porównawczy i możesz to sprawdzić samodzielnie. To jest sposób @serge:

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @counter INT;
SET @counter = 1;

set nocount on;

declare @YourTable table (ID int, Name nvarchar(50))

WHILE @counter < 1000
BEGIN
    insert into @YourTable VALUES (ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC')
    SET @counter = @counter + 1;
END

SET @startTime = GETDATE()

;WITH Partitioned AS
(
    SELECT 
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM @YourTable
),
Concatenated AS
(
    SELECT ID, CAST(Name AS nvarchar) AS FullName, Name, NameNumber, NameCount FROM Partitioned WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, CAST(C.FullName + ', ' + P.Name AS nvarchar), P.Name, P.NameNumber, P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C ON P.ID = C.ID AND P.NameNumber = C.NameNumber + 1
)
SELECT 
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

SET @endTime = GETDATE();

SELECT DATEDIFF(millisecond,@startTime, @endTime)
--Take about 54 milliseconds

A to jest sposób xmlpath:

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @counter INT;
SET @counter = 1;

set nocount on;

declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))

WHILE @counter < 1000
BEGIN
    insert into @YourTable VALUES (@counter, ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC')
    SET @counter = @counter + 1;
END

SET @startTime = GETDATE();

set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

SET @endTime = GETDATE();

SELECT DATEDIFF(millisecond,@startTime, @endTime)
--Take about 4 milliseconds

2
+1, ty QMasterze (Czarnej Magii) ty! Dostałem jeszcze bardziej dramatyczną różnicę. (~ 3000 ms CTE w porównaniu z ~ 70 msec XML w programie SQL Server 2008 R2 w systemie Windows Server 2008 R2 na Intel Xeon E5-2630 v4 @ 2,20 GHZ x2 z ~ 1 GB wolnego). Jedyne sugestie to: 1) Albo użyj OP lub (najlepiej) ogólnych terminów dla obu wersji, 2) Ponieważ OP's Q to jak „konkatenować / agregować ciągi ” i jest to potrzebne tylko dla łańcuchów (w przeciwieństwie do wartości liczbowych ), ogólne terminy są zbyt ogólne. Po prostu użyj „GroupNumber” i „StringValue”, 3) Zadeklaruj i użyj zmiennej „Delimiter” i użyj „Len (Delimiter)” zamiast „2”.
Tom

1
+1 za brak rozszerzania znaków specjalnych do kodowania XML (np. „&” Nie jest rozszerzany do „& amp;”, jak w wielu innych gorszych rozwiązaniach)
Reversed Engineer

13

Aktualizacja: Ms SQL Server 2017+, Azure SQL Database

Można użyć: STRING_AGG.

Użycie jest dość proste na żądanie OP:

SELECT id, STRING_AGG(name, ', ') AS names
FROM some_table
GROUP BY id

Czytaj więcej

Cóż, mój stary brak odpowiedzi został słusznie usunięty (pozostawiony bez odpowiedzi poniżej), ale jeśli ktoś zdarzy się tu wylądować w przyszłości, jest dobra wiadomość. Zaimplementowali również STRING_AGG () w Azure SQL Database. Powinno to zapewnić dokładną funkcjonalność pierwotnie wymaganą w tym poście z natywną i wbudowaną obsługą. @hrobky wspomniał o tym wcześniej jako o funkcji SQL Server 2016 w tamtym czasie.

--- Stary post: Za mało reputacji, aby odpowiedzieć bezpośrednio @hrobky, ale STRING_AGG wygląda świetnie, jednak obecnie jest dostępny tylko w SQL Server 2016 vNext. Mamy nadzieję, że wkrótce trafi również do bazy danych Azure SQL Databse.


2
Właśnie to przetestowałem i działa jak urok w Azure SQL Database
Mediolan,

4
STRING_AGG()ma zostać udostępniony w programie SQL Server 2017 na dowolnym poziomie zgodności. docs.microsoft.com/en-us/sql/t-sql/functions/ ...
CVn

1
Tak. STRING_AGG nie jest dostępny w SQL Server 2016.
Magne

2

Możesz użyć + =, aby połączyć ciągi, na przykład:

declare @test nvarchar(max)
set @test = ''
select @test += name from names

jeśli wybierzesz @test, wszystkie nazwy zostaną połączone


Proszę podać dialekt lub wersję SQL od kiedy jest obsługiwany.
Hrobky,

Działa to w SQL Server 2012. Zauważ, że listę oddzieloną przecinkami można utworzyć za pomocąselect @test += name + ', ' from names
Art Schmidt,

4
Wykorzystuje niezdefiniowane zachowanie i nie jest bezpieczne. Jest to szczególnie prawdopodobne, że da to dziwny / nieprawidłowy wynik, jeśli masz ORDER BYw zapytaniu. Powinieneś użyć jednej z wymienionych alternatyw.
Dannnno

1
Ten typ zapytania nigdy nie był zdefiniowanym zachowaniem, aw SQL Server 2019 okazało się, że ma ono niepoprawne zachowanie bardziej konsekwentnie niż w poprzednich wersjach. Nie stosuj tego podejścia.
Matthew Rodatus

2

Uznałem, że odpowiedź Serge'a jest bardzo obiecująca, ale napotkałem również problemy z wydajnością, tak jak zostało napisane. Jednak kiedy zrestrukturyzowałem go tak, aby używał tabel tymczasowych i nie zawierał podwójnych tabel CTE, wydajność wzrosła z 1 minuty 40 sekund do sekundy dla 1000 połączonych rekordów. Tutaj jest dla każdego, kto musi to zrobić bez FOR XML w starszych wersjach SQL Server:

DECLARE @STRUCTURED_VALUES TABLE (
     ID                 INT
    ,VALUE              VARCHAR(MAX) NULL
    ,VALUENUMBER        BIGINT
    ,VALUECOUNT         INT
);

INSERT INTO @STRUCTURED_VALUES
SELECT   ID
        ,VALUE
        ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE) AS VALUENUMBER
        ,COUNT(*) OVER (PARTITION BY ID)    AS VALUECOUNT
FROM    RAW_VALUES_TABLE;

WITH CTE AS (
    SELECT   SV.ID
            ,SV.VALUE
            ,SV.VALUENUMBER
            ,SV.VALUECOUNT
    FROM    @STRUCTURED_VALUES SV
    WHERE   VALUENUMBER = 1

    UNION ALL

    SELECT   SV.ID
            ,CTE.VALUE + ' ' + SV.VALUE AS VALUE
            ,SV.VALUENUMBER
            ,SV.VALUECOUNT
    FROM    @STRUCTURED_VALUES SV
    JOIN    CTE 
        ON  SV.ID = CTE.ID
        AND SV.VALUENUMBER = CTE.VALUENUMBER + 1

)
SELECT   ID
        ,VALUE
FROM    CTE
WHERE   VALUENUMBER = VALUECOUNT
ORDER BY ID
;
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.