SQL QnA: [ORACLE] wmsys.wm_concat 字串彙總合併函數

2018年1月21日星期日

在此介紹10g 未公佈的指令wmsys.wm_concat，可於彙總運算中將同一群組之字串進行合併串連(CSV String)，類似MySQL所提供的GROUP_CONCAT函數。

也一併列出ORACLE 11g R2提供一組新的分析函數/彙總函數『LISTAGG』，同樣可用於彙總運算中使用。為分析重複資料對兩個指令影響及處理，因此額外造出重複資料。如下:

---測試資料

CREATE TABLE Calendar

SELECT TO_CHAR(DATE'2010-01-01' + (LEVEL-1), 'YYYY-MM-DD') DT

FROM DUAL

CONNECT BY DATE'2010-01-01' + (LEVEL-1) < DATE'2014-01-01'

UNION ALL --額外造出重複資料

SELECT TO_CHAR(DATE'2010-01-01' + (LEVEL-1), 'YYYY-MM-DD') DT

FROM DUAL

CONNECT BY DATE'2010-01-01' + (LEVEL-1) < DATE'2014-01-01';

SQL及執行結果如下。請注意由於wmsys.wm_concat輸出資料型態為CLOB，因此額外使dbms_lob.substr指令將CLOB轉換為VARCHAR2(4000)，也請特別注意ORACLE的VARCHAR2長度限制問題，而LISTAGG函數無剔除重複值之參數及設定，請注意使用。

--顥示結果

SELECT SUBSTR(DT, 1, 7) YrMs

, COUNT(*) RowCnt

, wmsys.wm_concat(DT) --輸出為CLOB

, dbms_lob.substr(wmsys.wm_concat(DT), 4000, 1) --未排序

, dbms_lob.substr(wmsys.wm_concat(distinct DT), 4000, 1) --使用DISTINCT即可排序

, LISTAGG(DT, ',') WITHIN GROUP(ORDER BY DT) --LISTAGG無法搭配DISTINCT

FROM Calendar

GROUP BY SUBSTR(DT, 1, 7)

由於wmsys.wm_concat命令除指定合併字串外，並無任何其他參數可設定，因此使用上非常簡便，且可直接搭配DISTINCT關鍵字即可剔除重複值，此二項為重要優點，但非為官方公開指令，因此無法確保未來是否繼續支援，另外，無法指定分隔符號及指定排序為其缺點，但可使用其他方法解決。

SQL QnA