--测试表
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;