Compare value to previous n values in dataset

Question

I would like to compare a value in a dataset to an average of n values recorded before that data point. I'm using SQL server.

So far I have this code. However this gives the average values for the latest 3 points (even if they were after that record) rather than the 3 points before that record for that parameter

SELECT DISTINCT data.batch, data.parameter, data.recorddate, data.value, 
   ( SELECT Avg(Val) 
FROM (SELECT TOP 3 data.value as Val FROM dbo.data data
ORDER BY data.recorddate DESC ) t ) as AvVal 
FROM  dbo.data data
ORDER BY data.parameter

Desired output:

Batch Parameter RecordDate  Value   AvVal       Difference
A           X   20/02/2018  10.02       
B           X   21/02/2018  9.98        
D           X   22/02/2018  9.98        
C           X   23/02/2018  10.02   9.993333333 0.026666667
F           X   24/02/2018  10.01   9.993333333 0.016666667
E           X   25/02/2018  10.02   10.00333333 0.016666667
G           X   26/02/2018  10.02   10.01666667 0.003333333
H           X   27/02/2018  10.02   10.01666667 0.003333333
J           X   28/02/2018  10.01   10.02       -0.01
I           X   01/03/2018  10.01   10.01666667 -0.006666667
K           X   02/03/2018  10.01   10.01333333 -0.003333333
K           X   03/03/2018  10.01   10.01       0
L           X   04/03/2018  10      10.01       -0.01
M           X   05/03/2018  10      10.00666667 -0.006666667
N           X   06/03/2018  10      10.00333333 -0.003333333
D           Y   22/02/2018  6.31        
B           Y   22/02/2018  6.31        
A           Y   23/02/2018  6.25        
A           Y   24/02/2018  6.25    6.29            -0.04
D           Z   21/02/2018  220.3

Any help much appreciated!

Hi Tab Alleman. thanks for pointing this out. I'll check if it answers my question. — AnnaLouise, Feb 14 '19 at 15:34

score 0 · Accepted Answer · answered Feb 14 '19 at 15:29

You would use window functions:

SELECT d.batch, d.parameter, d.recorddate, d.value,
       AVG(Val) OVER (PARTITION BY parameter ORDER BY d.recorddate ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING),
       (val - AVG(Val) OVER (PARTITION BY parameter ORDER BY d.recorddate ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)) as diff
FROM  dbo.data data d
ORDER BY data.parameter;

Note: This will calculate the average even when there are not enough records. If you don't like that idea, you can use a case expression to limit the calculation to rows that have at least 3 preceding rows.

Hi Gordon. Thanks for your feedback. This works great with the Case expression added in. — AnnaLouise, Feb 22 '19 at 13:10

Compare value to previous n values in dataset

1 Answers1