Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 9 down vote accepted

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:

Results

Execution plan

More information:

Window Functions in SQL Server

Related question:

Date range rolling sum using window functions

share|improve this answer

This is a rolling average, which is a windowed function in SQL Server 2012 and newer. You could solve it like this:

SELECT Product, [Date], Weight,
       (CASE WHEN _runningCount>=4
             THEN _runningTotal/4.0
             END) AS [Average weight]
FROM (
    SELECT Product, [Date], Weight,
           SUM(Weight) OVER (
               PARTITION BY Product
               ORDER BY [Date]
               ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS _runningTotal,
           SUM(1) OVER (
               PARTITION BY Product
               ORDER BY [Date]
               ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS _runningCount
    FROM theTable
) AS sub;

Here are the key points:

  • The OVER () clause describes that the aggregation (SUM in this case) takes place in a window. In our case, we want to aggregate the most recent previous four rows, for the current product (the partition), ordered by the date.
  • SUM(1) acts as a count.
  • For readability, I've put the two window functions in a subquery, sub.
  • Then, if _runningCount is 4 or more, we can divide the running total of the most recent four rows by 4, otherwise, return NULL.

If you want to return the running total at any given time (including the first few dates as well), you would change the CASE to something like:

SELECT ...
       _runningTotal/_runningCount AS [Average weight]
share|improve this answer
    
Thank you! Thanks for explaining the components as well, just recently getting back in to DB after quite awhile, lots has changed :) – Neostim Mar 3 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.