计算SQL Server中的递归滚动平均值

Rich C 发表于 Dev

富C

我们正在尝试计算滚动平均值，并尝试转换许多SO答案以解决该问题。至此，我们仍然没有成功。

我们尝试过的是：

这是我们考虑过的一些SO答案。

我们的最新尝试是修改此处找到的解决方案之一（＃4）。https://www.red-gate.com/simple-talk/sql/t-sql-programming/calculating-values-within-a-rolling-window-in-transact-sql/

范例：

这是SQL Fiddle中的示例：http ://sqlfiddle.com/#!6/4570a/17

摆弄小提琴，我们仍在尝试使SUM正常工作，但最终我们试图获得平均值。

最终目标

使用Fiddle示例，我们需要找到Value1和CompareValue1之间的差异并将其表示为Diff1。当一行没有可用的Value1时，我们需要通过取最后两个Diff1值的平均值进行估算，然后将其添加到该行的ComparisonValue1中。

使用正确的查询，结果将如下所示：

GroupID  Number  ComparisonValue1  Diff1  Value1
5        10      54.78             2.41   57.19
5        11      55.91             2.62   58.53
5        12      55.93             2.78   58.71
5        13      56.54             2.7    59.24
5        14      56.14             2.74   58.88
5        15      55.57             2.72   58.29
5        16      55.26             2.73   57.99

问题：当有可能将其平均计入以下各行的平均值时，是否可以计算该平均值？

更新：

在Fiddle模式中添加了VIEW，以简化最终查询。
更新了查询，以包括Diff1的新滚动平均值（列Diff1Last2Avg）。直到我们在Value1列中遇到空值之前，此滚动平均值的效果都很好。这是我们需要插入估算值的地方。
更新了查询，以包括没有Value1（列Value1Estimate）时应使用的估计。如果可以在Value1列中使用估算值代替NULL，那么这很好，并且是完美的。由于Diff1列反映了Value1（或其估算值）和CompareValue1之间的差异，因此包括Estimate在内的Diff1中将填充所有NULL值。反过来，这将继续允许计算未来行的估计。在这一点上，它变得令人困惑，但仍在努力解决。有任何想法吗？

亚历克斯

这个想法归功于这个答案：@JesúsLópez的https://stackoverflow.com/a/35152131/6305294

我已在代码中包含注释以对其进行解释。

更新

我已根据评论更正了查询。
我在minuend和subtrahend中交换了数字，以得到作为正数的差。
删除了Diff2Ago列。

现在，查询结果与您的样本输出完全匹配。

;WITH cte AS
(
    -- This is similar to your ItemWithComparison view
    SELECT i.Number, i.Value1, i2.Value1 AS ComparisonValue1,
        -- Calculated Differences; NULL will be returned when i.Value1 is NULL
        CONVERT( DECIMAL( 10, 3 ), i.Value1 - i2.Value1 ) AS Diff
    FROM Item AS i
            LEFT JOIN [Group] AS G ON g.ID = i.GroupID
            LEFT JOIN Item AS i2 ON i2.GroupID = g.ComparisonGroupID AND i2.Number = i.Number
    WHERE NOT i2.Id IS NULL
),
cte2 AS(
    /*
    Start with the first number

    Note if you do not have at least 2 consecutive numbers (in cte) with non-NULL Diff value and therefore Diff1Ago or Diff2Ago are NULL then everything else will not work;
    You may need to add additional logic to handle these cases */
    SELECT TOP 1 -- start with the 1st number (see ORDER BY)
            a.Number, a.Value1, a.ComparisonValue1, a.Diff, b.Diff AS Diff1Ago
    FROM cte AS a
            -- "1 number ago"
            LEFT JOIN cte AS b ON a.Number - 1 = b.Number
    WHERE NOT a.Value1 IS NULL
    ORDER BY a.Number
    UNION ALL
    SELECT b.Number, b.Value1, b.ComparisonValue1,
            ( CASE
                WHEN NOT b.Value1 IS NULL THEN b.Diff
                ELSE CONVERT( DECIMAL( 10, 3 ), ( a.Diff + a.Diff1Ago ) / 2.0 )
            END ) AS Diff,
        a.Diff AS Diff1Ago
    FROM cte2 AS a
        INNER JOIN cte AS b ON a.Number + 1 = b.Number
)
SELECT *, ( CASE WHEN Value1 IS NULL THEN ComparisonValue1 + Diff ELSE Value1 END ) AS NewValue1
FROM cte2 OPTION( MAXRECURSION 0 );

局限性：此解决方案仅在需要考虑少量先前值时才有效。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。