2013年11月11日 星期一

[ORACLE] LISTAGG函數之應用

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 aggregategroup-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-0112-31日期之日期連結,字串長度過長將出現ORA-01489之錯誤,可改用xmlagg函數,但傳回結果型態為CLOB

SQL
執行結果
SELECT SUBSTR(DT, 1, 7) YrMs
  , LISTAGG(DT, ',')
      WITHIN GROUP (ORDER BY DT) AS DT_LIST
FROM   Calendar
GROUP BY SUBSTR(DT, 1, 7)

SELECT SUBSTR(DT, 1, 4) Yr
  , LISTAGG(DT, ',')
       WITHIN GROUP (ORDER BY DT) AS DT_LIST
FROM   Calendar
GROUP BY SUBSTR(DT, 1, 4)
ORA-01489: result of string concatenation is too long

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)

沒有留言:

張貼留言