移动平均线/滚动平均线 [英] Moving Average / Rolling Average

问题描述

我在 MS SQL 中有 2 列，其中一个是序列号.其他是价值观.我需要第三列，它为我提供该行中的值和接下来的 2 的总和.

I have 2 columns in MS SQL one is Serial no. and other is values. I need the thrird column which gives me the sum of the value in that row and the next 2.

前

SNo values
1 2
2 3
3 1
4 2
5 6
7 9
8 3
9 2

所以我需要第三列的总和为 2+3+1、3+1+2 等等，所以第 8 行和第 9 行将没有任何值:

So I need third column which has sum of 2+3+1, 3+1+2 and So on, so the 8th and 9th row will not have any values:

1   2   6   
2   3   6   
3   1   4   
4   2   5   
5   1   6   
7   2   7   
8   3       
9   2       

解决方案是否可以通用，以便我可以改变将 3 个数字添加到更大的数字(例如 60)的当前窗口大小.

Can the Solution be generic so that I can Varry the current window size of adding 3 numbers to a bigger number say 60.

推荐答案

这里是SQL Fiddle 演示以下查询:

Here is the SQL Fiddle that demonstrates the following query:

WITH TempS as 
(
  SELECT s.SNo, s.value, 
  ROW_NUMBER() OVER (ORDER BY s.SNo) AS RowNumber
  FROM MyTable AS s
)
SELECT m.SNo, m.value,
(
  SELECT SUM(s.value) 
  FROM TempS AS s
  WHERE RowNumber >= m.RowNumber
  AND RowNumber <= m.RowNumber + 2
) AS Sum3InRow
FROM TempS AS m

在您的问题中，您要求对 3 个连续值求和.您修改了您的问题，说您需要求和的连续记录数可能会改变.在上面的查询中，您只需将 m.RowNumber + 2 更改为您需要的任何内容.

In your question you were asking to sum 3 consecutive values. You modified your question saying the number of consecutive records you need to sum could change. In the above query you simple need to change the m.RowNumber + 2 to what ever you need.

所以如果你需要 60，那么使用

So if you need 60, then use

m.RowNumber + 59

正如您所见，它非常灵活，因为您只需更改一个数字.

As you can see it is very flexible since you only have to change one number.

这篇关于移动平均线/滚动平均线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！