복붙노트

[SQL] CONCATENATE / 집계 문자열에 최적의 방법

SQL

CONCATENATE / 집계 문자열에 최적의 방법

나는 하나의 행에 다른 행의 문자열을 집계 할 수있는 방법을 찾는거야. 그래서이 될 좋은를 촉진하는 기능을 갖는 많은 다른 장소에서이 일을 찾고 있어요. 나는 유착 및 XML FOR를 사용하여 솔루션을 시도했지만, 그들은 단지 나를 위해 그것을 잘라하지 않습니다.

문자열 집계는 이런 짓을 할 것이다 :

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

나는 유착 및 XML을 대체 CLR 정의 집계 함수에서 살펴 보았다,하지만 분명히 SQL 애저는 나는 그것이를 해결할 사용할 수있는 알고 있기 때문에 나를 위해 고통 CLR 정의 물건을 지원하지 않습니다 나를 위해 문제의 훨씬.

나는 내 물건을 집계하는 데 사용할 수있는 모든 가능한 해결 방법, 또는 (CLR과 같은 최적으로되지 않을 수도 있습니다,하지만 헤이 내가 무엇을 얻을 수 할게요) 유사 최적의 방법이 있습니까?

해결법

  1. ==============================

    1.해결책

    해결책

    최적의 정의는 다양하지만, 여기에 푸른에서 잘 작동합니다 일반 거래 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

    설명

    접근 방식은 세 단계로 요약된다 :

    이 쿼리 예측 가능한 일을하기 위해 모두 (연결된다 동일한 ID와 시나리오 행, 예를 들어) 그룹화 및 정렬을 정의하는 것을 명심하십시오 (나는 가정 당신 알파벳 순으로 연결하기 전에 간단하게 정렬 문자열).

    나는 빨리 다음 데이터를 SQL 서버 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
  2. ==============================

    2.아래 아주 천천히 그렇게 XML 경로에 대한 사용 방법은? Itzik 벤의 GaN이 방법은 자신의 T-SQL 쿼리 책 (미스터 벤 질화 갈륨 (GaN) 내보기에, 신뢰할 수있는 소스입니다)에서 좋은 성능을 가지고 씁니다.

    아래 아주 천천히 그렇게 XML 경로에 대한 사용 방법은? Itzik 벤의 GaN이 방법은 자신의 T-SQL 쿼리 책 (미스터 벤 질화 갈륨 (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
  3. ==============================

    3.이를 발견하고 푸른 SQL 데이터베이스를 사용하지 않는 사람들을 위해 :

    이를 발견하고 푸른 SQL 데이터베이스를 사용하지 않는 사람들을 위해 :

    PostgreSQL의에서 STRING_AGG (), SQL 서버 2017과 푸른 SQL 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

    (푸른 업데이트 @Brianjorden에 감사 @milanio)

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

    SQL 바이올린 : http://sqlfiddle.com/#!18/89251/1

  4. ==============================

    4.@serge 대답은 정확하지만,하지만 난 xmlpath에 대한 그의 방법의 시간 소비를 비교 나는 xmlpath이 너무 빠르게 발견했다. 나는 비교 코드를 작성하는 것입니다 그리고 당신은 스스로를 확인할 수 있습니다. 이 @serge 방법입니다 :

    @serge 대답은 정확하지만,하지만 난 xmlpath에 대한 그의 방법의 시간 소비를 비교 나는 xmlpath이 너무 빠르게 발견했다. 나는 비교 코드를 작성하는 것입니다 그리고 당신은 스스로를 확인할 수 있습니다. 이 @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

    그리고 이것은 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
  5. ==============================

    5.업데이트 : MS SQL 서버 2017+, 푸른 SQL 데이터베이스

    업데이트 : MS SQL 서버 2017+, 푸른 SQL 데이터베이스

    당신은 사용할 수 있습니다 STRING_AGG를.

    사용법은 영업 이익의 요청에 대해 매우 간단하다 :

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

    자세히보기

    그럼 나의 오래된 비 대답은 정당 (에 - 재치 아래 왼쪽)을 삭제하지만, 사람이 미래에 여기에 착륙 발생하는 경우, 좋은 소식이있어. 그들은뿐만 아니라 푸른 SQL 데이터베이스에 STRING_AGG ()을 implimented있다. 즉, 정확한 기능은 원래 기본이 게시물에 요청 및 지원에 내장 제공해야한다. @hrobky 시간에 SQL 서버 2016 기능으로 이전에이 언급했다.

    --- 이전 게시물 : 여기 충분하지 않습니다 명성을 직접 @hrobky에 댓글을 올리려면하지만 STRING_AGG는 현재 SQL 서버 2016 vNext에서만 사용할 수 있지만, 멋지다. 희망이 곧뿐만 아니라 푸른 SQL Datababse에 따를 것이다 ..

  6. ==============================

    6.당신은 예를 들어, + = CONCATENATE 문자열로 사용할 수 있습니다 :

    당신은 예를 들어, + = CONCATENATE 문자열로 사용할 수 있습니다 :

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

    당신이 @Test 선택하면, 그것은 당신에게 연결된 모든 이름을 줄 것이다

  7. ==============================

    7.나는 방탄복의 대답은 매우 유망 할 찾았지만 작성된-I는 또한 성능 문제가 발생했습니다. 나는 구조 조정 때, 그것은 임시 테이블을 사용하지 이중 CTE 테이블을 포함, 성능은 1,000 결합 기록을 위해 하위 두 번째로 1 분에서 40 초 갔다. 여기가 SQL 서버의 이전 버전에 XML을위한없이이 작업을 수행해야하는 사람입니다 :

    나는 방탄복의 대답은 매우 유망 할 찾았지만 작성된-I는 또한 성능 문제가 발생했습니다. 나는 구조 조정 때, 그것은 임시 테이블을 사용하지 이중 CTE 테이블을 포함, 성능은 1,000 결합 기록을 위해 하위 두 번째로 1 분에서 40 초 갔다. 여기가 SQL 서버의 이전 버전에 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
;

    8. from https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게이 게임 통계 쿼리를 단순화 할 수 있습니까?  (0) 2020.03.14
[SQL] 클래스에서 전역 변수를 사용하여  (0) 2020.03.14
[SQL] MySQL은 NULL (성능 및 스토리지)  (0) 2020.03.14
[SQL] 오라클 "(+)"운영자  (0) 2020.03.14
[SQL] PostgreSQL의 쿼리에 변수를 선언하는 방법  (0) 2020.03.14
,

티스토리툴바