我在 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.
更多推荐
移动平均线/滚动平均线
发布评论