STRING_AGG에서 DISTINCT 값 생성

STRING_AGG에서 DISTINCT 값 생성

SQL Server 2017에서 STRING_AGG 함수를 사용하고 있습니다.와 같은 효과를 내고 싶습니다.COUNT(DISTINCT <column>).나는 노력했다.STRING_AGG(DISTINCT <column>,',')하지만 그것은 법적 구문이 아닙니다.

T-SQL 해결 방법이 있는지 알고 싶습니다.여기 제 샘플이 있습니다.

WITH Sitings 
  AS
  (
    SELECT * FROM (VALUES 
      (1, 'Florida', 'Orlando', 'bird'),
      (2, 'Florida', 'Orlando', 'dog'),
      (3, 'Arizona', 'Phoenix', 'bird'),
      (4, 'Arizona', 'Phoenix', 'dog'),
      (5, 'Arizona', 'Phoenix', 'bird'),
      (6, 'Arizona', 'Phoenix', 'bird'),
      (7, 'Arizona', 'Phoenix', 'bird'),
      (8, 'Arizona', 'Flagstaff', 'dog')
    ) F (ID, State, City, Siting)
  ) 
SELECT State, City, COUNT(DISTINCT Siting) [# Of Types], STRING_AGG(Siting,',') Animals
FROM Sitings 
GROUP BY State, City

위의 결과는 다음과 같습니다.

+---------+-----------+--------------+-------------------------+
|  State  |   City    | # Of Types   |         Animals         |
+---------+-----------+--------------+-------------------------+
| Arizona | Flagstaff |            1 | dog                     |
| Florida | Orlando   |            2 | dog,bird                |
| Arizona | Phoenix   |            2 | bird,bird,bird,dog,bird |
+---------+-----------+--------------+-------------------------+

출력은 정확히 제가 원하는 것입니다. 단, 피닉스 애리조나에 대해 나열된 연결된 "동물"이 다음과 같이 고유하기를 바랍니다.

+---------+-----------+--------------+--------------------+
|  State  |   City    | # Of Types   |      Animals       |
+---------+-----------+--------------+--------------------+
| Arizona | Flagstaff |            1 | dog                |
| Florida | Orlando   |            2 | dog,bird           |
| Arizona | Phoenix   |            2 | bird,dog           |
+---------+-----------+--------------+--------------------+

아이디어 있어요?

훨씬 더 큰 실제 데이터 세트를 사용하면 "동물" 열이 8,000자를 초과한다는 오류가 발생합니다.

제가 생각하는 제 질문은 이 질문과 동일하지만, 제 예는 훨씬 더 단순합니다.

여기에 그것을 하는 한 가지 방법이 있습니다.

개별 카운트도 원하기 때문에 행을 두 번 그룹화하면 됩니다.첫번째GROUP BY중복 항목을 제거할 것이고, 두 번째 항목은GROUP BY최종 결과를 생성합니다.

WITH
Sitings
AS
(
    SELECT * FROM (VALUES 
    (1, 'Florida', 'Orlando', 'bird'),
    (2, 'Florida', 'Orlando', 'dog'),
    (3, 'Arizona', 'Phoenix', 'bird'),
    (4, 'Arizona', 'Phoenix', 'dog'),
    (5, 'Arizona', 'Phoenix', 'bird'),
    (6, 'Arizona', 'Phoenix', 'bird'),
    (7, 'Arizona', 'Phoenix', 'bird'),
    (8, 'Arizona', 'Flagstaff', 'dog')
    ) F (ID, State, City, Siting)
)
,CTE_Animals
AS
(
    SELECT
        State, City, Siting
    FROM Sitings
    GROUP BY State, City, Siting
)
SELECT
    State, City, COUNT(1) AS [# Of Sitings], STRING_AGG(Siting,',') AS Animals
FROM CTE_Animals
GROUP BY State, City
ORDER BY
    State
    ,City
;

결과

+---------+-----------+--------------+----------+
|  State  |   City    | # Of Sitings | Animals  |
+---------+-----------+--------------+----------+
| Arizona | Flagstaff |            1 | dog      |
| Arizona | Phoenix   |            2 | bird,dog |
| Florida | Orlando   |            2 | bird,dog |
+---------+-----------+--------------+----------+

---

8,000자를 초과한다는 오류 메시지가 계속 표시되면 다음에 값을 캐스팅합니다.varchar(max)전에STRING_AGG.

비슷한 것

STRING_AGG(CAST(Siting AS varchar(max)),',') AS Animals

한 가지 더 방법이 있습니다(sql fiddle).

  WITH Sitings 
  AS
  (
    SELECT * FROM (VALUES 
      (1, 'Florida', 'Orlando', 'bird'),
      (2, 'Florida', 'Orlando', 'dog'),
      (3, 'Arizona', 'Phoenix', 'bird'),
      (4, 'Arizona', 'Phoenix', 'dog'),
      (5, 'Arizona', 'Phoenix', 'bird'),
      (6, 'Arizona', 'Phoenix', 'bird'),
      (7, 'Arizona', 'Phoenix', 'bird'),
      (8, 'Arizona', 'Flagstaff', 'dog')
    ) F (ID, State, City, Siting)
  ) 

select State,City,count(*) as [# Of Sitings],(select string_agg(value,', ') from (select distinct value from string_split(string_agg(Siting, ','),',')) t) AS Animals
FROM Sitings 
GROUP BY State, City

분할 및 병합 부분을 재사용 가능한 스칼라 값 함수로 쉽게 변환할 수 있습니다.

메모

이것은 최적의 솔루션이 아닙니다. 먼저 그룹화한 다음 집계(위의 답변과 같이)를 수행하는 것이 좋습니다.또한, 그것은 얻지 못합니다.# of Types그것은# of Sitings대신.그러나 빠른 인라인 기능으로 편리해집니다.

그냥 쓰기sub-query

WITH Sitings 
      AS
      (
        SELECT * FROM (VALUES 
          (1, 'Florida', 'Orlando', 'bird'),
          (2, 'Florida', 'Orlando', 'dog'),
          (3, 'Arizona', 'Phoenix', 'bird'),
          (4, 'Arizona', 'Phoenix', 'dog'),
          (5, 'Arizona', 'Phoenix', 'bird'),
          (6, 'Arizona', 'Phoenix', 'bird'),
          (7, 'Arizona', 'Phoenix', 'bird'),
          (8, 'Arizona', 'Flagstaff', 'dog')
        ) F (ID, State, City, Siting)
      ) 

    select State,City,count(*) as [# Of Types],STRING_AGG(Siting,',') AS Animals from 
    (
      SELECT State, City, Siting
    FROM Sitings 
    GROUP BY State, City,Siting
    ) as T  group by State,City

http://sqlfiddle.com/ #!18/ba4b8/11

  State     City    # Of Types  Animals
Arizona     Flagstaff   1   dog
Florida     Orlando     2   bird,dog
Arizona     Phoenix     2   bird,dog

물론 매우 늦은 답장입니다.

이것은 다른 방법입니다.더 시팅 인STRING_AGG(Siting,',')그룹화된 키가 주 및 시와 일치하는 고유한 SITING 목록을 반환하도록 조회할 수 있습니다.

WITH Sitings 
      AS
      (
        SELECT * FROM (VALUES 
          (1, 'Florida', 'Orlando', 'bird'),
          (2, 'Florida', 'Orlando', 'dog'),
          (3, 'Arizona', 'Phoenix', 'bird'),
          (4, 'Arizona', 'Phoenix', 'dog'),
          (5, 'Arizona', 'Phoenix', 'bird'),
          (6, 'Arizona', 'Phoenix', 'bird'),
          (7, 'Arizona', 'Phoenix', 'bird'),
          (8, 'Arizona', 'Flagstaff', 'dog')
        ) F (ID, State, City, Siting)
      ) 

SELECT 
    S.State, 
    S.City, 
    COUNT(DISTINCT S.Siting) AS [# Of Types],

    --STRING_AGG(S.Siting,',') AS Animals
    (
        SELECT STRING_AGG(U.SITING, ',')
        FROM 
        (
            SELECT DISTINCT T.Siting
            FROM Sitings AS T
            WHERE 
                T.State = S.State AND
                T.City = S.City
        ) AS U
    ) AS ANIMAL

FROM 
    Sitings AS S
GROUP BY 
    S.State, 
    S.City
ORDER BY
    S.State, 
    S.City

당신은 이것을 우편물에 사용할 수 있습니다.mysql에 대해서는 잘 모르겠습니다.하지만 이것은 우체국에서 작동합니다.

select state, city, string_agg(distinct (siting), ', ') from sitings group by state, city;

이렇게 하면 고유한 값만 집계됩니다.

언급URL : https://stackoverflow.com/questions/51646385/produce-distinct-values-in-string-agg