Oracle的分组统计函数
(avg,corr,count,covar_pop,covar_samp,cume_dist,dense_rank,first,group_id,grouping,grouping_id,glb,last,listagg,lub,max,min,percent_rank,percentile_cont,percentile_disc,rank,stddev,stddev_pop,stddev_samp,sum,var_pop,var_samp,variance)
说明:分组函数也被称为多行函数,它会根据输入的多行数据返回一个结果.主要用于执行数据统计或汇总操作,并且分组函数只能出现在select语句选择列表、order by子句和having子句中.注意分组函数不能直接在plsql中引用,只能在内嵌select语句中使用.分组函数,除了count(),count(1),其他分组函数都会忽略null行,包括count(列名)./
/*AVG
语法:AVG([DISTINCT|ALL]col)
功能:返回一列数据的平均值,缺省使用是ALL修饰符,all表示对所有的值求平均值,distinct排重后再求平均值
使用位置:查询列表和GROUP BY子句.*/
select avg(HISAL) V FROM scott.salgrade;
/*CORR
语法:CORR([expr1,expr2)
功能:返回成对数值的相关系数,其数值使用表达式”covar_pop(expr1,expr2)/(stddev_pop(expr1)*stddev_pop(expr2))”
使用位置:查询列表和GROUP BY子句.*/
select corr(LOSAL,HISAL) V from scott.salgrade;
/*COUNT
语法:COUNT(*|[DISTINCT|ALL] col)
功能:得到查询中行的数目.如果使用了获得行的总数.如果在参数中传递的是选择列表,那么计算的是非空数值.我基于10G测试,有主键情况下,count(主键)最快,count(1)和count()调动主键统计,时间上一样；无主键情况下count(索引列)最快,但需要注意count(列名)统计不包括null,count(常量)和count()包括null/
select count(distinct GRADE) V from scott.salgrade;
/*COVAR_POP
语法:COVAR_POP(expr1,expr2)
功能:返回成对数字的协方差,其数值使用表达式”(sum(expr1*expr2)-sum(expr1)sum(expr2)/n)/n”/
select COVAR_POP(LOSAL,HISAL) V from scott.salgrade;
/*COVAR_SAMP
语法:COVAR_SAMP(expr1,expr2)
功能:返回成对数字的协方差,其数值使用表达式”(sum(expr1*expr2)-sum(expr1)sum(expr2)/n)/n-1”/
select COVAR_SAMP(LOSAL,HISAL) V from scott.salgrade;
/*CUME_DIST
语法:CUME_DIST(expr1,expr2…) within group (order by expr1,expr2…)
功能:返回特定数值在一组行数据中的累积分布比例.*/
select CUME_DIST(2000) within group (order by sal) v from scott.emp;
/*DENSE_RANK
语法:DENSE_RANK(expr1,expr2…) within group (order by expr1,expr2…)
功能:返回特定数据在一组行数据中的等级.*/
select DENSE_RANK (5000) within group (order by sal) v from scott.emp;
/*FIRST
语法:FIRST
功能:9i新增,不能单独使用,必须与其他分组函数结合使用.通过使用该函数,可以取得排序等级的第一级,然后使用分组函数汇总该等级的数据.*/
select min(sal) keep (dense_rank first order by comm desc) v from scott.emp;
/*GROUP_ID
语法:GROUP_ID
功能:9i新增,用于区分分组结果中的重复行.*/
select deptno,job,avg(sal),group_id() from scott.emp group by deptno,rollup(deptno,job);
/*GROUPING
语法:GROUPING(expr)
功能:用于确定统计结果是否使用了特定的表达式,返回0则用到了表达式,1则未用.*/
/*GROUPING_ID
语法:GROUPING_ID(expr1[,expr2]…)
功能:9i新增,用于返回对应于特定行的grouping位向量的值.*/
select deptno,job,sum(sal),grouping_id(job,deptno) v from scott.emp group by rollup(deptno,job);
/*GLB
语法:GLB ([DISTINCT|ALL]label)
功能:获得由label界定的最大下界.函数仅用于trusted oracle.
使用位置:trusted数据库的选择列表和GROUP BY子句.*/
/*LAST
语法:LAST
功能:9i新增,不能单独使用,必须与其他分组函数结合使用.通过使用该函数,可以取得排序等级的最后一级,然后使用分组函数汇总该等级的数据.*/
select min(sal) keep (dense_rank last order by comm) v from scott.emp;
/*LISTAGG
语法:listagg
功能:列转行*/
select deptno,listagg(ENAME||':'||SAL, ';') within group(order by deptno) v from scott.emp group by deptno;
/*LUB
语法:LUB ([DISTINCT|ALL]label)
功能:获得由label界定的最小上界.用于trusted oracle.数据库.
使用位置:trusted数据库的选择列表和GROUP BY子句.过程性语言和SQL语句.*/
/*MAX
语法:MAX([DISTINCT|ALL]col)
功能:获得选择列表或表达式的最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
使用位置:仅用于查询选择和GROUP BY子句.*/
select max(distinct sal) v from scott.emp;
/*MIN
语法:MIN([DISTINCT|ALL]col)
功能:获得选择列表或表达式的最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
使用位置:仅用于查询选择和GROUP BY子句.*/
select min(all sal) v from scott.emp;
/*PERCENT_RANK
语法:PERCENT_RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)
功能:该函数用于返回特定数值在统计级别中所占的比例.*/
select percent_rank(3000) within group(order by sal) V from scott.emp;
/*PERCENTILE_CONT
语法:PERCENTILE_CONT(percent_expr)WITHIN GROUP (ORDER BY expr)
功能:9i新增,用于返回在统计级别中处于某个百分点的特定数值(按照连续分布模型确定).*/
select percentile_cont(0.6) within group(order by sal) V from scott.emp;
/*PERCENTILE_DISC
语法:PERCENTILE_DISC(percent_expr)WITHIN GROUP (ORDER BY expr)
功能:9i新增,用于返回在统计级别中处于某个百分点的特定数值(按照离散分布模型确定).*/
select percentile_cont(0.5) within group(order by sal) V from scott.emp;
/*RANK
语法:RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)
功能:该函数用于返回特定数值中所占据的等级.*/
select rank(3000) within group(order by sal) V from scott.emp;
/*STDDEV
语法:STDDEV([DISTINCT|ALL]col)
功能:获得选择列表的标准差.标准差为方差(VARIANCE)的平方根, ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差.
使用位置:仅用于查询选择和GROUP BY子句.*/
select stddev(distinct sal) v from scott.emp;
/*STDDEV_POP
语法:STDDEV_POP(col)
功能:返回统计标准差,其数值是统计方差的平方根.
使用位置:仅用于查询选择和GROUP BY子句.*/
select stddev_pop(sal) v from scott.emp;
/*STDDEV_SAMP
语法:STDDEV_SAMP(col)
功能:返回采样标准差,其数值是采样方差的平方根.*/
select stddev_samp(sal) v from scott.emp;
/*SUM
语法:SUM([DISTINCT|ALL]col)
功能:返回选择的数值和总和
使用位置:仅用于查询选择和GROUP BY子句.*/
select sum(sal) v from scott.emp;
/*VAR_POP
语法:VAR_POP([DISTINCT|ALL]col)
功能:返回统计方差.使用公式为(sum(expr*expr)-sum(expr)*sum(expr)/count(expr))/(count(expr)
使用位置:仅用于查询选择和GROUP BY子句.*/
select VAR_POP(sal) v from scott.emp;
/*VAR_SAMP
语法:VAR_SAMP([col)
功能:返回采样方差.使用公式为(sum(expr*expr)-sum(expr)*sum(expr)/count(expr))/(count(expr-1)
使用位置:仅用于查询选择和GROUP BY子句.*/
select variance(sal) v from scott.emp;
/*VARIANCE
语法:VARIANCE([DISTINCT|ALL]col)
功能:返回选择列或表达式的采样方差.使用公式为(sum(expr*expr)-sum(expr)*sum(expr)/count(expr))/(count(expr-1)
使用位置:仅用于查询选择和GROUP BY子句.*/
select variance(sal) v from scott.emp;
