User-defined Aggregate Function in Oracle

Author    Book                     Author    Books
--------+--------                     --------+--------
poly    |   A                          poly   |   A,B,C
poly    |   B                          amy    |   D,E
poly    |   C         ====>
amy     |   D
amy     |   E

 

HOW?

select author, to_list(book) as books
  from table_name
group by author;

 

SO SIMPLE? 

Yes, we just need to create a user-defined aggregate function as follows:

 

1. create type object

create or replace 
type tolist as object 
( 
  list varchar2(2000), 

  static function ODCIAggregateInitialize 
  ( actx in out tolist 
  ) return number, 

  member function ODCIAggregateIterate 
  ( self  in out tolist , 
    value in varchar2 
  ) return number, 

  member function ODCIAggregateTerminate 
  ( self in tolist , 
    returnValue  out varchar2, 
    flags in varchar2 
  ) return number, 

  member function ODCIAggregateMerge 
  (self in out tolist , 
   ctx2 in     tolist  
  ) return number 
) 

 

 2. create type body

create or replace 
type body tolist is 

static function ODCIAggregateInitialize 
  ( actx in out tolist
  ) return number is 
  begin
    actx := tolist('');
    return ODCIConst.Success; 
  end; 


member function ODCIAggregateIterate 
  ( self  in out tolist, 
    value in varchar2
  ) return number is 
  begin 
        if self.list is null then self.list := value;
        else
           self.list := self.list || ',' || value; 
        end if;     
     return ODCIConst.Success; 
  end; 


 member function ODCIAggregateTerminate 
  ( self in tolist, 
    returnValue out varchar2, 
    flags in varchar2 
  ) return number is 
  begin 
   returnValue:= self.list; 
   return ODCIConst.Success; 
  end; 


  member function ODCIAggregateMerge 
  (self in out tolist, 
   ctx2 in     tolist 
  ) return number is 
  begin 
    if ctx2.list <> '' then 
       self.list := self.list || ',' || ctx2.list; 
    end if; 
    return ODCIConst.Success; 
  end; 

end; 

 

3. create function

CREATE OR REPLACE FUNCTION to_list
( x varchar2
) RETURN varchar2
PARALLEL_ENABLE
AGGREGATE USING tolist;

 

done

 

---------------------------------------------

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1005029

http://www.oracle.com/technology/oramag/oracle/06-jul/o46sql.html

 

 

==========================

Supplement

 

"Displaying multiple records in one row" give a summary :

1. SELECT wmsys.wm_concat(dname) departments FROM dept;  (10g)

2. A way to get around the restriction that user-defined aggregates may only have one argument, which allows you to specify an alternative separator character.

3. A really delicate way using just sql

SELECT deptno
     , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
FROM   ( SELECT deptno
              , ename
              , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
              , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
         FROM   emp )
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

4. Another approach involves harnessing the dark power of XML

SELECT deptno
     , RTRIM
       ( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()')
       , ',' ) AS concatenated
FROM   emp
GROUP BY deptno;

 

Oracle10g 数据的汇总/分析计算问题。Aggregate, Analytic Function/ Windowing Clause/ User-defined Function

05-20

对一定时间段内的数据进行Percentile计算(用PERCENTILE_CONT()).rnrn表1:数据类别的计算参数rnCAT_ID MIN_RESULTS_IN_SET MAX_RESULTS_IN_SET SET_RESULTS_PERIOD(DAYS)rn===================================================================================rnSA001 3 4 6rnSE023 2 3 5rnSC231 4 4 4rn...rnrn表2:数据 (日期为 日/月/年) 假设需要进行1/1/01到23/1/01的计算rnCAT_ID RESULT_DATE RESULT |||| 对应此行需要的输出结果rn========================================rn...rnSA001 1/1/01 89 -->无。因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为1,小于(MIN_RESULTS_IN_SET = 3)rnrnSA001 2/1/01 12 -->无。因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为2,小于(MIN_RESULTS_IN_SET = 3)rnrnSA001 3/1/01 32 -->因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为3 == MIN_RESULTS_IN_SET,所以rn输出3/1/01的计算结果--PERCENTILE_CONT(0.5)这个计算是基于1/1/01,2/1/01和3/1/01这3条数据的。rnrnSA001 4/1/01 22 -->因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为4 > MIN_RESULTS_IN_SET,=MAX_RESULTS_IN_SET,所以rn输出4/1/01的计算结果--PERCENTILE_CONT(0.5)这个计算是基于1/1/01,2/1/01,3/1/01和4/1/01这4条数据的。rnrnSA001 6/1/01 42 -->因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为6(1,2,3,4,6/1/01) > MIN_RESULTS_IN_SET,同时>MAX_RESULTS_IN_SET,只取前4条(MAX_RESULTS_IN_SET)数据。所以rn输出6/1/01的计算结果--PERCENTILE_CONT(0.5)这个计算是基于1/1/01,2/1/01,3/1/01,4/1/01这4条数据的。rnrnSA001 9/1/01 42 -->因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为3(4,6,9/1/01) == MIN_RESULTS_IN_SET,无。因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为2(9,13/1/01),小于(MIN_RESULTS_IN_SET = 3)rnrnSA001 15/1/01 38 -->无。因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为2(13,15/1/01),小于(MIN_RESULTS_IN_SET = 3)rnSA001 22/1/01 13 -->无。因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为1(22/1/01),小于(MIN_RESULTS_IN_SET = 3)rnSA001 23/1/01 86 -->无。因为此日(含)及之前6日(SET_RESULT_PERIOD)的数据数目为2(22,23/1/01),小于(MIN_RESULTS_IN_SET = 3)rn...rnrnrn所以最后有如下的计算结果:rnSA001 3/1/01 xxxrnSA001 4/1/01 xxxrnSA001 6/1/01 xxxrnSA001 9/1/01 xxxrnrnrn针对以上的需求,似乎用简单的SQL难以解决。我试了一些Aggregate Function/Analytic Function:rnrn*** select result_dt, result, count(*) OVER(order by result_dt range 4 preceding) CNT from testrnrn*** SELECT DISCHARGE_POINT_ID, PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY RESULT) PERCENTILE_80 FROM PSD_RESULT WHERE RESULT_DT>'03/JUL/03' AND RESULT_DT<'03/SEP/03' GROUP BY DISCHARGE_POINT_IDrnrn因为PERCENTILE_CONT()本身不支持WINDOWING_CLAUSE,所以要实现上述需求有些困难。rnrn我是业余新手,对Oracle了解不多,希望哪位能够指点一下。我是应该做一个函数(User-defined Function),还是组合几个SQL查询。。。rnrn任何可能的方向,都请给些提示。rnrn谢谢!rn100pts.

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试