Oracle窗口函数基础知识学习

Oracle窗口函数基础知识学习

1,测试环境：
SQL> create table win_order(
    month number(2),
    total_sales number);

 2,输入数据:
insert into win_order values(1,623141);
insert into win_order values(2,423124);
insert into win_order values(3,323214);
insert into win_order values(4,212314);
insert into win_order values(5,654314);
insert into win_order values(6,122134);
insert into win_order values(7,859234);
insert into win_order values(8,752314);
insert into win_order values(9,365314);
insert into win_order values(10,265314);
insert into win_order values(11,563114);
insert into win_order values(12,595314);

 3,测试语句:

      我们前面使用了sum(sum(sal)) over (partition by deptno) 来统计每个部门的总额。现在我们要统计的不单是每个部门，而是所有分区，partition by region_id在这里不起作用了。

Oracle为这种情况提供了一个子句：rows between ... preceding and ... following。从字面上猜测它的意思是：在XXX之前和XXX之后的所有记录，实际情况如何让我们通过示例来验证：

SQL> select month,
            sum(total_sales) month_sales,
            sum(sum(total_sales))over (order by month
            rows between unbounded preceding and unbounded following) total_sale
        from win_order group by month;
 
MONTH MONTH_SALES TOTAL_SALE
----- ----------- ----------
    1      623141    5758845
    2      423124    5758845
    3      323214    5758845
    4      212314    5758845
    5      654314    5758845
    6      122134    5758845
    7      859234    5758845
    8      752314    5758845
    9      365314    5758845
  10      265314    5758845
  11      563114    5758845
  12      595314    5758845
 
12 rows selected

高亮处的代码在这里发挥了关键作用，它告诉oracle统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次，这时相当费时的！但至少我们解决了问题。

    unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢？看看下面的结果：

SQL> select month,
              sum(total_sales) month_sales,
              sum(sum(total_sales))over (order by month
              rows between 1 preceding and unbounded following) total_sale
          from win_order group by month ;

MONTH MONTH_SALES TOTAL_SALE
----- ----------- ----------
    1      623141    5758845
    2      423124    5758845
    3      323214    5135704
    4      212314    4712580
    5      654314    4389366
    6      122134    4177052
    7      859234    3522738
    8      752314    3400604
    9      365314    2541370
  10      265314    1789056
  11      563114    1423742
  12      595314    1158428
 
12 rows selected
    很明显这个语句错了。实际1在这里不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。