oracle分析函数

这首先引用一个大佬关于分析函数理解

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