oracle-开窗函数-求最近7天平均值

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_sumFROM 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 BYt.dt,t.index_subjuct,t.index_id,t.index_name,t.index_provider,index_date_range