SQL 查询事件表中所有实体的当前状态 [英] SQL Query for current state of all entities in event table

问题描述

我有一个表，其中包含 N 个设备的事件数据，每个设备在给定时间可能会或可能不会向表中添加数据.我需要在表中查询表中每个不同设备的最后状态，以及设备的先前状态.

I have a table that contains event data for N number of devices, each of which may or may not be adding data to the table at a given time. I need to query the table for the last status of each distinct device in table, as well as the prior status of the device.

示例表数据:

id   | device    | event_data  | date_time
________________________________________________
1019 | sensor501 | open        | 2018-01-05T00:05:15Z
1020 | sensor509 | closed      | 2018-01-05T00:05:16Z
1021 | sensor501 | reset       | 2018-01-05T00:05:18Z
1022 | sensor501 | closed      | 2018-01-05T00:05:19Z
1023 | sensor974 | open        | 2018-01-05T00:05:27Z
1024 | sensor729 | closed      | 2018-01-05T00:06:07Z
1025 | sensor729 | reset       | 2018-01-05T00:06:46Z

示例结果数据:

device    | event_data  | prev_event_data | date_time
________________________________________________
sensor501 | closed      | reset           | 2018-01-05T00:05:19Z
sensor509 | closed      | null            | 2018-01-05T00:05:16Z
sensor974 | open        | null            | 2018-01-05T00:05:27Z
sensor729 | reset       | closed          | 2018-01-05T00:06:46Z

您将如何以最有效的方式查询示例表以获取示例结果数据?

How would you, in the most efficient way, query the example table to get the example result data?

推荐答案

这可以通过相关的子查询来完成.首先获取最新的 event_data，然后再获取最新的上一行.

This can be done with correlated sub-queries. First to get the latest event_data and then one more to get the previous row of the latest.

select t1.device,t1.event_data,t1.date_time,
(select t2.event_data from tbl t2 
 where t1.device=t2.device and t1.date_time>t2.date_time 
 order by t2.date_time desc limit 1) as prev_event_data
from tbl t1
where t1.date_time = (select max(t2.date_time)
                      from tbl t2
                      where t1.device=t2.device
                     )

如果您切换到支持窗口函数的 dbms 版本，请使用

In case you switch over to a dbms version that supports window functions, use

select device,event_data,prev_event_data,date_time
from (select t.*
      ,lead(event_data) over(partition by device order by date_time) as next_event_data
      ,lag(event_data) over(partition by device order by date_time) as prev_event_data
      from tbl t
     ) t 
where next_event_data is null -- last row in the group which will be the row with latest date_time

这篇关于SQL 查询事件表中所有实体的当前状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！