时间戳

首页 > 解决方案 > 如何在按不同字段分组时查询过去 n 个月的平均值

sql - 如何在按不同字段分组时查询过去 n 个月的平均值

问题描述

我有下表：

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

我需要一个查询，为每个 deviceId 检索过去 3 个月的平均值。例如：

deviceId  Average
432       991.7
437       0.3

我很确定我必须使用分区或类似的东西。

我认为它应该看起来像这样，但我不知道如何获得最近 3 个月的平均值。

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

标签： sqlsql-server

解决方案

您可以使用 ROW_NUMBER 来确定每个 deviceid 的最后 3 个月。

样本数据：

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   )

询问：

;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

回报：

deviceid    average
432         991.666666
437         0.333333

推荐阅读