分析函数

 

一，     窗口函数

1， 计算累加和

执行语句：

select month,sum(amount) as month_amount,

sum(sum(amount)) over

(order by month rows between unbounded preceding and current row) as cumulative_amount

from all_sales where year=2003 group by month order by month;

执行结果：

CUMULATIVE_AMOUNT这一列显示的就是累加后的结果，1月份，前面没有累加的月份，所以还是原值，2月份，就是95525.55+116671.6的值了；同样，三月份，是95525.55+116671.6+160307.92的值。

rows between unbounded preceding and current row指定了窗口的范围：起点为查询结果集的第一行，终点为查询结果集的当前行。

 

2，计算移动平均值。

首先要搞清楚，何为移动平均值。先举个简单例子：

要求下面这个表本月与前三个月之间销售额的移动平均值：

月份      销售额    移动平均值

01                                             100          100

02                                             200      （100+200）/2=150

03                                             300      （100+200+300）/3=200

04                                             400       (400+300+200+100)/4=250

05                                             500      （500+400+300+200）/4=350

因为1到3月份可用的数据都少于3个月，所以他们的值只是基于可以用的月份计算的。

下面我们执行一个语句：

select month,sum(amount) as month_amount,

avg(sum(amount)) over

(order by month rows between 3 preceding and current row) as moving_average

from all_sales where year=2003 group by month order by month;

执行结果：

1月份的移动平均值，就是自己；

2月份的：（95525.55+116671.6）/2；

3月份的：（95525.55+116671.6+160307.92）/3；

4月份的：（95525.55+116671.6+160307.92+175998.8）/4

5月份的：（116671.6+160307.92+175998.8+154349.44）/4

。。。。。。

 

 

3，    计算中心平均值

执行语句：

select month,sum(amount) as month_amount,

avg(sum(amount)) over

(order by month rows between 1 preceding and 1 following ) as moving_average

from all_sales where year=2003 group by month order by month;

与第二个句子相比，改了窗口的范围，窗口的终点不再是current row,而是1 following。执行结果：

 

1月份：（95525.55+116671.6）/2;

2月份：（95525.55+116671.6+160307.92）/3；

3月份：（116671.6+160307.92+175998.8）/3；

。。。。。。

 

 

4,使用last_value()与first_value()函数。

last_value():获取窗口的最后一行数据。

first_value()：获取窗口的第一行数据。

select month,sum(amount) as month_amount,

first_value(sum(amount)) over

(order by month rows between 1 preceding and 1 following) as previous_month_amount,

last_value(sum(amount)) over

(order by month rows between 1 preceding and 1 following ) as next_month_amount

from all_sales where year=2003 group by month order by month;

对于每个月份来说，可以返回其上一个月与下一个月的销售额。

 

二，  使用LAG和LEAD函数。

它们的功能是获取位于距当前行指定距离处的那条记录中的数据，举例说明：

select month,sum(amount) as month_mount,

lag(sum(amount),1) over (order by month ) as previous_month_amount,

lead(sum(amount),1) over (order by month) as next_month_amount from all_sales

where year=2003 group by month order by month;

1表示距当前行有1行的距离，根据后面的ORDER BY 子句，即距离当前月上（下）一个月的销售额。

注：这个地方与前文的last_value(),first_value()很像，都是返回当前月的上一个月和下一个月的销售。但是，这里的1月份，就是第1行，没有前一行，所以前一个月为空，同理，12月份的后一个月也为空。但是，last_value(),first_value()就不同，它是返回的窗口的第一行和最后一行，所以，即使是1月份的前一个月和12月份的后一个月，也是有值返回的。

 

三，  使用first,last函数。

获得一个排序分组中的第一个值和最后一个值。由于值有可能有重复（sum(amount)），所以，要用MAX（）或者MIN（）进行指定唯一month值。(这与ROWID在去重中的用法有点类似)

select min(month) keep (dense_rank first order by sum(amount) desc) as highest_sales_month,

min(month) keep (dense_rank last order by sum(amount) desc) as lowest_sales_month from all_sales

where year=2003 group by month order by month;

其中KEEP的意思是：只保留FIRST/LAST这一行，起的就是保留作用。