分析函数之窗口子句(r4笔记第3天)
关于分析函数，可能大家基本都是从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.