使用分析函式實現累加和移動平均

--測試表
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;