移动平均线/滚动平均线

本文介绍了移动平均线/滚动平均线的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在 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.