《分析函数总结》由会员分享，可在线阅读，更多相关《分析函数总结（11页珍藏版）》请在金锄头文库上搜索。
1、分析函数的基础语法oracle分析函数的语法： function_name(arg1,arg2,.) over ( ) 说明： 1. partition-clause 数据记录集分组 2. order-by-clause 数据记录集排序 3. windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。有三种开窗方式: range、row、specifying。分析函数的四大要点Analytic Function PARTITION BY ORDER BYROWS Vs RANGEUNBOUNDED PRECEDING FOLLOWING CURRENT ROW1.有
2、的分析函数可以带window,有的不能带window子句,不能带window子句的比如rank,dense_rank,row_number,FIRST,LAST,lead,lag 可以带windows的比如count,SUM,AVG,MIN,MAX,first_value,last_value。 FIRST,LAST里的分析函数部分order BY都是不允许的 有的分析函数，比如row_number,dense_rank,rank是必须要有order BY的。 2.PARTITION BY 是按字段值将对应的行分组(不能带括号，带括号的是model和 PARTITIONED outer JOI
3、N使用的)， ORDER BY 是组内行的顺序，window子句决定每行对应的窗口范围3.PARTITION BY ,ORDER BY ,window子句共同决定了当前行对应的窗口范围，当前行分析函数值就是基于这个窗口计算的4.注意partition BY,ORDER BY,window子句的关系 window子句是在partition by和order BY前提下设定当前行对应的窗口范围的,因此必须有order by才能写window子句。 可以没有这三个子句，那么相当于当前行对应于所有行的窗口中。BETWEEN unbounded preceding AND unbounded follo
4、wing 有partition by可以没有有order BY，注意有的分析函数必须要有order BY. 5.ORDER by与rows,range的区别 range保证结果的稳定性，RANGE的行都是逻辑行，按order BY值计算，包括current ROW也是逻辑行。 默认的有order by没有window就是逻辑上限到当前逻辑行，排序重复稳定 ROWS是物理行，按排序的行标计算，CURRENT ROW是物理行。排序重复不稳定 ORDER BY如果有多个排序键值，那么range则必须对应的窗口是（因为逻辑窗口不知道按什么键来计算）: a. between unbounded prec
5、eding and current row -相当于没有写window，因为order by默认就是组的首行到当前行 b.between current row and unbounded following c.between unbounded preceding and unbounded following 相当于没有写order by，表示是组的首行到组的末行 在排名函数，FIRST/LAST 6.window子句定义的范围必须从上到下。比如： rows 1 following，range 1 following,rows between 1 preceding and 2 prec
6、eding 都是错误的 默认的窗口是range到current ROW7. 0 following和0 preceding都相同于current row。 分析函数里使用DISTINCT有限制,不能带order by 8. 分析子句顺序是partition子句order by子句window子句。其中有的分析函数必须有order by子句，另外有window子句必须要有order by子句。 分析函数是在from，where，group by，having之后才开始工作的。出现在最后order by和select之前，分析函数也可以用于子查询，用于过滤父查询的查询结果。分析函数只允许出现在or
7、der by和select中，只是针对同一级查询。不可嵌套如果最后查询结果需要一定的顺序，则在最后显示指定order by，因为分区子句的order by只保证组内有序，特别是有多个分析函数的时候，会覆盖的排序结果(函数优先级，顺序等)，所以最后要显示order by 8.分析函数的缺点：经常需要有排序操作，很多就算无order by也需要内部排序，如果写多个分析函数，会产生很多排序，依赖于内存。当然也可以优化排序，比如通过索引消除排序。分析函数的优点：代替复杂的子查询，join等，减少表的扫描次数，提高效率。分析函数的分类主要列表分为四类聚集分析函数SUM ：该函数计算组中表达式的累积和MI
8、N ：在一个组中的数据窗口中查找表达式的最小值MAX ：在一个组中的数据窗口中查找表达式的最大值AVG ：用于计算一个组和数据窗口内表达式的平均值。COUNT ：对一组内发生的事情进行累积计数-排名分析函数RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置DENSE_RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置FIRST ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行LAST ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行FIRST_VALUE ：返回组中数据窗口的
9、第一个值LAST_VALUE ：返回组中数据窗口的最后一个值。ROW_NUMBER ：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号-行比较分析函数LAG ：可以访问结果集中的其它行而不用进行自连接LEAD ：LEAD与LAG相反，LEAD可以访问组中当前行之后的行-统计分析函数STDDEV ：计算当前行关于组的标准偏离STDDEV_POP：该函数计算总体标准偏离，并返回总体变量的平方根STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根VAR_POP ：该函数返回非空集合的总体变量（忽略null）VAR_SAMP ：该函数返回非空集合的样本变量（忽略null）
10、VARIANCE ：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMPCOVAR_POP ：返回一对表达式的总体协方差COVAR_SAMP ：返回一对表达式的样本协方差CORR ：返回一对表达式的相关系数CUME_DIST ：计算一行在组中的相对位置NTILE ：将一个组分为表达式的散列表示PERCENT_RANK ：和CUME_DIST（累积分配）函数类似PERCENTILE_DISC ：返回一个与输入的分布百分比值相对应的数据值PERCENTILE_CONT ：返回一个与输入的分布百分比值相对应的数据值RATIO_TO_REPORT ：该函数计算expressi
11、on/(sum(expression)的值，它给出相对于总数的百分比REGR_ (Linear Regression) Functions ：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用分析函数示例 （如打不开请双击右边的图片）分析函数专题连续值连续数区间临界值连续值区间案例1：要求查出连续数据，并且要写出最小值和最大值及连续的个数drop table t purge;create table t (id1 int,id2 int ,id3 int);insert into t (id1 ,id2,id3) values (1,45,89);insert into t (i
12、d1 ,id2,id3) values (2,45,89);insert into t (id1 ,id2,id3) values (3,45,89);insert into t (id1 ,id2,id3) values (8,45,89);insert into t (id1 ,id2,id3) values (12,45,89);insert into t (id1 ,id2,id3) values (36,45,89);insert into t (id1 ,id2,id3) values (22,45,89);insert into t (id1 ,id2,id3) values (
13、23,45,89);insert into t (id1 ,id2,id3) values (89,45,89);insert into t (id1 ,id2,id3) values (92,45,89);insert into t (id1 ,id2,id3) values (91,45,89);insert into t (id1 ,id2,id3) values (90,45,89);commit;效果如下 1 3 322 23 289 92 4SELECT MIN(id1), MAX(id1), COUNT(*) FROM (SELECT id1, id2, id3, ROW_NUMBER() OVER(ORDER BY id1) - ID1 AS group_id FROM t)HAVING COUNT(*) 1 GROUP BY group_id ORDER BY 1;2：找出sp_id,trade_money相同的，且start_time 在4分钟内大于等于4条的记录找出sp_id,trade_money相同的，且start_time 在4分钟内大于等于4条的记录大于5条数据drop
