Oracle开发之窗口函数(2)-木庄网络博客

当前第2页返回上一页

     MONTH MONTH_SALES CURRENT_TOTAL_SALES
---------- ----------- -------------------
         1      610697              610697
         2      428676             1039373
         3      637031             1676404
         4      541146             2217550
         5      592935             2810485
         6      501485             3311970
         7      606914             3918884
         8      460520             4379404
         9      392898             4772302
        10      510117             5282419
        11      532889             5815308
        12      492458             6307766

已选择12行。

现在我们能得到滚动的销售总额了！下面这个统计结果看起来更加完美，它展现了所有我们需要的数据：

代码如下:

SQL> select month,
         sum(tot_sales) month_sales,
         sum(sum(tot_sales)) over(order by month
         rows between unbounded preceding and current row) current_total_sales,
         sum(sum(tot_sales)) over(order by month
         rows between unbounded preceding and unbounded following) total_sales
    from orders
   group by month;

     MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
---------- ----------- ------------------- -----------
         1      610697              610697     6307766
         2      428676             1039373     6307766
         3      637031             1676404     6307766
         4      541146             2217550     6307766
         5      592935             2810485     6307766
         6      501485             3311970     6307766
         7      606914             3918884     6307766
         8      460520             4379404     6307766
         9      392898             4772302     6307766
        10      510117             5282419     6307766
        11      532889             5815308     6307766
        12      492458             6307766     6307766

已选择12行。

在一些销售报表中我们会时常看到求平均值的需求，有时可能是针对全年的数据求平均值，有时会是针对截至到当前的所有数据求平均值。很简单，只需要将：
sum(sum(tot_sales))换成avg(sum(tot_sales))即可。

四、窗口函数进阶－根据时间范围统计：

前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个SQL语句就统计了当天销售额和五天内的评价销售额：

代码如下:

select trunc(order_dt) day,
    sum(sale_price) daily_sales,
    avg(sum(sale_price)) over (order by trunc(order_dt)
    range between interval '2' day preceding
    and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null
    and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
    and to_date('31-jul-2001','dd-mon-yyyy')

为了对指定范围进行统计，Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。

五、窗口函数进阶－first_value/last_value：

Oracle提供了2个额外的函数：first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值，这两个函数就可以派上用场了。

代码如下:

select month,
    first_value(sum(tot_sales)) over (order by month
    rows between 1 preceding and 1 following) prev_month,
    sum(tot_sales) monthly_sales,
    last_value(sum(tot_sales)) over (order by month
    rows between 1 preceding and 1 following) next_month,
    avg(sum(tot_sales)) over (order by month
    rows between 1 preceding and 1 following) rolling_avg
from orders
where year = 2001
    and region_id = 6
group by month
order by month;

首先我们来看:rows between 1 preceding and 1 following告诉Oracle在当前记录的前一条、后一条范围内查找并统计，而first_value和last_value在这3条记录中至分别找出第一条、第三条记录，这样我们就轻松地得到相邻三个月的销售记录及平均值了！

六、窗口函数进阶－比较相邻记录：

通过第五部分的学习，我们知道了如何利用窗口函数来显示相邻的记录，现在假如我们想每次显示当月的销售额和上个月的销售额，应该怎么做呢？

从第五部分的介绍我们可以知道，利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了，其实Oracle还有一个更简单的方式让我们来比较2条记录，它就是lag函数。

leg函数类似于preceding和following子句，它能够通过和当前记录的相对位置而被应用，在比较同一个相邻的记录集内两条相邻记录的时候特别有用。

代码如下:

select month,
    sum(tot_sales) monthly_sales,
    lag(sum(tot_sales), 1) over (order by month) prev_month_sales
from orders
where year = 2001
    and region_id = 6
group by month
order by month;

lag(sum(tot_sales),1)中的1表示以1月为基准。

以上就是Oracle窗口函数用法的全部内容，希望能给大家一个参考，也希望大家多多支持。

标签：Oracle

返回前面的内容