Oracle SQL 一些函数用法以下sql环境都是在 oracle 11g/scott完成 Group by与GROUP BY一起使用的关建字 GROUPING,GROUP SET,ROLLUP,CUBE结合这些特性的函数可以实现SQL的明细+分组统计 GROUPINGGROUPING 记录是对哪个字段进行统计,其值只能是 0 & 1 Group(column) =0 表示此字段参与了分组统计 =1表示字段未参与分组统计 select deptno,job,sum(sal),grouping(deptno),grouping(job) from emp groupby ( deptno,job); 执行结果如下 ROLLUPselect deptno,job,sum(sal),grouping(deptno),grouping(job) from emp groupby rollup( deptno,job); 等价于 selectnull,null,sum(sal)from emp unionall select deptno,null,sum(sal)from empgroupby deptno unionall select deptno,job,sum(sal)from empgroupby deptno,job 首先是进行无字段的聚合,然后在对字段进行从左到右依次组合后聚合 从上面的结果可以看到 rollup( deptno,job) = group by deptno, job + group by deptno + group by null 另外 rollup (deptno,job)<>rollup(job,deptno)两者的结果集是不同的,可以把SQL修改后,执行下看看 提示:在对rollup的分组进行组合使用 select deptno,job,sum(sal),grouping (deptno),grouping(job) from emp groupby rollup ((deptno,job),null) ; 此时 rollup对(deptno,job)这个组合字段进行分组,这样我们可以控制rollup的分组规则的颗粒细度 rollup ((deptno,job),null) =group by (deptno,job) + group by (deptno,job), null +group by null 其中group by (deptno,job)=group by (deptno,job),null 所以其产生的结果集会出现group by (deptno,job)的重复记录 CUBEselect deptno,job,sum(sal),grouping (deptno),grouping(job) from emp groupby cube( deptno,job); 等价于 select deptno,job,sum(sal)from empgroupby deptno,job unionall select deptno,null,sum(sal)from empgroupby deptno unionall selectnull,null,sum(sal)from emp union all select null,job,sum(sal) from emp group by job CUBE的语法规则与rollup一致,只是在分组聚合上cube要更复杂,从数学的角度讲一个是排序规则,一个是组合规则 GROUP SETS可以按自己定义的规则进行分组聚合,Grouping sets ()的聚合规则是按“()”里的字段(字段可以是表的单一字段,也可以是多个字段的组合)进行聚合 select deptno,job,sum(sal),grouping (deptno),grouping(job) from emp groupby grouping sets(deptno,job,null) ; 等价于 group by depnto + group by job +group by null select deptno,job,sum(sal),grouping (deptno),grouping(job)from emp group by grouping sets((deptno,job),null) ; 等价于 group by deptno,job +group by null select deptno,job,sum(sal),grouping (deptno),grouping(job) from emp groupby groupingsets ((deptno,job)) ; 等价与 group by deptno,job Connect byCONNECT 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 具体的SQL语法不在此举例 产生一个无限序列 select levelfrom dualconnect by 1=1
注意 where 1=1 and ….. connect by prior and …. start with and ….. 的条件and的约束范围
select prior empno,prior mgr, empno ,mgr,x.ename,lpad(' ',(level-1)*10,' ')||x.ename from emp x connectbyprior x.empno=x.mgrstartwith x.mgrisnull 无限记录 select *from emp where empno=7369connectby empno=empno; 一条记录 select *from emp where empno=7369connectby empno=empnoand prior empno is null; 内部自循环,无法执行 select *from emp where empno=7369connectby prior empno=empno; 一条记录 select *from empwhere empno=7369connectbyprior empno=empno and prior empnoisnull; 一条记录 select *from emp where empno=7369connectby nocycle prior empno=empno ; OVER分析函数 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;
row_number()over(partitionby deptno orderby sal) x, rank() over(partitionby deptno orderby sal) y, dense_rank()over(partitionby deptno orderby 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 先按 deptno进行分组,然后在按ename排序后,累加 sal 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 2 following --是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总 行列互换函数行转列ListaggListagg 函数是11g以上才出现的,针对行转列的函数(10g不支持改函数) listagg函数的语法结构如下： listagg虽然是聚合函数，但可以提供分析功能（比如可选的OVER()子句）。使用listagg中，下列中的元素是必须的： 需要聚合的列或者表达式 WITH GROUP 关键词 分组中的ORDER BY子句 listagg聚合的结果列大小限制在varchar2类型的最大值内 varchar2(4000) select deptno,listagg(ename,',')withingroup(orderbynull )over(partitionby deptno) from emp; 从上面的结果集看,listagg并没有去掉重复的数据 select deptno, listagg(ename,',')within group(orderbynull) from emp groupby deptno; 自定义聚合函数针对 11g以下的数据库,不能使用 listagg这个函数,可以进行自定义聚合函数来替代此函数 oracle自定义聚集函数接口: static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type) return number 自定义聚集函数初始化设置,从这儿开始一个聚集函数 member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2) return number 自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self为当前聚集函数的指针,用来与前面的计算结果进行关联 member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT varchar2,flags IN number) return number 用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number) return number 终止聚集函数的处理,返回聚集函数处理的结果. 下面代码是实现自定义函数的结果如下图所示 实现代码如下: 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; / 执行以下语句即可 select deptno, f_row_column(ename)from scott.emp groupby deptno 其它函数利用connect by+分析函数也可以完成行转列的功能 请参考如下SQL with tas (select deptno, ename, count(*)over(partitionby deptno) count_num, row_number()over(partitionby deptno orderbynull) row_num from emp) select deptno, substr(sys_connect_by_path(ename,','),2) row_column from t where count_num=row_num connectbyprior t.row_num+1 = t.row_num and t.deptno =prior t.deptno startwith t.row_num =1 条件说明: connectbyprior t.row_num+1 = t.row_num and t.deptno =prior t.deptno Prior t.row_num +1=t.row_num 进行自连接的层次构造 t.deptno= prior t.deptno 保证自连接的记录都在一个deptno里进行 另外很多人使用 wmsys.wm_concat这个函数进行行列互换这个函数是oracle非公布的函数,也就是说在以后的数据库版本中,不一定还会继续支持,所以尽量避免使用,最好使用公布的函数 列转行REGEXP_SUBSTRRegexp_substr 按照正则表达式的规则,将一个字符串按分隔符拆分成记录行 REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier) srcstr ：检索字符串 pattern ：匹配模式 position ：搜索srcstr的起始位置（默认为1） occurrence ：搜索第几次出现匹配模式的字符串（默认为1） modifier ：检索模式('i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'） select regexp_substr('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD', '[^,]+', 1, level) from dual connectby regexp_count('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD','[^,]+') >= level; regexp_substr 中使用了正则表达式 '[^,]+',具体的正则表达式的含义,可以参考正则表达的语法 在不支持regexp_count的数据库环境下,可以使用 length , replace这两个函数来实现它的效果 Regexp_count Regexp_like Regexp_replace Regexp_instr这些函数都可以使用正则表达式来完成特定模式的处理 交叉函数PIVOT & UNPIVOTPivot 、Unpivot这两个函数是11g版本后推出的, pivot ( 聚合函数 for列名 in (列名的值)) unpiovt ( 伪列 for伪列 in (表中的字段名称 ) ) --表中的字段名称可以是多个,但必须是类型一致的字段 统计emp表中每个部门下的JOB有多少个员工,并以交叉报表的形式展现 select *from ( SELECT x.ename, x.deptno,x.job FROM emp x ) pivot (count(ename)for jobin ('ANALYST','CLERK','MANAGER','PRESIDENT','SALESMAN') ) ; 对不能使用pivot的函数我们可以通过其它方式展现,以下SQL是比较常用的产生交叉样式; SELECT DEPTNO,SUM(DECODE(JOB,'ANALYST',1,0)) ANALYST ,SUM(DECODE(JOB,'CLERK',1,0)) CLERK ,SUM(DECODE(JOB,'MANAGER',1,0)) MANAGER ,SUM(DECODE(JOB,'PRESIDENT',1,0)) PRESIDENT ,SUM(DECODE(JOB,'SALESMAN',1,0)) SALESMAN FROM EMP GROUPBY DEPTNO; Unpiovt : select * from emp unpivot( valuefor VALUE_TYPEin (ename,job) ); Model这个函数一般不太常见,但确是非常有用的一个函数,基本上model可以完成所有函数的功能 语法定义如下 --MODEL:MODEL语句的关键字，必须,后面可以跟 partition by --DIMENSION BY:维度的意思,必须,而且必须是一个主键或者是组合主键。 --MEASURES:指定作为数组的列,可以定义出许多有规则的伪列 --RULES:对数组的各列进行各种操作规则的定义,特有的函数有 any,cv(),cv(维度字段) 先从简单的了解下model函数的特性: 自循环功能 selectkey, m_1 from dual model dimensionby(0key) --定义维度列名=key值等于0 measures(cast(nullasvarchar2(100)) m_1 ) --定义一个度量类型是 varchar2(100) 列名=m_1 rules--规则约束 iterate(5) --定义自循环次数 =5从 0开始循环 (m_1[0]=nvl(m_1 [0],'TEST')||'x'||'/'||iteration_number||'/')
当然,此处不是要真的实现阶乘的算法,只是为了理解model函数的用法, 再看看如下的SQL 目的:根据emp表的 mgr和empno的关系来显示上级的ename和job 最直接最常用的语法就是 select x.empno,x.ename,x.job,x.mgr,y.ename,y.jobfrom emp x,emp y where x.mgr=y.empno(+) ; 但这样的SQL的执行计划显示对EMP表进行了两次全表扫描 换成model函数执行下 select *from emp model dimensionby (empno) measures ( ename,job,mgr ,cast(nullasvarchar2(20)) mgr_ename ,cast(nullasvarchar2(20)) mgr_job ) rules ( mgr_ename[any]=ename[mgr[cv()]] --cv()代表对当前行的维度值 --mgr[cv()]是获取当前维度下的mgr值 ,然后在对 mgr[cv()]进行维度的数据定位到度量ename也就是当前ename的上级ename ,mgr_job[any]=job[mgr[cv()]] ) 再看看它的执行计划,如下图: 执行以下SQL,看看结果集,理解model函数 --显示部门,年份,当前年份汇总sal,上年汇总sal with tas (select deptno,to_char(emp.hiredate,'yyyy')year,sum(sal) salfrom empgroupby deptno,to_char(emp.hiredate,'yyyy')) select deptno,year,sal,p_sal from t model dimensionby (deptno,year) measures (sal,0 p_sal) rules ( p_sal[any,any]=sal[cv(),cv(year)-1] ); --分组 group by deptno合计 select ename,salesfrom emp modelpartitionby (deptno) dimensionby (ename) measures (sal sales) rules ( sales['合计']=sum(sales)[cv(ename)='合计'] ); -- x =sal -- y 只给deptno=30的赋予当前sum(sal) -- z 显示 sum(sal) where deptno=20 -- m 汇总个部门的sum(sal) select deptno,ename,sales,x,y,z,mfrom emp modelpartitionby (deptno) dimensionby (ename,deptno dep) measures (sal sales,0 x,0 y,0 z,0 m) rules ( x[any,any]=sum(sales)[cv(),cv()] ,y[any,any]=sales[cv(),30]--注意此处是 30可以不用sum,而不是 cv()=30,cv()=30存在多条记录 ,z[any,any]=sum(sales) [any,cv()=20] ,m[any,any]=sum(sales) [any,any] ); --部门号,年份, --sum(sal) group by deptno,year --sum(sal) group by deptno --sum(sal) group by null --sum(sal) group by year --sum(sal) group by null with tas (select deptno,to_char(emp.hiredate,'yyyy')year,sum(sal) salfrom empgroupby deptno,to_char(emp.hiredate,'yyyy')) select deptno,year,sal,p_sal,x,y,m from t model dimensionby (deptno,year) measures (sal,0 p_sal ,0 x,0 y ,0 m) rules ( p_sal[any,any]=sum(sal)[cv(),cv()isnotnull ] --sum(sal) group by deptno ,x[any,any]=sum(sal)[any,cv()isnotnull ] --sum(sal) group by null ,y[any,any]=sum(sal)[cv()isnotnull,cv()] --sum(sal) group by year ,m[any,any]=sum(sal)[cv()isnotnull,any ] --sum(sal) group by null -- cv() 中如果没有null的记录那么 cv() is not null等价与 any ); 用model函数产生行转列 字符串='adfd,bere,cf234,4d54d' select r,z from dual model dimension by (0 x) measures (cast ('adfd,bere,cf234,4d54d'asvarchar2(200)) y ,cast(nullasvarchar2(1000)) z ,cast(nullasvarchar2(1000)) r --显示字符串列 ) --申明一个字符串的伪列 rulesiterate(10)--定义循环100次 --PRESENTV(cell,expr1,expr2) --如果cell引用的记录在MODEL子句执行以前就存在，那么返回表达式expr1。如果这条记录不存在，则返回表达式expr2 until (presentv( y[instr(y[0],',',1,iteration_number+2)],0,1) = 0 )--循环退出的条件 ( --对字符串进行循环截取操作 y[iteration_number+1]=substr(y[iteration_number],instr(y[iteration_number],',',1)+1) ,r[any]=y[0] ,z[iteration_number]=nvl(substr(y[iteration_number],1,instr(y[iteration_number],',',1)-1),y[iteration_number]) ,z[iteration_number+1]=y[iteration_number+1] ) 用model函数产生 列转行 with tas ( select'abc' xfrom dual unionall select'XTZ'from dual unionall select'IJM'from dual unionall select'KPI'from dual ) select *from t model dimensionby (rownum sn) measures(cast (x asvarchar2(1000)) x) rules iterate (100) until (presentv( x[ iteration_number+1],1,0 )=0 ) ( x[0]=x[0]||','||x[iteration_number+1] ); 用model函数产生交叉表格 select DEPTNO,CLERK_JOB,ANALYST_JOB,MANAGER_JOB,PRESIDENT_JOB,SALESMAN_JOBfrom emp modelpartitionby (deptno) dimensionby (empno,job) measures ( ename,cast(nullasvarchar2(1000)) CLERK_JOB ,cast(nullasvarchar2(1000)) ANALYST_JOB ,cast(nullasvarchar2(1000)) MANAGER_JOB ,cast(nullasvarchar2(1000)) PRESIDENT_JOB ,cast(nullasvarchar2(1000)) SALESMAN_JOB ) rules( CLERK_JOB[ANY,ANY]= (ENAME[CV(),'CLERK']) ,ANALYST_JOB[ANY,ANY]=(ENAME[CV(),'ANALYST']) ,MANAGER_JOB[ANY,ANY]=(ENAME[CV(),'MANAGER']) ,PRESIDENT_JOB[ANY,ANY]=(ENAME[CV(),'PRESIDENT']) ,SALESMAN_JOB[ANY,ANY]=(ENAME[CV(),'SALESMAN']) ); |
|
|
