发布时间 : 星期六 文章oracle的sqlplus下的配置更新完毕开始阅读f4f09e76001ca300a6c30c22590102020740f2b0
oracle分析函数--SQL*PLUS环境 一、总体介绍
12.1 分析函数如何工作
语法 FUNCTION_NAME(<参数>,?) OVER (
RANGE N PRECEDING 仅对数值或日期类型有效,选定窗为排序后当前行之前,某列(即排序列)值大于/小于(当前行该列值 –/+ N)的所有行,因此与ORDER BY子句有关系。
2. 行窗(ROW WINDOW)
ROWS N PRECEDING 选定窗为当前行及之前N行。
还可以加上BETWEEN AND 形式,例如RANGE BETWEEN m PRECEDING AND n FOLLOWING
函数 AVG(
一组或选定窗中表达式的平均值 CORR(expr, expr) 即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2)),两个表达式的互相关,-1(反相关) ~ 1(正相关),0表示不相关
COUNT(
CUME_DIST 累积分布,即行在组中的相对位置,返回0 ~ 1
DENSE_RANK 行的相对排序(与ORDER BY搭配),相同的值具有一样的序数(NULL计为相同),并不留空序数
FIRST_VALUE 一个组的第一个值
LAG(expr,
LAST_VALUE 一个组的最后一个值
LEAD(expr,
MAX(expr) 最大值 MIN(expr) 最小值
NTILE(expr) 按表达式的值和行在组中的位置编号,如表达式为4,则组分4份,分别为1 ~ 4的值,而不能等分则多出的部分在值最小的那组 PERCENT_RANK 类似CUME_DIST,1/(行的序数 - 1) RANK 相对序数,答应并列,并空出随后序号
RATIO_TO_REPORT(expr) 表达式值 / SUM(表达式值) ROW_NUMBER 排序的组中行的偏移 STDDEV(expr) 标准差
STDDEV_POP(expr) 总体标准差 STDDEV_SAMP(expr) 样本标准差
SUM(expr) 合计
VAR_POP(expr) 总体方差 VAR_SAMP(expr) 样本方差 VARIANCE(expr) 方差
REGR_ xxxx(expr, expr) 线性回归函数
REGR_SLOPE：返回斜率，等于COVAR_POP(expr1, expr2) / VAR_POP(expr2) REGR_INTERCEPT：返回回归线的y截距，等于
AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2) REGR_COUNT：返回用于填充回归线的非空数字对的数目 REGR_R2：返回回归线的决定系数，计算式为： If VAR_POP(expr2) = 0 then return NULL
If VAR_POP(expr1) = 0 and VAR_POP(expr2) != 0 then return 1 If VAR_POP(expr1) > 0 and VAR_POP(expr2 != 0 then return POWER(CORR(expr1,expr),2)
REGR_AVGX：计算回归线的自变量(expr2)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr2)
REGR_AVGY：计算回归线的应变量(expr1)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr1)
REGR_SXX： 返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2) REGR_SYY： 返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1) REGR_SXY: 返回值等于REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)
首先：创建表及接入测试数据 create table students (id number(15,0), area varchar2(10), stu_type varchar2(2), score number(20,2));
insert into students values(1, '111', 'g', 80 ); insert into students values(1, '111', 'j', 80 ); insert into students values(1, '222', 'g', 89 ); insert into students values(1, '222', 'g', 68 ); insert into students values(2, '111', 'g', 80 ); insert into students values(2, '111', 'j', 70 ); insert into students values(2, '222', 'g', 60 ); insert into students values(2, '222', 'j', 65 ); insert into students values(3, '111', 'g', 75 ); insert into students values(3, '111', 'j', 58 ); insert into students values(3, '222', 'g', 58 ); insert into students values(3, '222', 'j', 90 ); insert into students values(4, '111', 'g', 89 ); insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 ); insert into students values(4, '222', 'j', 89 ); commit;
二、具体应用： 1、分组求和： 1）GROUP BY子句 --A、GROUPING SETS
select id,area,stu_type,sum(score) score from students
group by grouping sets((id,area,stu_type),(id,area),id) order by id,area,stu_type;
/*--------理解grouping sets select a, b, c, sum( d ) from t group by grouping sets ( a, b, c )
等效于
select * from (
select a, null, null, sum( d ) from t group by a union all
select null, b, null, sum( d ) from t group by b union all
select null, null, c, sum( d ) from t group by c ) */
--B、ROLLUP
select id,area,stu_type,sum(score) score from students
group by rollup(id,area,stu_type) order by id,area,stu_type;
/*--------理解rollup select a, b, c, sum( d ) from t
group by rollup(a, b, c);
等效于
select * from (
select a, b, c, sum( d ) from t group by a, b, c union all
select a, b, null, sum( d ) from t group by a, b union all
select a, null, null, sum( d ) from t group by a union all
select null, null, null, sum( d ) from t ) */
--C、CUBE
select id,area,stu_type,sum(score) score from students
group by cube(id,area,stu_type) order by id,area,stu_type;
/*--------理解cube
select a, b, c, sum( d ) from t group by cube( a, b, c)
等效于
select a, b, c, sum( d ) from t group by grouping sets( ( a, b, c ),
( a, b ), ( a ), ( b, c ), ( b ), ( a, c ), ( c ), () ) */
--D、GROUPING
/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null, 如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/
select decode(grouping(id),1,'all id',id) id,
decode(grouping(area),1,'all area',to_char(area)) area,
decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type, sum(score) score from students
group by cube(id,area,stu_type) order by id,area,stu_type;