oracle分析函数应用

oracle分析函数适用于联机分析处理OLAP，在进行多维度聚合方面拥有极大的优势。

现做一些应用如下：

create table sale_stat(

country varchar2(50),

subregion varchar2(50),

procname varchar2(50),

stat_year varchar2(20),

stat_week number(10),

sales number(24,6)

);

--聚合分区函数

select stat_year,

country,

subregion,

stat_week,

sales,

sum(sales)over(partition by stat_year,country order by sales rows between unbounded preceding and current row)

from sale_stat

order by stat_year,sales;

--跨越整个分区的聚合函数,使用开窗子句

select stat_year,

country,

subregion,

stat_week,

sales,

max(sales)over(partition by stat_year,country,stat_week order by sales)

from sale_stat

order by stat_year,stat_week;

select stat_year,

country,

subregion,

stat_week,

sales,

max(sales)over(partition by stat_year,country,stat_week order by sales rows between unbounded preceding and unbounded following)

from sale_stat

order by stat_year,stat_week;

select stat_year,

country,

subregion,

stat_week,

max(sales)over(partition by stat_year,country,stat_week order by sales)

from sale_stat

order by stat_year,stat_week;

--细粒度窗口声明,查询当前周的前一周与后一周共三周时间的销售数据

select stat_year,

country,

subregion,

stat_week,

sales,

max(sales)over(partition by stat_year,country order by stat_week rows between 1 preceding and 1 following)

from sale_stat

order by stat_year,stat_week;

--lag 此函数用于查询跨行引用,如查询当前周的前5周数据

select stat_year,

country,

subregion,

stat_week,

sales,

lag(sales,5)over(partition by stat_year,country order by stat_week)

from sale_stat

order by stat_year,stat_week;

--lead 此函数与lag函数相反,虽也是用于查询跨行引用,但它是查询当前周的后5周数据

select stat_year,

country,

subregion,

stat_week,

sales,

lead(sales,5)over(partition by stat_year,country order by stat_week)

from sale_stat

order by stat_year,stat_week;

--first_value函数,获取分区列中最大值,此函数获取值的方法必须根据排序,它只取排序后的第一条值,last_value函数与之意义一样

select stat_year,

country,

subregion,

stat_week,

sales,

first_value(sales)over(partition by stat_year,country order by sales desc rows between unbounded preceding and unbounded following) as max_sales,

first_value(stat_year)over(partition by stat_year,country order by sales desc rows between unbounded preceding and unbounded following) as year

from sale_stat

order by stat_year,stat_week;

--rank函数,计算排名,当两个值相等将具有同样的排名,下一个排名将会跳过.如要应用连续排名须使用dense_rank函数

select * from (

select stat_year,

country,

subregion,

stat_week,

sales,

rank()over(partition by stat_year,country order by sales desc ) as rank_sales

from sale_stat

)/*where rank_sales<5*/;

--row_number函数,此函数是一个非确定性函数,功能与rank/dense_rank,但这两个函数是确定性函数,排名不会更改