CODEWITHSUNDEEP
sqlsql-server
Stefaan Van Hoof
Stefaan Van Hoof

Reputation: 85

Get the average over the next 5 rows

I have this Query that Calculates the avg for measurements by day now i would like to have for each row the avg from across the next 5 days from that

oh... I'm using Sql Server

Here is what i have 

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'))

I whas looking into something like this, but just can't get it to work 

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

Upvotes: 1

Views: 1235

Answers (2)

Kevin Cook
Kevin Cook

Reputation: 1932

This is if you are SQL Server 2008 The first part just makes the test data:

DECLARE @MyTable TABLE
(
    MyDate DATETIME,
    Value DECIMAL(19,6)
)

INSERT INTO @MyTable
VALUES
('2014-07-01 00:00:00.000',16.780000),
('2014-07-02 00:00:00.000',15.940000), 
('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);

Now I create a CTE order my data and give it a rownumber.

WITH SortedData AS
(
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY MyDate) RN
    FROM @MyTable
)

Now using my CTE, I get the next 4 rows to avg with.

SELECT 
CAST(t1.MyDate AS DATE) AS MyDate,
t1.Value,
t1.RN,
d.FCNT,
d.FSUM,
d.FAVG
FROM SortedData T1
OUTER APPLY
(
    SELECT 
        SUM(T2.VALUE)  FSUM,
        COUNT(1) FCNT,
        AVG(T2.Value) FAVG
    FROM SortedData T2
    WHERE T2.RN >= T1.RN
        AND T2.RN <= T1.RN + 4
) d
ORDER BY T1.RN

Here is the output:

MyDate  Value   RN  FCNT    FSUM    FAVG
2014-07-01  16.780000   1   5   82.340000   16.468000
2014-07-02  15.940000   2   5   82.060000   16.412000
2014-07-03  16.790000   3   5   82.910000   16.582000
2014-07-04  16.790000   4   5   82.910000   16.582000
2014-07-05  16.040000   5   5   82.910000   16.582000
2014-07-06  16.500000   6   4   66.870000   16.717500
2014-07-07  16.790000   7   3   50.370000   16.790000
2014-07-08  16.790000   8   2   33.580000   16.790000
2014-07-09  16.790000   9   1   16.790000   16.790000

For SQL Server 2012 it could be as easy as this: Lets change up the test data and put some more rows for each date:

DECLARE @MyTable TABLE
(
    MyDate DATETIME,
    Value DECIMAL(19,6)
)

INSERT INTO @MyTable
VALUES
('2014-07-01 00:00:00.000',1.0),
('2014-07-01 00:00:00.000',2.0),
('2014-07-01 00:00:00.000',3.0),
('2014-07-01 00:00:00.000',4.0),
('2014-07-02 00:00:00.000',7.0),
('2014-07-02 00:00:00.000',7.0),
('2014-07-02 00:00:00.000',7.0),
('2014-07-03 00:00:00.000',8.0),
('2014-07-04 00:00:00.000',9.0),
('2014-07-05 00:00:00.000',10.0),
('2014-07-06 00:00:00.000',11.0),
('2014-07-07 00:00:00.000',20.0),
('2014-07-08 00:00:00.000',25.0),
('2014-07-09 00:00:00.000',50.0);

Now lets make our CTE group and AVG the data:

WITH SortedData AS
(
    SELECT
        MyDate,
        AVG(VALUE) DayAvg,
        ROW_NUMBER() OVER (ORDER BY MyDate) RN,
        AVG(AVG(VALUE)) OVER (ORDER BY MyDate ROWS between current row and 4 following) FDAVG
    FROM @MyTable
    GROUP BY MyDate
)

SELECT CAST(sd.MyDate AS DATE) AS MyDate,
    sd.DayAvg,
    sd.RN,
    sd.FDAVG
FROM SortedData sd

Here is the output:

MyDate  DayAvg  RN  FDAVG
2014-07-01  2.500000    1   7.300000
2014-07-02  7.000000    2   9.000000
2014-07-03  8.000000    3   11.600000
2014-07-04  9.000000    4   15.000000
2014-07-05  10.000000   5   23.200000
2014-07-06  11.000000   6   26.500000
2014-07-07  20.000000   7   31.666666
2014-07-08  25.000000   8   37.500000
2014-07-09  50.000000   9   50.000000

Upvotes: 1

James S
James S

Reputation: 3588

I think your problem is that you are using AVG() With the OVER clause incorrectly.

You are using GROUP BY - and therefore AVG() in the same query will apply to the group, not the entire data set, hence you can't use the SQL SERVER 2012 specific windowing version without first doing a subquery or CTE.

Here's how I think you could make it work, but note - I've not actually tested it at this point.

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

As you can see I put your query inside a common table expression, and added 2 new columns for SUM and COUNT. Then I applied the windowed AVG OVER to these items (by doing SUM(total) OVER (...) / SUM(count) OVER (...) - a straight AVG of the already averaged values from the CTE would be incorrect)

Hopefully that makes sense and works!

Upvotes: 2

Related Questions