我有一个表(日期、有效负载),其中包含大约 10 年的数据,我想计算移动平均值 (MA),以基于 14 天(2 周)和 90 天(12 周)间隔显示有效负载的趋势
我已经编写了这个查询,但它给了我错误的值
SELECT x.*,
ABS (LTMA-STMA) DIFFERECNE
FROM
(SELECT SDATE,
PAYLOAD,
AVG(PAYLOAD) OVER (ORDER BY W ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) STMA,
AVG(PAYLOAD) OVER (ORDER BY W ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) LTMA
FROM
(SELECT b.*,
TO_NUMBER(TO_CHAR( X.SDATE, 'W')) W
FROM
(SELECT TO_DATE(TO_CHAR(a.SDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') SDATE,
SUM(a.PAYLOAD) PAYLOAD
FROM TABLE_PAYLOAD a
WHERE a.SDATE > sysdate - 3 * 365
GROUP BY TO_CHAR(a.SDATE, 'YYYY-MM-DD')
ORDER BY TO_CHAR(a.SDATE, 'YYYY-MM-DD')
) b )
ORDER BY SDATE
) x;
其实我知道什么MA
是的,但我不明白Oracle是如何工作的!
我可以计算MA
在 Excel 中,但我需要在数据库级别执行此操作,您能告诉我如何执行此操作吗?
我不明白使用的意义是什么TO_CHAR(SDATE, 'W')
?根据文档,这应该为您提供该月的周数......
无论如何,我尝试简化查询,希望它仍然满足您的需求:
SELECT x.*, ABS (LTMA-STMA) DIFFERENCE
FROM
(
SELECT SDATE, PAYLOAD,
AVG(PAYLOAD) OVER (ORDER BY SDATE rows BETWEEN 14 PRECEDING AND CURRENT ROW) STMA,
AVG(PAYLOAD) OVER (ORDER BY SDATE rows BETWEEN 90 PRECEDING AND CURRENT ROW) LTMA
FROM
(
SELECT a.SDATE, SUM(a.PAYLOAD) PAYLOAD
FROM TABLE_PAYLOAD a
WHERE a.SDATE > sysdate - 3 * 365
GROUP BY a.SDATE
)
) x
ORDER BY SDATE;
PS:我也不认为这样做有什么意义TO_DATE(TO_CHAR(a.SDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
,这相当于a.SDATE
...
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)