读取测量装置的所有最新测量值

Question

我有张桌子看起来是这样的：

id | date             | device_id | measurement_id | value
---+------------------+-----------+----------------+------
1  | 2020-02-24 20:00 | 3         | 1              | 7.9
2  | 2020-02-24 20:00 | 3         | 2              | 3.2
3  | 2020-02-24 20:00 | 3         | 3              | 5.12
4  | 2020-02-24 20:00 | 1         | 1              | 7.9
5  | 2020-02-24 20:00 | 1         | 2              | 3.2
6  | 2020-02-24 20:00 | 1         | 3              | 5.12
7  | 2020-02-24 20:10 | 1         | 1              | 15.2
8  | 2020-02-24 20:10 | 1         | 2              | 5.8
9  | 2020-02-24 20:10 | 1         | 3              | 9.1
10 | 2020-02-24 20:10 | 2         | 1              | 4.6
11 | 2020-02-24 20:10 | 2         | 2              | 2.3
12 | 2020-02-24 20:20 | 1         | 4              | 45.1

现在，我想阅读每个measurement_id的最新值，具体取决于device_id。因此，在本例中(对于device_id 1)：

id | date             | device_id | measurement_id | value
---+------------------+-----------+----------------+------
7  | 2020-02-24 20:10 | 1         | 1              | 15.2
8  | 2020-02-24 20:10 | 1         | 2              | 5.8
9  | 2020-02-24 20:10 | 1         | 3              | 9.1
12 | 2020-02-24 20:20 | 1         | 4              | 45.1

如何在Azure Server上使用T来实现这一点呢？

Answer 1

对于正确的索引，关联子查询通常具有最佳性能：

select t.*
from t
where t.device = 1 and
      t.date = (select max(t2.date) 
                from t t2 
                where t2.device_id = t.device_id and
                      t2.measurement_id = t.measurement_id
               );

您想要的索引在(device_id, measurement_id, date)上。

规范的解决方案使用row_number()，并且几乎同样工作。

Answer 2

您可以使用row_number函数获取每个设备ID和度量ID的最新记录：

select a.*
from
(select *,
       row_number() over(partition by device_id,measurement_id order by date desc) as rownum
from table) a
where a.rownum=1

Answer 3

试试看，没试过.

select * 
from my_table mt1 
where mt1.date = (select max(mt2.date) 
                   from my_table mt2 
                   where mt2.device_id = mt1.device_id 
                   and  mt2.measurement_id = mt1.measurement_id
                   )