Oracle
SQL一些函数用法
以下sql环境都是在oracle
11g/scott完成
Group
by
与GROUP
BY一起使用的关建字GROUPING,GROUP
SET,ROLLUP,CUBE结合这些特性的函数可以实现SQL的明细+分组统计
GROUPING
GROUPING记录是对哪个字段进行统计,其值只能是0
& 1
Group(column)
=0表示此字段参与了分组统计=1表示字段未参与分组统计
selectdeptno,job,sum(sal),grouping(deptno),grouping(job)
fromempgroupby(
deptno,job);
执行结果如下
ROLLUP
selectdeptno,job,sum(sal),grouping(deptno),grouping(job)
fromempgroupbyrollup(
deptno,job);
等价于
selectnull,null,sum(sal)fromemp
unionall
selectdeptno,null,sum(sal)fromempgroupbydeptno
unionall
selectdeptno,job,sum(sal)fromempgroupbydeptno,job
首先是进行无字段的聚合,然后在对字段进行从左到右依次组合后聚合
从上面的结果可以看到
rollup( deptno,job) = group
by deptno, job + group by deptno
+ group by null
另外rollup
(deptno,job)<>rollup(job,deptno)两者的结果集是不同的,可以把SQL修改后,执行下看看
提示:在对rollup的分组进行组合使用
selectdeptno,job,sum(sal),grouping(deptno),grouping(job)
fromempgroupbyrollup((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)的重复记录
CUBE
selectdeptno,job,sum(sal),grouping(deptno),grouping(job)
fromempgroupbycube(
deptno,job);
等价于
selectdeptno,job,sum(sal)fromempgroupbydeptno,job
unionall
selectdeptno,null,sum(sal)fromempgroupbydeptno
unionall
selectnull,null,sum(sal)fromemp
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 group by 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
selectdeptno,job,sum(sal),grouping(deptno),grouping(job)
fromempgroupbygroupingsets((deptno,job))
;
等价与 group by deptno,job
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
具体的SQL语法不在此举例
产生一个无限序列
select level from dualconnect by 1=1
显示20个记录结果集select level from dualconnect by 1=1 and level<=20Lpad与level伪列可以显示一个层级结构的数据select lpad('
',(level-1)*10,'
')||ename,levelfrom emp connectby prior empno=mgr start with mgr is null
注意where 1=1
and
….. connect
by prior and
…. start
with and
…..的条件and的约束范围
再分别执行以下语句,并理解其含义
selectpriorempno,priormgr,empno
,mgr,x.ename,lpad('
',(level-1)*10,'
')||x.ename
fromempx connectbypriorx.empno=x.mgr startwithx.mgrisnull
无限记录
select *from emp where empno=7369connect by empno=empno;
一条记录
select *from emp where empno=7369connect by empno=empnoand prior
empno is null;
内部自循环,无法执行
select * from emp where empno=7369connect by prior empno=empno;
一条记录
select* fromempwhereempno=7369connectbypriorempno=empno andpriorempnoisnull;
一条记录
select*fromempwhereempno=7369connectbynocyclepriorempno=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;
selectempno,ename,sal,deptno,
row_number()over(partitionbydeptnoorderbysal)
x,
rank()over(partitionbydeptnoorderbysal)
y,
dense_rank()over(partitionbydeptnoorderbysal)
z
fromemp;
执行以下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)的汇总
行列互换函数
行转列
Listagg
Listagg函数是11g以上才出现的,针对行转列的函数(10g不支持改函数)
listagg函数的语法结构如下：LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY
)]
listagg虽然是聚合函数，但可以提供分析功能(比如可选的OVER()子句)。使用listagg中，下列中的元素是必须的：
需要聚合的列或者表达式
WITH
GROUP关键词
分组中的ORDER BY子句
listagg聚合的结果列大小限制在varchar2类型的最大值内varchar2(4000)
selectdeptno,listagg(ename,',')withingroup(orderbynull)over(partitionbydeptno)fromemp;
从上面的结果集看,listagg并没有去掉重复的数据
select deptno,
listagg(ename,',') within group(order bynull)
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;
/
执行以下语句即可
selectdeptno,
f_row_column(ename)fromscott.emp
groupbydeptno
其它函数
利用connect
by+分析函数也可以完成行转列的功能
请参考如下SQL
withtas
(selectdeptno,
ename,
count(*)over(partitionbydeptno)
count_num,
row_number()over(partitionbydeptnoorderbynull)
row_num
fromemp)
selectdeptno,substr(sys_connect_by_path(ename,','),2)
row_column
fromtwherecount_num=row_num
connectbypriort.row_num+1=
t.row_numandt.deptno =priort.deptno
startwitht.row_num =1
条件说明:
connectbypriort.row_num+1=
t.row_numandt.deptno =priort.deptno
Prior t.row_num +1=t.row_num进行自连接的层次构造
t.deptno= prior
t.deptno保证自连接的记录都在一个deptno里进行
另外很多人使用wmsys.wm_concat这个函数进行行列互换这个函数是oracle非公布的函数,也就是说在以后的数据库版本中,不一定还会继续支持,所以尽量避免使用,最好使用公布的函数
列转行
REGEXP_SUBSTR
Regexp_substr按照正则表达式的规则,将一个字符串按分隔符拆分成记录行
REGEXP_SUBSTR(srcstr, pattern,
position, occurrence, modifier)
srcstr：检索字符串
pattern：匹配模式
position：搜索srcstr的起始位置(默认为1)
occurrence：搜索第几次出现匹配模式的字符串(默认为1)
modifier：检索模式('i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c')
selectregexp_substr('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD',
'[^,]+',
1,
level)
fromdual
connectbyregexp_count('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD','[^,]+')
>=
level;
regexp_substr中使用了正则表达式'[^,]+',具体的正则表达式的含义,可以参考正则表达的语法
在不支持regexp_count的数据库环境下,可以使用length
, replace这两个函数来实现它的效果
Regexp_count Regexp_like Regexp_replace Regexp_instr这些函数都可以使用正则表达式来完成特定模式的处理
交叉函数PIVOT &
UNPIVOT
Pivot、Unpivot这两个函数是11g版本后推出的,
pivot
(聚合函数for列名in
(列名的值))
unpiovt
(伪列for伪列in
(表中的字段名称) )
--表中的字段名称可以是多个,但必须是类型一致的字段
统计emp表中每个部门下的JOB有多少个员工,并以交叉报表的形式展现
select*from(
SELECTx.ename,
x.deptno,x.jobFROMemp x
)
pivot(count(ename)forjobin('ANALYST','CLERK','MANAGER','PRESIDENT','SALESMAN'))
;
对不能使用pivot的函数我们可以通过其它方式展现,以下SQL是比较常用的产生交叉样式;
SELECTDEPTNO,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
FROMEMP
GROUPBYDEPTNO;
Unpiovt :
select*fromemp
unpivot(
valueforVALUE_TYPEin(ename,job)
);
Model
这个函数一般不太常见,但确是非常有用的一个函数,基本上model可以完成所有函数的功能
具体的官方资料可以参考noname.htm
语法定义如下
--MODEL:MODEL语句的关键字，必须,后面可以跟partition
by
--DIMENSION
BY:维度的意思,必须,而且必须是一个主键或者是组合主键。
--MEASURES:指定作为数组的列,可以定义出许多有规则的伪列
--RULES:对数组的各列进行各种操作规则的定义,特有的函数有any,cv(),cv(维度字段)
先从简单的了解下model函数的特性:
自循环功能
selectkey,
m_1fromdual
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的循环来实现阶层的算法
当然,此处不是要真的实现阶乘的算法,只是为了理解model函数的用法,
再看看如下的SQL
目的:根据emp表的mgr和empno的关系来显示上级的ename和job
最直接最常用的语法就是
selectx.empno,x.ename,x.job,x.mgr,y.ename,y.jobfromemp
x,emp y
wherex.mgr=y.empno(+);
但这样的SQL的执行计划显示对EMP表进行了两次全表扫描
换成model函数执行下
select*fromemp
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
withtas(selectdeptno,to_char(emp.hiredate,'yyyy')year,sum(sal)
salfromempgroupbydeptno,to_char(emp.hiredate,'yyyy'))
selectdeptno,year,sal,p_sal
fromt
model
dimensionby(deptno,year)
measures(sal,0p_sal)
rules
(
p_sal[any,any]=sal[cv(),cv(year)-1]
);
--分组group by deptno合计
selectename,sales fromemp
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)
selectdeptno,ename,sales,x,y,z,m fromemp
modelpartitionby(deptno)
dimensionby(ename,deptno dep)
measures(sal sales,0x,0y,0z,0m)
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
withtas(selectdeptno,to_char(emp.hiredate,'yyyy')year,sum(sal)
salfromempgroupbydeptno,to_char(emp.hiredate,'yyyy'))
selectdeptno,year,sal,p_sal,x,y,m
fromt
model
dimensionby(deptno,year)
measures(sal,0p_sal ,0x,0y ,0m)
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'
selectr,z
fromdual
model
dimensionby(0x)
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函数产生列转行
withtas(
select'abc'xfromdual
unionall
select'XTZ'fromdual
unionall
select'IJM'fromdual
unionall
select'KPI'fromdual
)
select*fromt
model
dimensionby(rownumsn)
measures(cast(xasvarchar2(1000))
x)
rules
iterate(100)
until(presentv( x[
iteration_number+1],1,0)=0)
(
x[0]=x[0]||','||x[iteration_number+1]
);
用model函数产生交叉表格
selectDEPTNO,CLERK_JOB,ANALYST_JOB,MANAGER_JOB,PRESIDENT_JOB,SALESMAN_JOBfromemp
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'])
);
3990
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
