如何剔除STRING_AGG、LISTAGG字串彙總分析函數之重複值

先前文章曾探討兩大資料庫MSSQL及ORACLE已分別提供STRING_AGG、LISTAGG字串彙總分析函數，如下表所示，但兩者均未支援DISTINCT子句的使用，另外，分析函數是不允許互相嵌套（Nested），因此採用子查詢方式為目前較為合宜作法，字串彙總分析函數為彙總類函數，如子查詢中搭配DISTINCT使用，則恐造成彙總運算不正常問題。在此將利用ROW_NUMBER次序函數及NULL資料運算概念克服此問題。

	MSSQL 2017	ORACLE 11g R2
函數名稱	STRING_AGG	LISTAGG
分隔符號參數	必要參數	選擇參數。預設逗號
WITHIN GROUP關鍵字	選擇參數	必要參數
ORDER BY子句	選擇參數	必要參數
輸出型態	*與第一個參數相同 通常為8000 bytes	varchar2 4000 bytes
GROUP BY子句（彙總）	需搭配使用	需搭配使用
DISTINCT關鍵字	不支援	不支援
OVER子句	不支援	支援

以下範例中，每個部門中同仁以CSV字串串連呈現，但資料可能有多筆重覆，希望呈現結果如下

資料		希望呈現

將利用先前文章中所探討之函數特性達成此功能，SQL如下：

SELECT Dept

, STRING_AGG(CASE WHEN SEQ =1 THEN EmpName END, ',') WITHIN GROUP (ORDER BY EmpName) Emp_List

 --重點2: 保留第一筆資料，其他相值資料將轉為NULL

, COUNT(*)                  Row_Cnt

, COUNT(DISTINCT EmpName)   Ps_Cnt

FROM

    (

    SELECT Dept

         , EmpName

         , ROW_NUMBER() OVER(PARTITION BY Dept, EmpName

                             ORDER BY GETDATE()  --重點1:由於已無適當欄位可供排序，可用GETDATE()

                             ) SEQ

    FROM (VALUES('HR', 'Jane')

              , ('IT', 'Kevin')

              , ('MA', 'May')

              , ('MA', 'James')

              , ('IT', 'Kevin')

              , ('IT', 'Andy')

              , ('HR', 'Jane')

              , ('IT', 'Kevin')

          ) AS Emp(Dept, EmpName)

    ) A

GROUP BY Dept

SQL二項重點：

1.  取得第一筆（ROW_NUMBER次序函數）

此案例中，由於已無適當欄位可供排序，但排序對ROW_NUMBER次序函數中為必要項目，因此可用《如何在無任何適當ORDER BY欄位情況下，使用ROW_NUMBER函數》文章中所探討，MSSQL可使用GETDATE()函數，ORACLE則可使用NULL、SYSDATE等。

2.  保留第一筆資料

《NULL對運算上之影響》文章中所提大部分的彙總函數是針對非NULL資料進行彙總運算，將利用此特性，以CASE WHEN將第一筆資料保留其餘則轉換為NULL，以下兩種語法相同均可使用。

#	SQL
1	CASE WHEN SEQ =1 THEN EmpName END
2	CASE WHEN SEQ =1 THEN EmpName ELSE NULL END

ORACLE概念相同，SQL如下：

SELECT Dept

   , LISTAGG(CASE WHEN SEQ =1 THEN EmpName END, ',') WITHIN GROUP (ORDER BY EmpName) Emp_List

     --重點2: 保留第一筆資料，其他相值資料將轉為NULL

   , COUNT(*)                Row_Cnt

   , COUNT(DISTINCT EmpName) Ps_Cnt

FROM

    (

    SELECT Dept

         , EmpName

         , ROW_NUMBER() OVER(PARTITION BY Dept, EmpName

                             ORDER BY SYSDATE  --重點1:由於已無適當欄位可供排序，可用GETDATE()

                             ) SEQ

    FROM

        (

        SELECT 'HR' Dept , 'Jane' EmpName  FROM DUAL UNION ALL

        SELECT 'IT', 'Kevin'                FROM DUAL UNION ALL

        SELECT 'MA', 'May'                  FROM DUAL UNION ALL

        SELECT 'MA', 'James'                FROM DUAL UNION ALL

        SELECT 'IT', 'Kevin'                FROM DUAL UNION ALL

        SELECT 'IT', 'Andy'                 FROM DUAL UNION ALL

        SELECT 'HR', 'Jane'                 FROM DUAL UNION ALL

        SELECT 'IT', 'Kevin'                FROM DUAL

        )

    )

GROUP BY Dept