1、分析函数是什么?
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。
2、分析函数和聚合函数的不同之处是什么?
普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition by分组,并且每组每行都可以返回一个统计值。
3、分析函数的形式
分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)。
注:窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提
关键词解释
unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
unbounded:不受控制的,无限的
preceding:在…之前
following:在…之后
ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总
ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总
ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
原表数据
实例一FIRST_VALUE()和LAST_VALUE()
FIRST_VALUE()指从开始到当前第一个值
FIRST_VALUE()指从开始到当前最后一个值
with temp as (
select level ord, decode(level, 5, null, 6, null, level) num
from dual
connect by level <= 8
)
select t1.*,
first_value(t1.num) over(order by t1.ord asc) first_lead,
first_value(t1.num ignore nulls) over(order by t1.ord asc) first_ignore,
last_value(t1.num) over(order by t1.ord asc) last_lag,
last_value(t1.num ignore nulls) over(order by t1.ord asc) last_ignore
from temp t1
order by ord
4、开窗函数之行内排序只去第一条数据
select t.*
from (select a.*, row_number() over(partition by 需要分组的字段 order by 排序字段 desc) rw
from 表 a) t
where t.rw = 1
5、前一行数据与后一行数据
with temp as (
/*select level ord, decode(level, 5, null, 6, null, level) num
from dual
connect by level <= 8
*/
select 1 as ord from dual
union all
select 2 as ord from dual
union all
select 3 as ord from dual
union all
select 6 as ord from dual
union all
select 9 as ord from dual
)
select c.* from (select t1.ord,lag(t1.ord,1,0) over (order by t1.ord) as p from temp t1) c
with temp as (
/*select level ord, decode(level, 5, null, 6, null, level) num
from dual
connect by level <= 8
*/
select 1 as ord from dual
union all
select 2 as ord from dual
union all
select 3 as ord from dual
union all
select 6 as ord from dual
union all
select 9 as ord from dual
)
select c.* from (select t1.ord,lead(t1.ord,1,0) over (order by t1.ord) as p from temp t1) c
没有补0