oracle – 开窗函数 – 求最近7天平均值
SELECT
t.dt
,t.index_subjuct
,t.index_id
,t.index_name
,t.index_provider
,t.index_date_range
,AVG(SUM(t.index_sum)) OVER (PARTITION BY t.index_subjuct,t.index_id,t.index_name,t.index_provider,t.index_date_range ORDER BY TRUNC(TO_DATE(t.dt,’yyyy-mm-dd’))
RANGE BETWEEN INTERVAL ‘6’ DAY preceding AND INTERVAL ‘0’ DAY following ) last_7_days_avg_sum
FROM adm_jd_core_index_d t
WHERE
to_date(dt,’yyyy-mm-dd’) >= to_date(#end_onedate# ,’yyyy-mm-dd’) – 30 AND dt <= #end_onedate#
GROUP BY
t.dt
,t.index_subjuct
,t.index_id
,t.index_name
,t.index_provider
,index_date_range