如何根据 ID 和日期获取一段时间内的价格 STDEV

Question

我正在测试下面的代码，我看到了一些奇怪的结果。

INSERT INTO VOL_TABLE
SELECT * FROM (
SELECT ID, AsOfDate, 
STDEV(Price) OVER (PARTITION BY  AsOfDate ORDER BY AsOfDate DESC ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) As Vol30Days,
STDEV(Price) OVER (PARTITION BY  AsOfDate ORDER BY AsOfDate DESC ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) As Vol60Days,
STDEV(Price) OVER (PARTITION BY  AsOfDate ORDER BY AsOfDate DESC ROWS BETWEEN 90 PRECEDING AND CURRENT ROW) As Vol90Days
FROM Price_Final
Where LEN(ID) > 1
And AsOfDate Is Not Null) x

我的结果是这样的。

ID  AsOfDate    Vol30Days   Vol60Days   Vol90Days
982781  7/7/2019    NULL        NULL        NULL
982781  7/8/2019    4.65        4.65        4.65
982781  7/9/2019    7.71        7.71        7.71
982781  7/10/2019   8.48        8.48        8.48
982781  7/11/2019   8.66        8.66        8.66
982781  7/12/2019   NULL        NULL        NULL
982781  7/13/2019   8.71        8.71        8.71
982781  7/14/2019   7.4         7.4         7.4
982781  7/16/2019   4.58        4.58        4.58

回到过去，不应该有任何空值，直到我从开始回到 30、60 和 90 天。 此外，这些数字看起来完全错误。 如果我在 Excel 中做同样的事情，我会得到这个。

ID      Price   AsOfDate    30DayVol    60DayVol    90DayVol
982781  117.663 7/6/2019    1.980175065 1.888013878 1.710750597
982781  117.565 7/7/2019    1.946177746 1.869405011 1.705288536
982781  119.323 7/8/2019    1.948570344 1.887679003 1.716398812
982781  119.382 7/9/2019    1.951828758 1.890564357 1.728941808
982781  119.508 7/10/2019   1.953349544 1.897933537 1.742350047
982781  119.604 7/11/2019   1.953751571 1.908002077 1.756114814
982781  115.665 7/12/2019   1.94839382  1.915523148 1.759252528
982781  117.786 7/13/2019   1.916545144 1.910871414 1.754831661
982781  117.943 7/14/2019   1.876038031 1.899600919 1.754410265
982781  115.397 7/16/2019   1.863831084 1.907504139 1.756518442

我猜问题来自分区，但我不确定。 我在这里做错了什么？

Answer 1

您的partition by为每个asofdate创建一个单独的分组。 这意味着分组中只有一个日期，order by无关紧要。

我猜你真的想要order by. 如果您想要前几天，请不要将DESC用于ORDER BY：

SELECT ID, AsOfDate, 
STDEV(Price) OVER (ORDER BY AsOfDate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) As Vol30Days,
STDEV(Price) OVER (ORDER BY AsOfDate ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) As Vol60Days,
STDEV(Price) OVER (ORDER BY AsOfDate ROWS BETWEEN 90 PRECEDING AND CURRENT ROW) As Vol90Days
FROM Price_Final
WHERE LEN(ID) > 1;

这假设数据中每天有一个价格。 如果您有多个产品(如id建议的那样(，您可能真的想要PARTITION BY id ORDER BY AsOfDate(。