常用Oracle分析函数大全（配上开窗函数over()）

• 分析函数是什么？

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。          

• 分析函数和聚合函数的不同之处是什么？

普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

• 分析函数的形式

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
注：窗口子句在这里我只说rows方式的窗口，range方式和滑动窗口也不提

1、first_value()与last_value()：求最值对应的其他属性

特殊说明：如果想要求最值不为空的值，可以参考一下Demo

last_value(t2.posi_change ignore nulls) over(partition by t1.trading_day,t1.client_id,t1.product_class,t1.product_id,t1.instrument_id order by t1.trade_time rows between unbounded preceding and 1 preceding)

2、rank(),dense_rank()与row_number()：求排序

rank，dense_rank，row_number函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①row_number： 
row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
②dense_rank： 
dense_rank函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。
③rank： 
rank函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

3、lag()与lead()：求之前或之后的第N行

lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)
第一个参数是列名，
第二个参数是偏移的offset，
第三个参数是超出记录窗口时的默认值。

特殊说明：如果想要求之前或之后不为空的第N行，可以参考一下Demo

select lag(tn.instrument_id) ignore nulls over(partition by tn.product_id order by tn.create_date) from table_name tn

4、rollup()与cube()：排列组合分组

1)、group by rollup(a, b, c)：
首先会对(a、b、c)进行group by，
然后再对(a、b)进行group by，
其后再对(a)进行group by，
最后对全表进行汇总操作。     

2)、group by cube(a, b, c)：
则首先会对(a、b、c)进行group by，
然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
最后对全表进行汇总操作。

5、max(),min(),sun()与avg()：求移动的最值总和与平均值