CODEWITHSUNDEEP
sqlsql-server
Matías Odriozola
Matías Odriozola

Reputation: 63

How to query last n months average while grouping by a different field

I have the following table:

deviceid  year  month   value
432       2019  3       2571
432       2019  2       90
432       2019  1       314
432       2018  12      0
432       2018  11      100
437       2019  2       0
437       2019  1       1
437       2018  12      0
437       2018  11      3
437       2018  10      2
437       2018  9       0
437       2018  8       2

I need a query that for each deviceId retrieves the average value for the last 3 months. Eg: 

deviceId  Average
432       991.7
437       0.3

I'm quite sure i have to use partitions or something similar.

Im thinking it should look something like this, but I can't figure out how to get the average value of just the last 3 months.

select deviceId, avg(value) from Table group by deviceid

Upvotes: 0

Views: 113

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

Another option would be to calculate a date from you year and month columns to use for comparison. 

DECLARE @ComparisonDate DATE = '20190301'; 
--Or, perhaps, EOMONTH(GETDATE(),-1) to get the last day of last month?

SELECT 
  deviceId,
  AVG(value) AS AvgValue
FROM
  @t as t
WHERE
  DATEFROMPARTS(year,month,1) >= DATEADD(MONTH,-3,@ComparisonDate)
GROUP BY
  deviceId;

Upvotes: 1

Max Szczurek
Max Szczurek

Reputation: 4334

You can use ROW_NUMBER to figure out which months are the last 3 per deviceid.

Sample Data:

DECLARE @t TABLE (deviceid INT, year INT, month INT, value NUMERIC(12,2))
INSERT INTO @t VALUES 
(432, 2019 , 3 ,2571),
(432, 2019 , 2 ,90  ),
(432, 2019 , 1 ,314 ),
(432, 2018 , 12,0   ),
(432, 2018 , 11,100 ),
(437, 2019 , 2 ,0   ),
(437, 2019 , 1 ,1   ),
(437, 2018 , 12,0   ),
(437, 2018 , 11,3   ),
(437, 2018 , 10,2   ),
(437, 2018 , 9 ,0   ),
(437, 2018 , 8 ,2   )

Query:

;WITH cte AS (
    SELECT deviceid, value, 
        ROW_NUMBER() OVER (PARTITION BY deviceid ORDER BY year DESC, month DESC) rn
    FROM @t
)
SELECT deviceid, AVG(value) average
FROM cte
WHERE rn BETWEEN 1 AND 3
GROUP BY deviceid

Returns:

deviceid    average
432         991.666666
437         0.333333

Upvotes: 1

Related Questions