it-gundan.com

문자열을 연결 / 집합하는 최적의 방법

다른 행의 문자열을 단일 행으로 집계하는 방법을 찾고 있습니다. 여러 곳 에서이 작업을 수행하려고 하므로이 기능을 사용하는 것이 좋습니다. COALESCEFOR XML을 (를) 사용하여 솔루션을 시도했지만 그들은 나를 위해 그것을 잘라 내지 않습니다.

문자열 집계는 다음과 같은 작업을 수행합니다.

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

COALESCEFOR XML의 대체물로 CLR 정의 집계 함수 를 살펴 보았지만 분명히 SQL Azure 는 CLR 정의를 지원하지 않습니다. CLR 정의 항목을 지원하지 않습니다. .

가능한 해결 방법 또는 유사하게 최적의 방법 (CLR만큼 최적은 아니지만 hey 내가 얻을 수있는 것을 취할 것입니다)을 집계하는 데 사용할 수 있습니까? 물건?

89
matt

해결책

optimal 의 정의는 다양 할 수 있지만 다음은 Azure에서 잘 작동하는 일반 Transact SQL을 사용하여 다른 행의 문자열을 연결하는 방법입니다.

;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

설명

이 접근법은 세 단계로 요약됩니다.

  1. OVERPARTITION 그룹화를 사용하여 행에 번호를 매기고 연결에 필요한 순서대로 정렬하십시오. 결과는 Partitioned CTE입니다. 나중에 결과를 필터링하기 위해 각 파티션에 행 수를 유지합니다.

  2. 재귀 CTE (Concatenated)를 사용하면 NameNumber 값을 Name 열에 추가하여 행 번호 (FullName 열)를 반복합니다.

  3. NameNumber이 (가) 가장 높은 결과를 제외한 모든 결과를 필터링하십시오.

이 쿼리를 예측 가능하게 만들려면 그룹화 (예 : 동일한 ID이 연결된 시나리오 행에서)와 정렬 (문자열을 사전 순으로 정렬하는 것으로 가정)을 정의해야합니다. 연쇄).

다음 데이터를 사용하여 SQL Server 2012에서 솔루션을 빠르게 테스트했습니다.

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

쿼리 결과 :

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

아래처럼 FOR XML PATH를 사용하는 메소드가 실제로 느리게 진행됩니까? Itzik Ben-Gan은이 방법이 그의 T-SQL Querying book (Mr. Ben-Gan은 신뢰할만한 출처라고 생각합니다)에서 좋은 성능을 가지고 있다고 말합니다.

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
44
slachterman

이것을 발견 한 우리를 위해 azure SQL Database를 사용하지 않습니다:

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

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

(Azure 업데이트를 위해 @Brianjorden 및 @milanio에게 감사합니다)

예제 코드 :

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

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

27
Hrobky

@ serge 답변은 정확하지만 xmlpath와 그의 방식의 시간 소비를 비교했지만 xmlpath가 너무 빠르다는 것을 알았습니다. 비교 코드를 작성하고 직접 확인할 수 있습니다. 이것은 @ 서지 방식입니다.

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

그리고 이것은 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
23
QMaster

업데이트 : MS SQL Server 2017+, Azure SQL 데이터베이스

다음을 사용할 수 있습니다 : STRING_AGG .

OP의 요청에 대한 사용법은 매우 간단합니다.

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

더 읽기

글쎄, 내 오래된 대답이 정당하게 삭제되었지만 (아래 그대로 유지) 나중에 누군가가 여기에 착륙하면 좋은 소식이 있습니다. Azure SQL Database에서도 STRING_AGG ()를 암시했습니다. 이 게시물에서 원래 요청한 정확한 기능과 기본 지원 기능이 제공되어야합니다. @hrobky는 이전에 이것을 SQL Server 2016 기능으로 언급했습니다.

--- Old Post : @hrobky에 직접 회신 할만큼 평판이 좋지 않지만 STRING_AGG는 훌륭하지만 SQL Server 2016 vNext에서만 사용할 수 있습니다. 바라건대 곧 Azure SQL Datababse를 따라갈 것입니다.

7
Brian Jorden

+ =를 사용하여 문자열을 연결할 수 있습니다. 예를 들면 다음과 같습니다.

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

@test를 선택하면 모든 이름이 연결됩니다.

2
jvc

Serge의 대답은 매우 유망한 것으로 나타 났지만 작성된대로 성능 문제가 발생했습니다. 그러나 임시 테이블을 사용하고 이중 CTE 테이블을 포함하지 않도록 재구성 한 경우 1000 개의 결합 된 레코드의 성능이 1 분 40 초에서 1 초 미만으로 바뀌 었습니다. 이전 버전의 SQL Server에서 FOR XML없이이 작업을 수행해야하는 사람은 다음과 같습니다.

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
;
1
Tom Halladay