Oracle中的分析函数总结

分析函数：也称开窗和窗口函数；它可以在数据中进行分组之后，基于组的统计值，并且每个分组的每一行都返回一个值。

(1) 聚合函数的分析函数

1. 分组统计：函数名(参数1, 参数2) over(partition by 列|表达式)
2. 不分组排序统计：函数名(参数1, 参数2) over(order by 列|表达式 asc/desc)
3. 分组后排序统计：函数名(参数1, 参数2) over(partition by 列|表达式 order by 列|表达式)

select avg(column1) over(partition by column2) from ...
select avg(column1) over(order by column2) from ...
select avg(column1) over(partition by column2 order by column3) from ...

(2) 排序函数的分析函数

1. row_numer(): 函数返回一个唯一值，当碰到相同数据时，排名会按照顺序依次递增：1,2,3,4,5…
2. rank(): 当碰到相同数据时，相同数据排名一致，最后一条相同数据和下一条数据之间空出排名：1,2,2,4,5…
3. dense_rank(): 当碰到相同数据时，相同数据排名一致，最后一条相同数据和下一条不同数据之间不空出排名：1,2,2,3,4…

select row_number() over(partition by column2) from ...
select row_number() over(order by column2) from ...
select row_number() over(partition by column2 order by column3) from ...

(3) 位移函数的分析函数

1. 上移：lead(数据列, 参数), 参数表示上移的行数
2. 下移：lag((数据列, 参数), 参数表示下移的行数

select lead(column1,1) over(partition by column2) from ...
select lead(column1,1) over(order by column2) from ...
select lead(column1,1) over(partition by column2 order by column3) from ...