​​​​​​​Oracle11 分析函数等高级函数使用说明

目录

1、Group by

2、Connect by

3、分析函数

4、Model

5、Listagg与自定义聚合函数

---

1、Group by

与GROUP BY 一起使用的关建字 GROUPING , ROLLUP,CUBE,结合这些特性的函数可以实现SQL的明细+分组统计

GROUPING 记录是对哪个字段进行统计
select  deptno,job,sum(sal),grouping(deptno),grouping(job) from emp  group by  rollup( deptno,job);

等价于

select deptno,job,sum(sal) from emp group by deptno,job

union all

select deptno,null,sum(sal) from emp group by deptno

union all

select null,null,sum(sal) from emp ;

关建字 CUBE

select  deptno,job,sum(sal),grouping (deptno),grouping(job) from emp  group by    cube( deptno,job);

 等价于

select deptno,job,sum(sal) from emp group by deptno,job

union all

select deptno,null,sum(sal) from emp group by deptno

union all

select null,null,sum(sal) from emp

union all

select null,job,sum(sal) from emp group by job

关建字 GROUP SETS  指定聚合方式

select  deptno,job,sum(sal),grouping (deptno),grouping(job) from emp  group by   grouping sets(deptno,job,null) ;

select  deptno,job,sum(sal),grouping (deptno),grouping(job) from emp  group by   grouping sets((deptno,job),null) ;

2、Connect by

CONNECT BY 语句特有的函数

SYS_CONNECT_BY_PATH   获取节点在整个树结构的路径

CONNECT_BY_ROOT,      获取节点规属的根节点

CONNECT_BY_ISLEAF,      判断该节点是否存在子节点

CONNECT_BY_ISCYCLE     该节点是否循环

LEVEL 伪列,用来指定节点在树结构里的层次

对 CONNECT BY 的结果集的每层进行排序

SELECT ……. FROM  TABLENAME

CONNECT BY PRIOR ID = F_ID

START WITH F_ID = 0

ORDER SIBLINGS BY ID DESC

无限记录的语句
select  level from dual connect by 1=1
显示20个记录结果集
select  level from dual connect by 1=1 and level<=20

显示一个树结构层次的
select  lpad(' ',(level-1)*10,' ')||ename,level from emp  connect by prior  empno=mgr  start with mgr is null

理解以下语句的执行
select  * from emp where empno=7369 connect by    empno=empno  ;

select * from emp  where empno=7369 connect by empno=empno and sys_guid() is not  null;

select * from emp  where empno=7369 connect by empno=empno and sys_guid() is    null;

select  * from emp where empno=7369 connect by prior   empno=empno;

select  * from emp where empno=7369 connect by prior   empno=empno and prior sys_guid() is null;

select  * from emp where empno=7369 connect by prior   empno=empno and   sys_guid() is null;

select  * from emp where empno=7369 connect by prior   empno=empno and prior sys_guid() is not null;

3、分析函数

关建字     分析函数 OVER (PARTITION BY …… ORDER BY ……)

PARTITION BY 分组

ORDER BY    排序

ROW_NUMBER ：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号,排行值相同,也是显示不间断流水号

RANK  ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置, 如果排序值相同,rank()值相同

DENSE_RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置, 与rank相似,只是不会跳号

SUM       ：该函数计算组中表达式的累积和

MIN       ：在一个组中的数据窗口中查找表达式的最小值

MAX       ：在一个组中的数据窗口中查找表达式的最大值

AVG        ：用于计算一个组和数据窗口内表达式的平均值。

COUNT     ：对一组内发生的事情进行累积计数

FIRST      ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行

LAST      ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

FIRST_VALUE ：返回组中数据窗口的第一个值

LAST_VALUE  ：返回组中数据窗口的最后一个值。

LAG         ：可以访问结果集中的其它行而不用进行自连接

LEAD         ：LEAD与LAG相反，LEAD可以访问组中当前行之后的行

例:

select empno,sal,

       row_number() over(order by sal) x,

       rank()       over(order by sal) y,

       dense_rank() over(order by sal) z

from emp where deptno=30;

select empno,ename,sal,deptno,

       row_number() over(partition by deptno order by sal) x,

       rank()       over(partition by deptno order by sal) y,

       dense_rank() over(partition by deptno order by sal) z

from emp;
 

执行以下SQL,看看查询后的结果集

1.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename) x

from emp;  

--注意PARTITION BY, ORDER BY

2.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename

                      rows between unbounded preceding and current row) x

from emp;  

--注意ROWS BETWEEN unbounded preceding AND current row  

--是指第一行至当前行的汇总 

3.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename

                      rows between current row and unbounded following) x

from emp;  

--注意ROWS BETWEEN current row AND unbounded following  

--是指当前行到最后一行的汇总 

4.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename

                      rows between 1 preceding and current row) x

from emp;  

--注意ROWS BETWEEN 1 preceding AND current row

--是指当前行的上一行(rownum-1)到当前行的汇总 

5.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename

                      rows between 1 preceding and 2 following) x

from emp;  

--注意ROWS BETWEEN 1 preceding AND 1 following

--是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

4、Model

这个函数一般不太常见,但确是非常有用的一个函数
具体的官方资料可以参考

noname.htm

语法定义如下

--MODEL:MODEL语句的关键字，必须。

--DIMENSION BY: DIMENSION维度的意思,必须。

--MEASURES:指定作为数组的列,可以定义出许多有规则的伪列

--RULES:对数组的各列进行各种操作规则的定义。

 

执行以下SQL,看看结果集,理解model 函数

with t as (select  deptno,to_char(emp.hiredate,'yyyy') year,sum(sal) sal from emp group by deptno,to_char(emp.hiredate,'yyyy'))

select deptno,year,sal,p_sal

from t

model

dimension by (deptno,year)

measures (sal,0 p_sal)

rules

(

p_sal[any,any]=sal[cv(),cv(year)-1]

);

select   ename,sales,xxx from emp

model

dimension by (ename)

measures (sal sales,0 xxx)

rules

(

xxx[any]=sum(sales)[ cv(ename)='CLARK' AND (ename)='CLARK']

);

select   ename,sales,xxx from emp

model

dimension by (ename)

measures (sal sales,0 xxx)

rules

(

xxx[any]= sales[ ename='SMITH']

);

select   ename,sales,xxx from emp

model

dimension by (ename)

measures (sal sales,0 xxx)

rules

(

xxx[any]= sum(sales)[ cv(ename)='SMITH']

);

select   ename,sales,xxx from emp

model

dimension by (ename)

measures (sal sales,0 xxx)

rules

(

xxx[any]= sum(sales)[ cv(ename)='SMITH']

);

///

比较这两个SQL的结果

with t as (select  deptno,to_char(emp.hiredate,'yyyy') year,sum(sal) sal from emp group by deptno,to_char(emp.hiredate,'yyyy'))

select deptno,year,sal,p_sal

from t

model

dimension by (deptno,year)

measures (sal,0 p_sal)

rules

(

p_sal[any,any]=sum(sal)[cv(),cv() is not null ]

);

with t as (select  deptno,to_char(emp.hiredate,'yyyy') year,sum(sal) sal from emp group by deptno,to_char(emp.hiredate,'yyyy'))

select deptno,year,sal,p_sal

from t

model

dimension by (deptno,year)

measures (sal,0 p_sal)

rules

(

p_sal[any,any]=sum(sal)[any,cv() is not null ]

);

5、Listagg与自定义聚合函数

Listagg 函数是11g以上才出现的,针对行转列的函数(10g不支持改函数)

listagg函数的语法结构如下：
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

listagg虽然是聚合函数，但可以提供分析功能（比如可选的OVER()子句）。使用listagg中，下列中的元素是必须的：1>需要聚合的列或者表达式  2>WITHIN GROUP 关键词 3>分组中的ORDER BY子句

select deptno, listagg(ename, ',') within group(order by null)

  from emp

 group by deptno;

另外介绍一个 相对listagg 这个函数  的行转列的函数 regexp_string

 select regexp_substr('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD',

                     '[^,]+',

                     1,

                     level)

  from dual

connect by regexp_count('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD', '[^,]+') >=

           level

在不支持regexp_count的数据库环境下,可以使用 length ,  replace 这两个函数来实现它的效果

针对 11g以下的数据库,不能使用 listagg这个函数,可以进行自定义聚合函数来替代此函数

oracle自定义聚集函数接口简介

   a. static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type) return number

   自定义聚集函数初始化设置,从这儿开始一个聚集函数

   b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2)  return number

    自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联

   c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT  varchar2,flags IN number)  return number

     用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.

    d. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number) return number

     终止聚集函数的处理,返回聚集函数处理的结果.

下面代码 是实现 listagg 函数效果的 自定义函数

实现效果如下图所示

实现代码如下:

create or replace type cux_listagg as object (

    --定义变量

    result_string varchar2(4000),

    --自定义聚集函数初始化设置,从这儿开始一个聚集函数

    static function ODCIAggregateInitialize(cs_ctx In Out cux_listagg) return number,

    --自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作

    --self 为当前聚集函数的指针,用来与前面的计算结果进行关联

    member function ODCIAggregateIterate(self In Out cux_listagg,value in varchar2) return number,

    -- 用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.

    member function ODCIAggregateMerge(self In Out cux_listagg,ctx2 In Out cux_listagg) return number,

    --终止聚集函数的处理,返回聚集函数处理的结果.

    member function ODCIAggregateTerminate(self In Out cux_listagg,returnValue Out varchar2,flags in number) return number

)

/

create or replace type body cux_listagg is

  --自定义聚集函数初始化设置,从这儿开始一个聚集函数

  static function ODCIAggregateInitialize(cs_ctx IN OUT cux_listagg) return number

  is

  begin

      cs_ctx := cux_listagg(null);

      return ODCIConst.Success;

  end;

    --自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作

    --self 为当前聚集函数的指针,用来与前面的计算结果进行关联

  member function ODCIAggregateIterate(self IN OUT cux_listagg,

                                       value IN varchar2 )

  return number

  is

  begin

      self.result_string := self.result_string || ','|| value;

      return ODCIConst.Success;

  end;

 --终止聚集函数的处理,返回聚集函数处理的结果.

  member function ODCIAggregateTerminate(self IN Out cux_listagg,

                                         returnValue OUT varchar2,

                                         flags IN number)

  return number

  is

  begin

      returnValue := ltrim(rtrim(self.result_string,','),',');

      return ODCIConst.Success;

  end;

-- 用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.

    

  member function ODCIAggregateMerge(self IN OUT cux_listagg,

                                     ctx2 IN Out cux_listagg)

  return number

  is

  begin

      self.result_string := self.result_string || ',' || ctx2.result_string;

      return ODCIConst.Success;

  end;

end;

/

再对这个type进行函数的创建

CREATE or replace

FUNCTION f_row_column(input varchar2 )

RETURN varchar2

PARALLEL_ENABLE AGGREGATE USING cux_listagg;

/