分析函数之窗口函数

 

分析函数之窗口函数  

 

当统计量随着当前记录变化时，就需要用到窗口函数了

特殊情况： select sum (c1) over() from  加的所有的和；

          select  sum(c1) over(order by c2) from  与

          over ( ORDER BY site_id ROWS BETWEEN UNBOUNDED PRECEDING  AND UNBOUNDED FOLLOWING)  相同

一、对行数规则的结果集，窗口仅涉及行数的移动

1、几种情况

     SUM((cnt)) over(ORDER BY site_id

      rows BETWEEN UNBOUNDED preceding AND UNBOUNDED following) AS total

      行      范围 为                 无限向前                                无限向后

      rows BETWEEN  1  preceding AND  n  following) AS total

      行        范围为           当前行前一行，当前行后n 行

       rows BETWEEN   0  preceding AND  0  following) AS total

       行        范围为         只统计当前行

        rows BETWEEN   1  preceding AND  current row) AS total

         行        范围为            当前行前一行和本行

2、例子

       insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (1, 10);

insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (2, 4);

insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (3, 2);

insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (4, 2);

insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (5, 4);

insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (6, 19);

insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (7, 4);

insert into test2 (SITE_ID, SALE_LOTTERY_CNT)
values (8, 19);

a、计算每个销售点销量占所有销量的比例
SELECT site_id,SUM(sale_lottery_cnt), 100*SUM(sale_lottery_cnt)/
       SUM(SUM(sale_lottery_cnt)) over(ORDER BY site_id rows BETWEEN UNBOUNDED preceding AND UNBOUNDED following)||'%' AS Percent_
  FROM test2
 GROUP BY site_id;

1 10 15.625%
2 4 6.25%
3 2 3.125%
4 2 3.125%
5 4 6.25%
6 19 29.6875%
7 4 6.25%
8 19 29.6875%

b、按销售点id排列，计算每个销售点销量、小于等于此销售点号的总销量

SELECT site_id,SUM(sale_lottery_cnt),
       SUM(SUM(sale_lottery_cnt)) over(ORDER BY site_id rows BETWEEN UNBOUNDED preceding AND 0 following) AS total
  FROM test2
 GROUP BY site_id; 

1 10 10
2 4 14
3 2 16
4 2 18
5 4 22
6 19 41
7 4 45
8 19 64

二、对行数不规则的结果集，窗口也可根据日期移动

 比如求5日内的平均值

 avg（c1) over( over by trunc(c2_day) range  between   interval '2' day preceding  and  interval '2' day following)