ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用分析函数实现累加和移动平均

使用分析函数实现累加和移动平均

原创 Linux操作系统 作者:redhouser 时间:2011-06-02 15:50:26 0 删除 编辑

--测试表
create table users
as
select sysdate-1000+dbms_random.value*1000 as crt_date
from dual
connect by level<10000;

--累积
SELECT crt_date,
       cnt,
       SUM(cnt) over(ORDER BY crt_date RANGE unbounded preceding)
  FROM (SELECT trunc(crt_date, 'DD') crt_date, COUNT(*) cnt
          FROM users
         GROUP BY trunc(crt_date, 'DD'))
 ORDER BY crt_date;


--自然周平均
--trunc(date,'D'),date所在周的第一天(周日)
with v as(select trunc(to_date('20000101', 'YYYYMMDD'),'D') t0 from dual)
SELECT crt_date,
       cnt,
       round((crt_date - v.t0) / 7) w,
       AVG(cnt) over(PARTITION BY(round((crt_date - v.t0) / 7)))
  FROM (SELECT trunc(crt_date, 'DD') crt_date, COUNT(*) cnt
          FROM users
         GROUP BY trunc(crt_date, 'DD')),v
 ORDER BY 1;

--7天为周期的移动平均
--如果日期不连续,使用rows between 有问题,应该使用 range between
SELECT crt_date,
       cnt,
       AVG(cnt) over(ORDER BY crt_date RANGE BETWEEN 3 preceding AND 3 following)
  FROM (SELECT trunc(crt_date, 'DD') crt_date, COUNT(*) cnt
          FROM users
         GROUP BY trunc(crt_date, 'DD'))
 ORDER BY 1;

--自然月平均
SELECT crt_date, cnt, AVG(cnt) over(PARTITION BY trunc(crt_date, 'MM'))
  FROM (SELECT trunc(crt_date, 'DD') crt_date, COUNT(*) cnt
          FROM users
         GROUP BY trunc(crt_date, 'DD'))
 ORDER BY 1;

--半年移动平均
SELECT crt_date,
       cnt,
       AVG(cnt) over(ORDER BY crt_date RANGE BETWEEN(crt_date - add_months(crt_date, -3)) preceding AND (add_months(crt_date, 3) - crt_date) following)
  FROM (SELECT trunc(crt_date, 'DD') crt_date, COUNT(*) cnt
          FROM users
         GROUP BY trunc(crt_date, 'DD'))
 ORDER BY 1;

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    809956