分析函数总结(oracle技术交流群).docx

分析函数的基础语法oracle分析函数的语法： function_name(arg1,arg2,...) over (partition-clause order-by-clause windowing clause) 说明： 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.有的分析函数可以带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 JOIN使用的)， 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 following 有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 preceding 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 preceding 都是错误的 默认的窗口是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之前，分析函数也可以用于子查询，用于过滤父查询的查询结果。分析函数只允许出现在order by和select中，只是针对同一级查询。不可嵌套如果最后查询结果需要一定的顺序，则在最后显示指定order by，因为分区子句的order by只保证组内有序，特别是有多个分析函数的时候，会覆盖的排序结果(函数优先级，顺序等)，所以最后要显示order by