获取接下来的5行的平均值

debugcn 发表于 Dev

斯特凡·范霍夫（Stefaan Van Hoof）

我现在有这样一个查询，它可以按天计算测量的平均数，我希望每行平均5天后的平均数

哦...我正在使用Sql Server

这是我所拥有的

SELECT Cast(Cast (Datepart(year, Dateadd(minute, (a.quarternumber * 15), 
            '2000-01-01')) 
                   AS 
            VARCHAR(4)) 
            + '-' 
            + Cast (Datepart(month, Dateadd(minute, (a.quarternumber * 15), 
                   '2000-01-01')) 
            AS VARCHAR(4)) 
            + '-' 
            + Cast (Datepart(day, Dateadd(minute, (a.quarternumber * 15), 
            '2000-01-01') 
                   )AS 
            VARCHAR(4)) 
            + ' ' AS DATETIME) AS [TimeStamp], 
       --AVG(a.value) over(order by a.value) as exper, 
       Round(Avg(a.value), 2)  AS Value 
FROM   measurements.archive a 
       INNER JOIN measurements.points p 
               ON a.pointid = p.id 
       INNER JOIN fifthcore..cm_lod_devices ld 
               ON ld.uuid = p.logicaldeviceuuid 
WHERE  ld.id IN (SELECT value 
                 FROM   @LodDeviceIds) 
       AND p.name = @Name 
       AND a.quarternumber BETWEEN @ChartBeginNumber AND @ChartEndNumber 
GROUP  BY Datepart(year, Dateadd(minute, ( a.quarternumber * 15 ), '2000-01-01') 
), 
          Datepart(month, Dateadd(minute, ( a.quarternumber * 15 ), '2000-01-01' 
                          )), 
          Datepart(day, Dateadd(minute, ( a.quarternumber * 15 ), '2000-01-01')) 
ORDER  BY Datepart(day, Dateadd(minute, ( a.quarternumber * 15 ), '2000-01-01'))

我一直在寻找类似的东西，但无法使它正常工作

AVG(y) OVER(ORDER BY x
3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)


Table                             :extra column that would avg the result of next five days 
2014-07-01 00:00:00.000 16.780000   --> 15.8 
2014-07-02 00:00:00.000 15.940000   --> 16 
2014-07-03 00:00:00.000 16.790000
2014-07-04 00:00:00.000 16.790000
2014-07-05 00:00:00.000 16.040000
2014-07-06 00:00:00.000 16.500000
2014-07-07 00:00:00.000 16.790000
2014-07-08 00:00:00.000 16.790000
2014-07-09 00:00:00.000 16.790000

詹姆斯·S

我认为您的问题是您错误地使用了AVG()WithOVER子句。

您正在使用GROUP BY-，因此AVG()在同一查询中，该查询将应用于该组，而不是整个数据集，因此，如果不先执行子查询或CTE，就无法使用SQL SERVER 2012特定的窗口版本。

这是我认为您可以使其工作的方式，但是请注意-我目前尚未进行实际测试。

WITH cte AS (
    SELECT Cast(Cast (Datepart(year, Dateadd(minute, (a.quarternumber * 15), 
                '2000-01-01')) 
                       AS 
                VARCHAR(4)) 
                + '-' 
                + Cast (Datepart(month, Dateadd(minute, (a.quarternumber * 15), 
                       '2000-01-01')) 
                AS VARCHAR(4)) 
                + '-' 
                + Cast (Datepart(day, Dateadd(minute, (a.quarternumber * 15), 
                '2000-01-01') 
                       )AS 
                VARCHAR(4)) 
                + ' ' AS DATETIME) AS [TimeStamp], 
           Round(Avg(a.value), 2)  AS Value
           SUM (a.value) AS ValueSum, -- New item - required for calculating windowed average from group
           COUNT(a.value) AS ValueCount  -- New item - required for calculating windowed average from group
    FROM   measurements.archive a 
           INNER JOIN measurements.points p 
                   ON a.pointid = p.id 
           INNER JOIN fifthcore..cm_lod_devices ld 
                   ON ld.uuid = p.logicaldeviceuuid 
    WHERE  ld.id IN (SELECT value 
                     FROM   @LodDeviceIds) 
           AND p.name = @Name 
           AND a.quarternumber BETWEEN @ChartBeginNumber AND @ChartEndNumber 
    GROUP  BY Datepart(year, Dateadd(minute, ( a.quarternumber * 15 ), '2000-01-01') 
    ), 
              Datepart(month, Dateadd(minute, ( a.quarternumber * 15 ), '2000-01-01' 
                              )), 
              Datepart(day, Dateadd(minute, ( a.quarternumber * 15 ), '2000-01-01')) 
    ORDER  BY Datepart(day, Dateadd(minute, ( a.quarternumber * 15 ), '2000-01-01'))
)
SELECT 
    [TimeStamp],
    Value,
    SUM(ValueSum) OVER (
        ORDER BY [TimeStamp] ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING
    ) /
    SUM(ValueCount) OVER (
        ORDER BY [TimeStamp] ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING
    ) AS ValueOverNext5 
    -- Average = SUM(x) / COUNT(x) = SUM(SUM(group)) / SUM(COUNT(group))
FROM cte

如您所见，我将查询放入一个公用表表达式中，并为SUM和COUNT添加了2个新列。然后，我将窗口化的AVG OVER应用于这些项（通过执行SUM（total）OVER（...）/ SUM（count）OVER（...）-来自CTE的已取平均值的直线AVG是不正确的）

希望这有意义并且可行！

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

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