sql - STRING_AGG가 예상대로 행동하지 않음




sql-server sql-server-2017 (2)

다음과 같은 쿼리가 있습니다.

WITH cteCountryLanguageMapping AS (
    SELECT * FROM (
        VALUES
            ('Spain', 'English'),
            ('Spain', 'Spanish'),
            ('Sweden', 'English'),
            ('Switzerland', 'English'),
            ('Switzerland', 'French'),
            ('Switzerland', 'German'),
            ('Switzerland', 'Italian')
    ) x ([Country], [Language])
)
SELECT
    [Country],
    CASE COUNT([Language])
        WHEN 1 THEN MAX([Language])
        WHEN 2 THEN STRING_AGG([Language], ' and ')
        ELSE STRING_AGG([Language], ', ')
    END AS [Languages],
    COUNT([Language]) AS [LanguageCount]
FROM cteCountryLanguageMapping
GROUP BY [Country]

스위스의 언어 열의 값이 쉼표로 구분 될 것으로 예상했습니다. 예 :

  | Country     | Languages                                 | LanguageCount
--+-------------+-------------------------------------------+--------------
1 | Spain       | Spanish and English                       | 2
2 | Sweden      | English                                   | 1
3 | Switzerland | French, German, Italian, English          | 4

대신 아래 출력을 얻고 있습니다 (4 개의 값은 and 로 구분됩니다).

  | Country     | Languages                                 | LanguageCount
--+-------------+-------------------------------------------+--------------
1 | Spain       | Spanish and English                       | 2
2 | Sweden      | English                                   | 1
3 | Switzerland | French and German and Italian and English | 4

내가 뭘 놓치고 있니?

다음은 또 다른 예입니다.

SELECT y, STRING_AGG(z, '+') AS STRING_AGG_PLUS, STRING_AGG(z, '-') AS STRING_AGG_MINUS
FROM (
    VALUES
        (1, 'a'),
        (1, 'b')
) x (y, z)
GROUP by y

  | y | STRING_AGG_PLUS | STRING_AGG_MINUS
--+---+-----------------+-----------------
1 | 1 | a+b             | a+b

SQL Server의 버그입니까?


예, 버그 (tm)입니다. SQL Server 2017 CU12까지 버전으로 제공됩니다 (단, Azure SQL Database의 @DanGuzman에 따르면, 이미 수정되었으므로 수정할 수 있습니다). 다음 CU). 특히 공통 하위 표현식 제거를 수행하는 옵티마이 STRING_AGG(x, <separator>) 필요 이상으로 표현식을 계산하지 않도록) STRING_AGG(x, <separator>) 형식의 모든 표현식이 x 일치하는 한 동일하게 간주됩니다 <separator> 는 쿼리의 첫 번째 계산 된 식과이를 통합합니다.

한 가지 해결 방법은 일종의 (근사) ID 변환을 수행하여 x 가 일치하지 않는지 확인하는 것입니다. 우리가 문자열을 다루기 때문에, 빈 문자열을 연결하면 :

SELECT y, STRING_AGG(z, '+') AS STRING_AGG_PLUS, STRING_AGG('' + z, '-') AS STRING_AGG_MINUS
FROM (
    VALUES
        (1, 'a'),
        (1, 'b')
) x (y, z)
GROUP by y

자신을 반복하지 마십시오 *. MAX(...) , LIST_AGG(...', ')LIST_AGG(...' and ') LIST_AGG(...', ') 를 사용하여 반복 LIST_AGG(...' and ') 있습니다. 이렇게 쿼리를 다시 작성하면 더 나은 계획을 세울 수 있습니다.

WITH cteCountryLanguageMapping AS (
    SELECT * FROM (
        VALUES
            ('Spain', 'English'),
            ('Spain', 'Spanish'),
            ('Sweden', 'English'),
            ('Switzerland', 'English'),
            ('Switzerland', 'French'),
            ('Switzerland', 'German'),
            ('Switzerland', 'Italian')
    ) x (Country, Language)
), results AS (
    SELECT
        Country,
        COUNT(Language) AS LanguageCount,
        STRING_AGG(Language, ', ') AS Languages
    FROM cteCountryLanguageMapping
    GROUP BY Country
)
SELECT Country, LanguageCount, CASE LanguageCount
    WHEN 2 THEN REPLACE(Languages, ', ', ' and ')
    ELSE Languages
END AS Languages_Fixed
FROM results

결과:

| Country     | LanguageCount | Languages_Fixed                  |
|-------------|---------------|----------------------------------|
| Spain       | 2             | Spanish and English              |
| Sweden      | 1             | English                          |
| Switzerland | 4             | French, German, Italian, English |

DB 피들

* 버그라고 말하는 것으로 다른 사람들도 반복하고 싶지 않았습니다.