Oracle Analytic Preceding 返回错误/意外结果
编写一个 SQL 查询，该查询应返回之前 91 天窗口的平均费用。我将日期截断为一周的第一天，并期望 91 天的平均值为该日期之前的 91 天。
然而，在验证时，91 天的平均值似乎包括我截断的本周。
查询：
SELECT
week AS WEEK,
to_char(week,'ww-yyyy') AS Week_num
--Sum of the charges for the previous 91 days / 91 will give you avg charges per day for the last 91 days
--Need to count days regardless of if there are any charges on that day
,(SUM(CHARGES) OVER (ORDER BY WEEK RANGE INTERVAL '91' DAY PRECEDING))/91 MV_91_DAY_AVG
FROM
(
SELECT
--Truncate date down to first of week. Goal is to make 91 day preceding being at this date.
TRUNC(TRANS.ORIG_POST_DATE,'WW') AS WEEK
,SUM(TRANS.AMOUNT) AS CHARGES
FROM TRANS
WHERE
TRANS.DETAIL = "Charge"
GROUP BY TRUNC(TRANS.ORIG_POST_DATE,'WW')
)
ORDER BY WEEK
当前输出： 
Writing a SQL query that should return the average charges for a preceding 91 day window. I truncate the date down to the first day of the week and expect the 91 day average to be the 91 days prior to this date.
However when validating it appears that the 91 day average is including the current week that I truncated.
Query:
SELECT
week AS WEEK,
to_char(week,'ww-yyyy') AS Week_num
--Sum of the charges for the previous 91 days / 91 will give you avg charges per day for the last 91 days
--Need to count days regardless of if there are any charges on that day
,(SUM(CHARGES) OVER (ORDER BY WEEK RANGE INTERVAL '91' DAY PRECEDING))/91 MV_91_DAY_AVG
FROM
(
SELECT
--Truncate date down to first of week. Goal is to make 91 day preceding being at this date.
TRUNC(TRANS.ORIG_POST_DATE,'WW') AS WEEK
,SUM(TRANS.AMOUNT) AS CHARGES
FROM TRANS
WHERE
TRANS.DETAIL = "Charge"
GROUP BY TRUNC(TRANS.ORIG_POST_DATE,'WW')
)
ORDER BY WEEK
Current output:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为您希望您的窗口条款是：
为了排除本周的费用。
您可能还需要将“91”更改为“92”，以在开始时添加额外的一周。
I think you want your window clause to be:
in order to exclude the current week's charges.
You may also want to change the '91' to '92' to include an additional week at the beginning.
除法之前先减去当前值？
Subtract the current value before dividing?