ORACLE 11g R2提供一組新的分析函數/彙總函數,可將同一群組內之字串串連之函數,類似MySQL所提供的GROUP_CONCAT函數功能之『LISTAGG』,於彙總運算中使用,如依照部門將其所屬員工姓名串連起來。LISTAGG函數語法如下:
LISTAGG(expr [, 'delimiter'])
WITHIN GROUP (order_by_clause) [OVER
partition_by_clause]
|
expr:用於字串連結之欄位(Column)或運算(expression)(必要參數)。
delimiter:字串連結之分隔符號(選擇參數)。
order_by_clause:字串連結之順序(必要參數)。
使用時語法/應用時請注意以下幾點:
ü
必須於彙總運算中
否則將出現ORA-00937:不是單一群組的群組函數。不論是single-set aggregate或group-set aggregate形式均可。
ü
須有WITH GROUP關鍵字
ü
須有組內ORDEER BY子句
將建立2010-01-01~2013-12-31日期資料為測試資料,SQL如下:
CREATE TABLE Calendar
AS
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及結果如下所示。就以[年月]群組可順利執行,資料為該月份之01~31日期字串;[年]的部分,由於是將整年度自01-01至12-31日期之日期連結,字串長度過長將出現ORA-01489之錯誤,可改用xmlagg函數,但傳回結果型態為CLOB。
xmlagg函數應用
以下為應用xmlagg函數之SQL及結果如下:
SELECT SUBSTR(DT, 1, 4) Yr
, TRIM(TRAILING ',' FROM
xmlserialize(content xmlagg(xmlelement(c, DT || ',') ORDER BY DT).extract('//text()') as clob)
) as DT_LIST
FROM Calendar
GROUP BY
SUBSTR(DT, 1, 4)
執行結果:
CLOB結果:
請特別注意xmlagg函數使用時,需額外針對日期進行排序,否則日期排列非如預期。
對於群組字串連結,ORACLE 11g R2可用LISTAGG函數、10g則可使用未公佈的指令wmsys.wm_concat,而9i以上可以用User-Defined
Aggreagte Function或使用xmlagg函數,xmlagg之運算結果為CLOB型態,可使用TO_CHAR轉換為varchar2,即可提供一般所使用,如下。
SELECT SUBSTR(DT, 1, 7) YrMS
, TO_CHAR(TRIM(TRAILING ',' FROM
xmlserialize(content xmlagg(xmlelement(c, DT || ',') ORDER BY DT).extract('//text()') as clob))
) as DT_LIST
FROM Calendar
GROUP BY SUBSTR(DT, 1, 7)
沒有留言:
張貼留言