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

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

• 博文量
211
• 访问量
828794