如何使用滞后函数进行STDEV计算？

Question

我正在运行这样的代码：

SELECT ID, Date, Price,
    STDEV(Price) OVER (ORDER BY ID, Date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) As OneMonths,
    STDEV(Price) OVER (ORDER BY ID, Date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) As TwoMonths,
    STDEV(Price) OVER (ORDER BY ID, Date ROWS BETWEEN 90 PRECEDING AND CURRENT ROW) As ThreeMonths
FROM Price_Table

这给了我这个结果。

​

​

在第五行，我总是有三个零的三个方差。这说得通。但是，每次ID更改时，我都必须得到前面ID的价格，因为每次ID更改时，我都会预期标准差会被重置。所以，我认为橙色的第一行应该是零，下一行应该是零，因为价格在第二天没有变化。我尝试将延迟函数包装在STDEV函数的周围，得到了一个错误。我做了相反的尝试，也犯了一个错误。

Answer 1

如果您想要每个id的值，那么您需要partition by

SELECT ID, Date, Price,
       STDEV(Price) OVER (PARTITION BY ID ORDER BY Date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) As OneMonths,
       STDEV(Price) OVER (PARTITION BY ID ORDER BY Date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) As TwoMonths,
       STDEV(Price) OVER (PARTITION BY ID ORDER BY Date ROWS BETWEEN 90 PRECEDING AND CURRENT ROW) As ThreeMonths
FROM Price_Table;