分析函数之窗口子句

关于分析函数，可能大家基本都是从row_number()开始了解到的。分析函数的使用在某种程度上可以避免自连接，使得原本较为繁琐复杂的查询一下子变得精简起来。
分析函数分为分区子句，排序子句，和窗口子句，对于窗口子句来说，可能开始比较难懂，这部分的使用也尤为重要。
还是先举个例子，然后基于例子再来简单分析一下分析函数。
我们创建一个测试表sales_fact
create table sales_fact(
product varchar2(200) not null,
country varchar2(100),
region varchar2(100),
year number,
week number,
sale number(10,2)
);

然后使用以下的pl/sql插入一部分数据，我们来针对美国的牛肉贸易来做一个简单的分析。
declare
tmp_sql varchar2(1000);
begin
for tmp_year in 2012..2014 loop
for i in 1..50 loop
insert into sales_fact values('BEEF','USA','NOUTH',tmp_year,i,abs(mod(dbms_random.random,12)*100));
end loop;
end loop;
end;
/

使用分析函数中的sum,这个sum和平时使用的sum还是有很大的不同。这个sum会按照年份来统计自1月份到当前月份的销售额。比如2012年2月的累计销售额就是100+400=500

####### sum #############
select year,week,sale,
sum(sale) over(
               partition by product,country,region,year
               order by week
               rows between unbounded preceding and current row
               ) running_sum_ytd
from sales_fact
where country='USA' and product='BEEF' and year in (2012,2013)
order by product,country,year,week;
      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      2012          1        400             400
      2012          2        100             500
      2012          3        600            1100
      2012          4        100            1200
      2012          5        200            1400
      2012          6          0            1400
      2012          7        100            1500
      2012          8        600            2100
      2012          9        300            2400
      2012         10        700            3100
      2012         11        400            3500
......
      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      2012         45        300           22900
      2012         46        900           23800
      2012         47        300           24100
      2012         48        800           24900
      2012         49        400           25300
      2012         50        100           25400
      2013          1        100             100
      2013          2        600             700
      2013          3        800            1500
      2013          4       1000            2500
      2013          5       1000            3500

对于上面的查询我们只修改一处。把rows between unbounded preceding and current row修改为rows between unbounded preceding and unbounded following
输出结果会大大不同。原因在于rows between unbounded preceding and current row是一种窗口函数，是相关分析函数的默认值，如果知道那个为unbounded following就会统计自1月份到12月份的销售额。

select year,week,sale,
sum(sale) over(
               partition by product,country,region,year
               order by week
               rows between unbounded preceding and unbounded following
               ) running_sum_ytd
from sales_fact
where country='USA' and product='BEEF'  and year in (2012,2013)
order by product,country,year,week;

      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      2012          1        400           25400
      2012          2        100           25400
      2012          3        600           25400
      2012          4        100           25400
      2012          5        200           25400
      2012          6          0           25400
      2012          7        100           25400
      2012          8        600           25400
      2012          9        300           25400
      2012         10        700           25400
      2012         11        400           25400
...
      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      2012         45        300           25400
      2012         46        900           25400
      2012         47        300           25400
      2012         48        800           25400
      2012         49        400           25400
      2012         50        100           25400
      2013          1        100           28700
      2013          2        600           28700
      2013          3        800           28700
      2013          4       1000           28700
      2013          5       1000           28700

对于max的使用，情况也是类似。我们可以根据需要来选择数据的范围来得到最大值。
####### max ############
select year,week,sale,
max(sale) over(
               partition by product,country,region,year
               order by week
               rows between unbounded preceding and unbounded following
               ) max_sale
from sales_fact
where country='USA' and product='BEEF' and year in (2012,2013)
order by product,country,year,week;

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2012          1        400       1100
      2012          2        100       1100
      2012          3        600       1100
      2012          4        100       1100
      2012          5        200       1100
      2012          6          0       1100
      2012          7        100       1100
      2012          8        600       1100
      2012          9        300       1100
      2012         10        700       1100
      2012         11        400       1100
...
      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2012         45        300       1100
      2012         46        900       1100
      2012         47        300       1100
      2012         48        800       1100
      2012         49        400       1100
      2012         50        100       1100
      2013          1        100       1100
      2013          2        600       1100
      2013          3        800       1100
      2013          4       1000       1100
      2013          5       1000       1100

比如2012年第1周的销售额是400，最高销售额是的当年的1100.
      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2012          1        400       1100

再来看看另外一个Max的使用。不同之处在于窗口函数的部分。
select year,week,sale,
max(sale) over(
               partition by product,country,region,year
               order by week
               rows between unbounded preceding and current row
               ) max_sale
from sales_fact
where country='USA' and product='BEEF'  and year in (2012,2013)
order by product,country,year,week;

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2012          1        400        400
      2012          2        100        400
      2012          3        600        600
      2012          4        100        600
      2012          5        200        600
      2012          6          0        600
      2012          7        100        600
      2012          8        600        600
      2012          9        300        600
      2012         10        700        700
      2012         11        400        700
...
      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2012         45        300       1100
      2012         46        900       1100
      2012         47        300       1100
      2012         48        800       1100
      2012         49        400       1100
      2012         50        100       1100
      2013          1        100        100
      2013          2        600        600
      2013          3        800        800
      2013          4       1000       1000
      2013          5       1000       1000
比如2012年第2周，相比于第2周来说，最高销售额是第1周的400。第3周的时候相比第1周，第2周，最高销售额是第3周的600.

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2012          1        400        400
      2012          2        100        400
      2012          3        600        600
从实际的使用角度来说，使用rows between unbounded preceding and current row 得到的数据是截止到指定时间的最大值，而rows between unbounded preceding and unbounded following得到的是历史数据最大值。

对于窗口函数的使用不限于此，我们还可以指定更细粒度的数据区间。
像rows between 2 preceding and 2 following 比较的数据就是当前行的前2行和后2行对应的区间的数据。
select year,week,sale,
max(sale) over(
               partition by product,country,region,year
               order by week
               rows between 2 preceding and 2 following
               ) max_sale
from sales_fact
where country='USA' and product='BEEF'  and year in (2012,2013)
order by product,country,year,week;

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2012          1        400        600
      2012          2        100        600
      2012          3        600        600
      2012          4        100        600
      2012          5        200        600
      2012          6          0        600
      2012          7        100        600
      2012          8        600        700
      2012          9        300        700
      2012         10        700        700
      2012         11        400        700
...
      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2012         45        300       1000
      2012         46        900       1000
      2012         47        300        900
      2012         48        800        900
      2012         49        400        800
      2012         50        100        800
      2013          1        100        800
      2013          2        600       1000
      2013          3        800       1000
      2013          4       1000       1000
      2013          5       1000       1000

比如说对于2012年第6中，销售额为0，但是在前2周和后2周的区间范围内，销售额最大值为600.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1387871/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1387871/