sql - 对组中上次更新的数据求和

Question

因此，我们有一个表，用于存储几个房间每小时的预测噪音水平以及预测的时间。

但是这个预测每天可能会改变几次，当这种情况发生时，我们会向表中添加一个新行，以保留历史记录，所以我们有这样的东西:

+----+----+-------+--------+------+-------+---------------------+
| room_id | reference_date | hour | noise |     update_time     |
+----+----+-------+--------+------+-------+---------------------+
|    1    |   2019-06-13   |   8  |    0  | 2019-06-13 07:15:23 |
|    1    |   2019-06-13   |   9  |    5  | 2019-06-13 07:15:23 |
|    2    |   2019-06-13   |   8  |    2  | 2019-06-13 07:15:23 |
|    1    |   2019-06-13   |   8  |    5  | 2019-06-13 08:15:23 |
|    1    |   2019-06-13   |   9  |    5  | 2019-06-13 08:15:23 |
+---------+--------+--------+-----+-------+---------------------+

现在我们想要对全天的预测噪音级别进行求和，但仅使用最后一个 update_time，否则一天中同一小时将有多个条目，而不是只有一个。

我仅尝试了几个示例，但无法使其与单个查询一起使用，我们使用的是 SQL Server 14.0.3030.27。我可以用代码来完成，但我相信有更好的解决方案。

查询结果应该是这样的

+----+----+-------+--------+------+-------+----+
| room_id | reference_date | total_daily_noise |
+----+----+-------+--------+-------------------+
|    1    |   2019-06-13   |        10         |
+---------+--------+-------+-------------------+

是否可以使用单个查询来完成？

提前感谢您的帮助!

Answer 1

row_number() 是获取每小时最新预测的一种方法:

select room_id, reference_date, sum(noise) as noise
from (select t.*,
             row_number() over (partition by room_id, reference_date, hour order by update_time desc) as seqnum
      from t
     ) t
where seqnum = 1
group by room_id;

另一种方法使用相关子查询:

select room_id, reference_date, sum(noise) as noise
from t
where t.update_time = (select max(t2.update_time)
                       from t t2
                       where t2.room_id = t.room_id and
                             t2.reference_date = t.reference_date and
                             t2.hour = t.hour
                      )
group by room_id;