ORACLE分析函数ppt课件

1、Oracle Analytic FunctionsIn PracticeBy 乙：相关列有索引吗？甲：owner有索引，选择性不错，我想用分析函数改写看看？乙：哦，知道了，这是典型的top-n查询。 SELECT owner,object_type FROM demo2 WHERE owner=DINGJUN123 AND trunc(created,dd) = (SELECT MAX(trunc(created,dd) FROM demo2 WHERE owner=DINGJUN123)SELECT owner,object_typeFROM (SELECT owner,object_typ

2、e,dense_rank() over(ORDER BY trunc(created,dd) DESC) rn FROM demo2 WHERE owner=DINGJUN123 ) WHERE rn=1分析函数作用欢迎进入今天的分析函数学习之旅！4子查询方法总行数：667827前往9行原始SQL : 逻辑读848，COST：1103优点：最容易想到缺陷：多次访问表或索引分析函数方法分析SQL : 逻辑读423，COST：693优点：减少表或索引的访问次数，逻辑读和COST 是常规方法的一半，SQL简单缺陷：需求排序操作进一步优化建立owner,trunc(created,dd) desc复合

3、索引作用对于原始SQL子查询可以快速扫描，分析函数消除排序优化后原始SQL逻辑读/COST：111/171,分析函数:6/767构建适宜索引，消除排序，是一种重要的SQL优化手段-| Id | Operation | Name | Rows |-| 0 | SELECT STATEMENT | | 202 |* 1 | TABLE ACCESS BY INDEX ROWID | DEMO2 | 202 |* 2 | INDEX RANGE SCAN | IDX_DEMO2 | 20237 | 3 | SORT AGGREGATE | | 1 | 4 | TABLE ACCESS BY INDE

4、X ROWID| DEMO2 | 20237 |* 5 | INDEX RANGE SCAN | IDX_DEMO2 | 20237 |Predicate Information (identified by operation id):- 1 - filter(TRUNC(INTERNAL_FUNCTION(CREATED),fmdd)= (SELECT MAX(TRUNC(INTERNAL_FUNCTION(CREATED),fmdd) FROM DEMO2 DEMO2 WHERE OWNER=DINGJUN123) 2 - access(OWNER=DINGJUN123) 5 - acc

5、ess(OWNER=DINGJUN123)-| Id | Operation | Name | Rows |-| 0 | SELECT STATEMENT | | 20237 |* 1 | VIEW | | 20237 |* 2 | WINDOW SORT PUSHED RANK | | 20237 | 3 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 20237 |* 4 | INDEX RANGE SCAN | IDX_DEMO2 | 20237 |-Predicate Information (identified by operation id):- 1

6、 - filter(RN=1) 2 - filter(DENSE_RANK() OVER ( ORDER BY TRUNC(INTERNAL_FUNCTION(CREATED),fmdd) DESC )=1) 4 - access(OWNER=DINGJUN123)分析函数作用分析函数的作用总结-| Id | Operation | Name | Rows |-| 0 | SELECT STATEMENT | | 213 | 1 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 213 |* 2 | INDEX RANGE SCAN | IDX_DEMO2 | 1

7、| 3 | SORT AGGREGATE | | 1 |* 4 | INDEX RANGE SCAN | IDX_DEMO2 | 21334 |-SELECT owner,object_type FROM demo2 WHERE owner=DINGJUN123 AND trunc(created,dd) = (SELECT MAX(trunc(created,dd) FROM demo2 WHERE owner=DINGJUN123)1.减少表或索引的访问次数SELECT owner,object_typeFROM (SELECT owner,object_type,dense_rank()

8、 over(ORDER BY trunc(created,dd) DESC) rn FROM demo2 WHERE owner=DINGJUN123 ) WHERE rn=1-| Id | Operation | Name | Rows |-| 0 | SELECT STATEMENT | | 21334 |* 1 | VIEW | | 21334 |* 2 | WINDOW NOSORT STOPKEY | | 21334 | 3 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 21334 |* 4 | INDEX RANGE SCAN | IDX_DEMO2

9、 | 21334 |-2.实现复杂的行间计算，复杂聚合等SELECT empno,sal,deptno,SUM(sal) over (PARTITION BY deptno ORDER BY empno) sum_currentFROM emp EMPNO SAL DEPTNO SUM_CURRENT- - - - 7782 2450 10 2450 7839 5000 10 7450 7934 1300 10 8750。很多分析函数要求排序SELECT a.ID,a.sal,a.ext FROM t1 a,(SELECT ID,MAX(sal) max_sal FROM t1 GROUP B

10、Y ID ) bWHERE a.sal=b.max_sal AND a.ID=b.IDSELECT ID,sal,extFROM (SELECT ID,sal,ext,rank() over(PARTITION BY ID ORDER BY sal DESC) rnFROM t1) WHERE rn=1- | SELECT STATEMENT | | 1 | 65 | | | HASH JOIN | | 1 | 65 | 35M| | VIEW | | 990K| 24M| | | HASH GROUP BY | | 990K| 24M| | | TABLE ACCESS FULL| T1 |

11、 990K| 24M| | | TABLE ACCESS FULL | T1 | 990K| 36M| |-Elapsed: 00:00:01.49- | SELECT STATEMENT | | 990K| 49M| | | VIEW | | 990K| 49M| | | WINDOW SORT PUSHED RANK| | 990K| 36M| 49M| | TABLE ACCESS FULL | T1 | 990K| 36M| |-Elapsed: 00:00:04.38CREATE TABLE t1ASSELECT mod(LEVEL,1000) ID,LEVEL+1000 sal,M

12、OD(LEVEL,10) extFROM dualCONNECT BY LEVEL1000000需求排序的分析函数，会耗费一定的资源，当然大多可以优化，对复杂的行间计算、累计值、挪动平均等还是首选分析函数ID很多反复，inline view分组减少衔接数量分析函数缺陷分析函数语法-图1分析函数语法-图2PARTITION BY ORDER BY ROWSVsRANGE AnalyticFunction UNBOUNDED PRECEDINGFOLLOWINGCURRENT ROW分析函数语法之关系文档留意点和限制分析函数语法之partition by 经过partition by子句，将一样的

13、行聚合到一同成为一组，之后当前行的分析函数计算就是在这行对应的partition里。每个分析函数都可以运用partition by子句。 每行在对应的窗口内，运用分析函数，然后计算得到当前行对应的分析函数值。 partition by子句可以没有，假设也没有order by子句，那么表示当前行对应的窗口范围是一切行。11223SELECT deptno,empno,sal,SUM(sal) over(PARTITION BY deptno) sum_dept,SUM(sal) over() sum_allFROM emp DEPTNO EMPNO SAL SUM_DEPT SUM_ALL- -

14、 - - - 10 7782 2450 8750 32025 10 7934 1300 8750 32025 10 7839 5000 8750 32025 20 7902 3000 10875 32025 20 7566 2975 10875 32025 20 7876 1100 10875 32025 20 7369 800 10875 32025 20 7788 3000 10875 32025分析函数语法之order by order by当前行默许窗口是当前行所属的partition第1行到当前行根据order by顺序指定，无order by就是对应所属partition一切行。

15、order by默许是range窗口，对应逻辑窗口，保证分析函数值的独一性，但是对排名分析函数特殊,由于排名函数不能带window。 order by假设有多个排序键且是range窗口，那么必需求求对应的窗口是当前partition一切行、第1行到当前行、当前行到当前partition最后一行或当前行到当前行123SELECT deptno, empno, sal, SUM(sal) over(PARTITION BY deptno ORDER BY sal) dept_current, SUM(sal) over(PARTITION BY deptno ORDER BY sal RANGE

16、BETWEEN unbounded preceding AND CURRENT ROW) dept_current1 FROM empDEPTNO EMPNO SAL DEPT_CURRENT DEPT_CURRENT1- - - - - 10 7934 1300 1300 1300 10 7782 2450 3750 3750 10 7839 5000 8750 8750 20 7369 800 800 800 20 7876 1100 1900 1900 20 7566 2975 4875 4875 20 7788 3000 10875 10875 20 7902 3000 10875 1

17、0875 30 7900 950 950 950 30 7654 1250 3450 3450 30 7521 1250 3450 3450 30 7844 1500 4950 4950 30 7499 1600 6550 6550 30 7698 2850 9400 9400逻辑当前行分析函数语法之windowWITH t AS(SELECT (CASE WHEN LEVEL IN (1,2) THEN 1 WHEN LEVEL IN (4,5) THEN 6 ELSE LEVEL END) ID FROM dual CONNECT BY LEVEL10)-默许排序,逻辑当前行SELECT

18、id,SUM(ID) over(ORDER BY ID) default_sum,-逻辑行上限到当前行，此当前行是逻辑当前行，和上面一样 SUM(ID) over(ORDER BY ID RANGE BETWEEN unbounded preceding AND CURRENT ROW) range_unbound_sum,-物理当前行，按排序后的行位置计算 SUM(ID) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) rows_unbound_sum,-下面两条与上面的比较，换成了对窗口有一定的限制，同样

19、分为-逻辑行和物理行 SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum, SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sumFROM t; 有显式window子句，必需有order by。有的分析函数不能有显式window,如 row_number,rank,dense_rank等 window可以指定是逻辑窗口还是物理窗口，逻辑行用range,物理行用rows window窗口滑动的方向应

20、该总是从上到下，特别留意range的计算方式和order by是升序还是降序有关。123 ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_SUM ROWS_SUM- - - - - - 1 2 2 1 5 5 1 2 2 2 5 11 3 5 5 5 3 16 6 23 23 11 33 21 6 23 23 17 33 25 6 23 23 23 33 27 7 30 30 30 42 30 8 38 38 38 24 24 9 47 47 47 17 1713语法综合之ROWS rows窗口是物理窗口，也就是排序后，按排序结果

21、的行号对应确定窗口，指定rows窗口的话，必需求求排序键独一，否那么结果能够不稳定。SELECT deptno, empno, sal, SUM(sal) over(PARTITION BY deptno ORDER BY sal ROWS BETWEEN unbounded preceding AND CURRENT ROW) dept_current FROM empDEPTNO EMPNO SAL DEPT_CURRENT- - - - 10 7934 1300 1300 10 7782 2450 3750 10 7839 5000 8750 20 7369 800 800 20 787

22、6 1100 1900 20 7566 2975 4875 20 7788 3000 7875 20 7902 3000 10875 rows窗口既然是物理窗口，那么它的order by是允许带多个排序键的。后面讲解range与rows不同。rows窗口滑动顺序总是向下。它的含义就是当前行之前xx行为起点，当前行之后xx行为终点其他类似。SELECT deptno, empno, sal, SUM(sal) over(PARTITION BY deptno ORDER BY sal DESC ROWS BETWEEN 1 preceding AND 2 preceding) sum_sal F

23、ROM empDEPTNO EMPNO SAL SUM_SAL- - - - 10 7839 5000 10 7782 2450 10 7934 1300 20 7788 3000 20 7902 3000 20 7566 2975 20 7876 1100 20 7369 800 30 7698 2850 30 7499 1600 30 7844 1500 30 7654 1250 30 7521 1250 30 7900 950终点起点换成1 follwing14语法综合之RANGE range窗口是默许窗口，它不论排序键能否是独一，都能保证诸如聚合分析函数值的独一排名分析函数？必需独一，

24、range窗口反响的是行与行之间的逻辑关系当前行的排序键的value加或减逻辑偏移量得到当前行对应的逻辑窗口的范围。 range+order by带多个排序键，必需求求对应的窗口是当前partition一切行、第1行到当前行、当前行到当前partition最后一行或当前行到当前行。(Why？)，升序range窗口preceding含义是比当前行小xx值，following含义是比当前行大xx值，降序range窗口preceding含义是比当前行大xx值，following含义是比当前行小xx值。SELECT department_id, employee_id, salary, SUM(sal

25、ary) over(PARTITION BY department_id ORDER BY salary RANGE BETWEEN 100 preceding AND 500 following) sum_salFROM hr.employees;DEPARTMENT_ID EMPLOYEE_ID SALARY SUM_SAL- - - - 10 200 4400 4400 20 202 6000 6000 20 201 13000 13000 30 119 2500 10800 30 118 2600 00 30 117 2800 8800 30 116 2900 8800 30 115

26、3100 3100 30 114 11000 11000换成rows含义变化15语法综合之RANGESELECT department_id, employee_id, salary, SUM(salary) over(PARTITION BY department_id ORDER BY salary DESC RANGE BETWEEN 100 preceding AND 500 following) sum_salFROM hr.employees;DEPARTMENT_ID EMPLOYEE_ID SALARY SUM_SAL- - - - 10 200 4400 4400 20 20

27、1 13000 13000 20 202 6000 6000 30 114 11000 11000 30 115 3100 11400 30 116 2900 10800 30 117 2800 10800 30 118 2600 5100 30 119 2500 5100 40 203 6500 6500 50 121 8200 24100 50 120 8000 15900 50 122 7900 15900 50 123 6500 6500 50 124 5800 5800 由于salary desc,所以100 preceding含义是比当前行的salary大100，以employee

28、_id=117为例，也就是起点是2800+100=2900,终点是比当前行小500，2800-500=2300。那么employee_id=117行分析函数计算的窗口范围是employee_id between 116 and 119。16语法综合之窗口范围 当前行对应的分析函数结果是根据当前行对应的窗口逻辑OR物理范围计算的。了解窗口的含义对分析函数的运用至关重要。窗口只需起点的，起点必需是CURRENT ROW之前包含CURRENT ROW。窗口内数据挪动顺序必需是按照排序顺序，从上到下，否那么要么报错，要么结果有问题。示例说明ROWS/RANGE UNBOUNDED PRECEDING终

29、点CURRENT ROW，相当于BETWEEN 起点 BTEWEEN CURRENT ROWROWS/RANGE CURRENT ROW相当于BETWEEN CURRENT ROW AND CURRENT ROWROWS/RANGE value_expr PRECEDING相当于BETWEEN value_expr PRECEDING AND CURRENT ROWvalue_expr FOLLOWING报错,无效窗口表1-只指定起点的表2-有起点和终点示例说明ROWS/RANGE BETWEEN UNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING和无ORDE

30、R BY一样，等价于没有写ORDER By和WINDOW子句ROWS/RANGE BETWEEN CURRENT ROW AND value_expr FOLLOWING当前行到对应value_exp偏移量的行ROWS/RANGE BETWEEN value_expr FOLLOWING AND value_expr1 FOLLOWING 起点是FOLLOWING,终点必须是FOLLOWINGROWS/RANGE BETWEEN value_expr PRECEDING AND value_expr1 FOLLOWING 终点是PRECEDING,起点必须是PRECEDINGROWS/RANG

31、E BETWEEN CURRENT ROW AND value_expr PRECEDING报错,无效窗口17分析函数分类分类内容说明使用频率排名分析函数ROW_NUMBER、RANK、DENSE_RANK、FIRST、LAST、LAST_VALUE,FIRST_VALUEFIRST,LAST不应该算做函数，它主要与聚合函数联合使用强聚合分析函数AVG、COUNT、MAX,MIN,SUM强行比较分析函数LEAD、LAG解决当前行与前后行之间的关系中统计分析函数RATIO_TO_REPORT解决报表占比问题中行连接分析函数LISTAGG11g新特性中其他分析函数CORR、CORVAR_POP、C

32、ORVAR_SAMP、CUME_DIST、NTH_VALUE、NTILE、PERCENT_RANK、PERCENTILE_CONT、PERCENTILE_DISC、REGR_ (Linear Regression) Functions、STDDEV、STDDEV_POP、STDDEV_SAMP、VAR_POP,VAR_SAMP、VARIANCE弱 按照各种分析函数运用频率以及业务特点，将分析函数分为排名分析函数、聚合分析函数、行比较分析函数、统计分析函数、行衔接分析函数、其他分析函数根本都是数学统计函数，很少运用。很多分析函数，同时也可以作为普通的组函数运用，有的分析函数，比如FIRST,LA

33、ST、LISTAGG，很多时候，主要用它的普通分组函数功能，而不是它的分析函数功能。排名分析函数row_number,dense_rank,rank 排名分析函数有row_number,dense_rank,rank,first,last,first_value,last_value等，其中row_number,dense_rank,rank,first,last都需求order by,first_value和last_value可以不指定。SELECT empno,sal,deptno,row_number() over(PARTITION BY deptno ORDER BY sal) r

34、ow_rn ,rank() over(PARTITION BY deptno ORDER BY sal) rank_rn,dense_rank() over(PARTITION BY deptno ORDER BY sal) dense_rnFROM emp EMPNO SAL DEPTNO ROW_RN RANK_RN DENSE_RN- - - - - - 7934 1300 10 1 1 1 7782 2450 10 2 2 2 7839 5000 10 3 3 3 7369 800 20 1 1 1 7876 1100 20 2 2 2 7566 2975 20 3 3 3 7902

35、3000 20 4 4 4 7788 3000 20 5 4 4 7900 950 30 1 1 1 7521 1250 30 2 2 2 7654 1250 30 3 2 2 7844 1500 30 4 4 3 7845 1500 30 5 4 3 7846 1500 30 6 4 3 7499 1600 30 7 7 4 7698 2850 30 8 8 519排名分析函数row_number,dense_rank,rank新特性 在9i以及之前的版本中运用row_number做分页，会有一定的问题，它没有做到谓词推进，10g之后曾经做了优化。SELECT empno,ename,job

36、,mgr,deptno FROM (SELECT empno,ename,job,mgr,deptno FROM scott.emp ORDER BY empno) WHERE ROWNUM10;-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 9 | 468 | 2 (0)| 00:00:01 |* 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 9 | 468 | 2 (0)| 00:00:01 | 3 | TABLE ACC

37、ESS BY INDEX ROWID| EMP | 12 | 300 | 2 (0)| 00:00:01 | 4 | INDEX FULL SCAN | PK_EMP | 9 | | 1 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 1 - filter(ROWNUM10)Statistics- 1 recursive calls 0 db block gets 4 consistent getsSELECT empno,ename,job,mgr,deptno FROM (SELECT a.*,row

38、_number() over(ORDER BY empno) rn FROM scott.emp a) WHERE rn10;-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 12 | 780 | 2 (0)| 00:00:01 |* 1 | VIEW | | 12 | 780 | 2 (0)| 00:00:01 |* 2 | WINDOW NOSORT STOPKEY | | 12 | 300 | 2 (0)| 00:00:01 | 3 | TABLE ACCESS

39、 BY INDEX ROWID| EMP | 12 | 300 | 2 (0)| 00:00:01 | 4 | INDEX FULL SCAN | PK_EMP | 12 | | 1 (0)| 00:00:01 |-Predicate Information (identified by operation id):- 1 - filter(RN10) 2 - filter(ROW_NUMBER() OVER ( ORDER BY EMPNO) break ON department_idSQL SELECT department_id,first_name 2 FROM hr.employe

40、es 3 WHERE department_id40 4 ORDER BY 1,2;DEPARTMENT_ID FIRST_NAME- - 10 Jennifer 20 Michael Pat 30 Alexander Den Guy Karen Shelli SigalSELECT decode(lag(department_id,1) over(PARTITION BY department_id ORDER BY first_name), department_id,NULL,department_id ) newdepartment_id, first_name FROM hr.emp

41、loyeesWHERE department_id40ORDER BY department_id,first_name反复行问题DELETE FROM duprows aWHERE a.ROWID (SELECT MIN(b.ROWID)FROM duprows bWHERE a.ext=b.ext)DELETE FROM duprows aWHERE a.ROWID IN(SELECT ROWID FROM (SELECT row_number() over(PARTITION BY b.ext ORDER BY b.ROWID) rnFROM duprows b) cWHERE c.rn

42、1)-DELETE STATEMENT | | 2 DELETE | DUPROWS | HASH JOIN | | 2 VIEW | VW_SQ_1 | 3 SORT GROUP BY | | 3 TABLE ACCESS FULL| DUPROWS | 3 TABLE ACCESS FULL | DUPROWS | 3-DELETE STATEMENT | | 1 DELETE | DUPROWS | NESTED LOOPS | | 1 VIEW | VW_NSO_1 | 3 SORT UNIQUE | | 1 VIEW | | 3 WINDOW SORT | | 3 TABLE ACC

43、ESS FULL | DUPROWS | 3 TABLE ACCESS BY USER ROWID| DUPROWS | 1-33行列转换问题1SQL select * from tab;A B- -A 1A 2A 3A 4A 5A 6A 7SQL set null nullSQL SELECT a,b,LEAD(b) OVER(PARTITION BY a ORDER BY b) c, 2 LEAD(b,2) OVER(PARTITION BY a ORDER BY b) d, 3 LEAD(b,3) OVER(PARTITION BY a ORDER BY b) e, 4 LEAD(b,4

44、) OVER(PARTITION BY a ORDER BY b) f 5 FROM tab;A B C D E F- - - - - -A 1 2 3 4 5A 2 3 4 5 6A 3 4 5 6 7A 4 5 6 7 nullA 5 6 7 null nullA 6 7 null null nullA 7 null null null null行列转换问题2SQL SELECT INDEX_NAME, COLUMN_NAME 2 FROM user_ind_columns 3 WHERE INDEX_NAME LIKE %PK 4 AND rownum 10; INDEX_NAME CO

45、LUMN_NAME- -ALL_ORDERS_PK YEARALL_ORDERS_PK MONTHALL_ORDERS_PK CUST_NBRALL_ORDERS_PK REGION_IDALL_ORDERS_PK SALESPERSON_IDASSEMBLY_PK ASSEMBLY_TYPEASSEMBLY_PK ASSEMBLY_IDA_ID_PK IDCUSTOMER_PK CUST_NBRselect INDEX_NAME, max(decode(rn, 1, COLUMN_NAME) c1, max(decode(rn, 2, COLUMN_NAME) c2, max(decode(

46、rn, 3, COLUMN_NAME) c3, max(decode(rn, 4, COLUMN_NAME) c4, max(decode(rn, 4, COLUMN_NAME) c5 from (select INDEX_NAME, TABLE_NAME, COLUMN_NAME, row_number() over(partition by INDEX_NAME order by COLUMN_NAME) rn from user_ind_columns where INDEX_NAME like %PK and rownum select * from demo7_1; ID AMOUN

47、T- - 1 100 2 50已用时间: 00: 00: 00.01SQL select * from demo7_2; ID PERSONS- - 1 3 2 2 平均分派问题，如何将金额平均分摊，并且小数也分摊掉，防止误差。SELECT ID, persons,(CASE WHEN rn = (amount - amount2) * 100 THEN 0.01 ELSE 0 END) + je AS je,amount -然后排序，与总金额有差额的补0.01FROM (SELECT t.*, SUM(je) OVER(PARTITION BY id) AS amount2, ROW_NUM

48、BER() OVER(PARTITION BY id ORDER BY je DESC) rn FROM ( -先展开记录数，用trunc先平均,只舍不入 SELECT tt.* FROM (SELECT t2.id, t2.persons, TRUNC(t1.amount /t2.persons, 2) je, t1.amount amount FROM demo7_1 t1, demo7_2 t2 WHERE t1.id = t2.id ) tt, -构造最大的人数序列 (SELECT LEVEL rn FROM dual CONNECT BY LEVEL = tm.rn) t ) ID

49、PERSONS JE AMOUNT- - - - 1 3 33.34 100 1 3 33.33 100 1 3 33.33 100 2 2 25 50 2 2 25 5036自定义分析函数 自定义分析函数就是需求实现ORACLE规定的对象的接口方法，然后自定义函数运用这个对象即可(红色为必需实现的，其他可选)。前往值： ODCIConst.Success胜利， ODCIConst.Error失败。接口说明ODCIAggregateDelete()从当前组中删除输入值ODCIAggregateInitialize()初始化对象的aggregation context和实例，并将此context

50、作为OUT参数返回ODCIAggregateIterate()核心逻辑，通过外部输入的行，迭代处理对应的值，并返回给context,忽略传入的值是NULL的ODCIAggregateMerge()合并两个context给单个对象实例ODCIAggregateTerminate()聚合计算，并做必要的清理工作，比如释放内存ODCIAggregateWrapContext()Integrates all external pieces of the current aggregation context to make the context self-contained37自定义分析函数-1.定义对象CREATE OR REPLACE TYPE TYPE_MULTI_AGG AS OBJECT ( results NUMBER, STATIC FUNCTION ODCIAGGREGATEINITIALIZE(sctx IN OUT TYPE_MULTI_AGG) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN