SQL Server: Average of Last 4 Runs Using Window Functions

aggregatesql serversql-server-2012window functions

I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a product by date it was created, and the average weight of the product for that day:

+---------+---------+--------+
| Product |  Date   | Weight |
+---------+---------+--------+
|  900000 | Jan 1   | 20.0   |
|  900000 | March 3 | 12.2   |
|  900000 | July 6  | 15.0   |
|  900000 | July 7  | 14.0   |
|  900000 | Aug 6   | 3.0    |
|  800000 | June 2  | 14.0   |
|  800000 | June 3  | 12.0   |
+---------+---------+--------+

The end result I'm looking for is to add a column that includes the average Weight for the last 4 dates the product was run for, so something like this:

+---------+---------+--------+----------------+
| Product |  Date   | Weight | Average Weight |
+---------+---------+--------+----------------+
|  900000 | Jan 1   | 20.0   | NULL           |
|  900000 | March 3 | 12.2   | NULL           |
|  900000 | July 6  | 15.0   | NULL           |
|  900000 | July 7  | 14.0   | NULL           |
|  900000 | Aug 6   | 3.0    | 15.3           | Jan1+Mar3+July6+July7/4
|  900000 | Aug 8   | 13.0   | 11.05          | Mar3+July6+July7+Aug6/4
|  800000 | June 2  | 14.0   | NULL           |
|  800000 | June 3  | 12.0   | NULL           |
|  800000 | June 4  | 12.0   | NULL           |
|  800000 | June 5  | 12.0   | NULL           |
|  800000 | June 6  | 12.0   | 12.5           | etc...
+---------+---------+--------+----------------+

The NULL's are just there since in this sample you can't calculate the average over the last 4 runs because the data isn't there.

Could anyone point me in the direction I need to be looking to do something like this?

Best Answer

Sample data:

CREATE TABLE dbo.Thing
(
    Product integer NOT NULL,
    TheDate date NOT NULL,
    TheWeight decimal(5, 1) NOT NULL
);

INSERT dbo.Thing
    (Product, TheDate, TheWeight)
VALUES
    (900000, CONVERT(date, '20160101', 112), 20.0),
    (900000, '20160303', 12.2),
    (900000, '20160706', 15.0),
    (900000, '20160707', 14.0),
    (900000, '20160806', 3.0 ),
    (900000, '20160808', 13.0 ),
    (800000, '20160602', 14.0),
    (800000, '20160603', 12.0),
    (800000, '20160604', 12.0),
    (800000, '20160605', 12.0),
    (800000, '20160606', 12.0);

Solution:

The general idea here is to use the extended window aggregate functions available in SQL Server 2012 and later.

The only wrinkle is that AVG does not return null over a window if it is smaller than the required four rows. To address that, we also calculate the number of rows found in the window using COUNT. A simple CASE expression can then be used to return a null if the window holds fewer than four rows:

SELECT
    T.Product,
    T.TheDate,
    T.TheWeight,
    [Average Weight] =
        CASE
            WHEN
                4 > COUNT_BIG(*) OVER (
                    PARTITION BY T.Product
                    ORDER BY T.Product, T.TheDate
                    ROWS BETWEEN 4 PRECEDING
                    AND 1 PRECEDING
                    )
                THEN NULL
            ELSE
                AVG(T.TheWeight) OVER (
                    PARTITION BY T.Product
                    ORDER BY T.Product, T.TheDate
                    ROWS BETWEEN 4 PRECEDING
                    AND 1 PRECEDING
                    )
        END
FROM dbo.Thing AS T
ORDER BY
    T.Product,
    T.TheDate;

Run the query on Stack Exchange Data Explorer

Output:

More information:

Window Functions in SQL Server

Related question:

Date range rolling sum using window functions

Related Solutions

SQL Server – How to Get Average According to Last Update Time

Assuming your table and sample data look like this:

CREATE TABLE dbo.Item
(
    Item        char(1) NOT NULL,
    RecordTime  datetime2(0) NOT NULL,
    Value       decimal(5,3) NOT NULL,

    CONSTRAINT PK_dbo_Item_Item_RecordTime
        PRIMARY KEY (Item, RecordTime)
);

INSERT dbo.Item
    (Item, RecordTime, Value)
VALUES
    ('A', '2014-12-29T10:13:00', 0.139),
    ('A', '2014-12-29T10:12:00', 0.150),
    ('A', '2014-12-29T10:11:00', 0.113),
    ('B', '2014-12-29T10:10:00', 0.139),
    ('B', '2014-12-29T10:09:00', 0.120),
    ('B', '2014-12-29T10:08:00', 0.084),
    ('C', '2014-12-29T09:55:00', 0.101),
    ('C', '2014-12-29T09:54:00', 0.095),
    ('C', '2014-12-29T09:53:00', 0.129);

One way to write the query is:

SELECT
    MaxTime.Item,
    Average.Value
FROM 
(
    -- Highest record time per item
    SELECT
        I.Item,
        RecordTime = MAX(I.RecordTime)
    FROM dbo.Item AS I
    GROUP BY
        I.Item
) AS MaxTime
CROSS APPLY 
(
    -- Average value within 15 minutes
    SELECT
        Value = CONVERT(decimal(5,3), AVG(I2.Value))
    FROM dbo.Item AS I2
    WHERE
        I2.Item = MaxTime.Item
        AND I2.RecordTime >= DATEADD(MINUTE, -15, MaxTime.RecordTime)
) AS Average
ORDER BY
    MaxTime.Item;

SQLFiddle Demo

Sql-server – Calculating Average Excluding Outliers

A very basic example would be to get the AVG and STDEV of the range of numbers and then exclude any that were more than 1 Standard Deviation from that average.

You then take the average of the new range.

This is quite a basic bit of code (don't forget the CAST to a DECIMAL) which you can expand upon to make it more suitable to your needs.

CREATE TABLE #nums (num INT);

INSERT INTO #nums VALUES (1), (2), (1), (2), (7);

WITH AvgStd AS (
  SELECT
    AVG(num) AS avgnum,
    STDEVP(num) AS stdnum
  FROM #nums AS n  
)
SELECT 
  AVG(CAST(num AS DECIMAL(5,2)))
FROM #nums AS n
CROSS JOIN AvgStd
WHERE num < (avgnum + stdnum)
  AND num > (avgnum - stdnum);

DROP TABLE #nums;