假设我们有一个关于扫描不同包的设备的下表。如何使用MySQL(版本8(获取每个完整的scan_package事件持续时间。为了考虑有效的对scan_package事件持续时间;"开始";应该发生在";结束";动作,并且两个动作都应该使用相同的设备,并且我们只记录";"开始-结束">一次。(参见下表(
CREATE TABLE record (
device VARCHAR(100),
eventTime datetime,
event VARCHAR(100),
actions VARCHAR(100)
);
INSERT INTO record VALUES
( 'device1' , '2020-02-01 10:05:25', 'scan_package' , 'end'),
( 'device1' , '2020-02-01 10:10:25', 'scan_package' , 'start'),
( 'device1' , '2020-02-01 10:25:25', 'scan_package' , 'end'),
( 'device1' , '2020-02-01 10:30:25', 'scan_package' , 'start'),
( 'device1' , '2020-02-01 10:35:25', 'scan_package' , 'end'),
( 'device2' , '2020-02-01 10:40:25', 'scan_package' , 'end'),
( 'device2' , '2020-02-01 10:42:25', 'scan_package' , 'start'),
( 'device2' , '2020-02-01 10:47:25', 'scan_package' , 'end'),
( 'device2' , '2020-02-01 10:51:25', 'scan_package' , 'start'),
( 'device2' , '2020-02-01 10:53:25', 'scan_package' , 'end'),
( 'device3' , '2020-02-02 08:45:25', 'scan_package' , 'start');
有效输出应该如下所示:
设备 | eventTime1 | action1eventTime2 | action2 | scan_duration_in_mins||
---|---|---|---|---|---|
设备1 | 2020-02-01 10:10:25 | 扫描包开始 | 结束 | 15 | |
设备1 | 2020-02-01 10:30:25 | 扫描包 | 开始2020:02-01 10:35:25 | 结束 | 5|
设备2 | 2020-02-01 10:42:25 | scan_package开始 | 2020-02-02 10:47:25 | 结束 | 5 |
设备2 | 2020-02-01 10:51:25 | 扫描包 | 开始2020-02-02 10:53:25 | 结束2
这个问题有两个选项:
为每对添加一个唯一的键/索引,因此过滤将变得简单明了。如果您添加一个额外的列"start_time",也就足够了,您可以在其中插入每个"end"记录的原始开始时间。
增加一些额外的知识。这意味着你必须改进你的过滤器;结束";具有最低时间戳AND时间戳的记录大于";"开始";记录否则,它取决于DB,您将获得哪条记录(很可能是最先插入的记录,但这并不能保证(。
第二个选项需要一个";相关子查询";,这要慢得多。
我已经更新了你的小提琴:https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=12762f164b72a06849fd66d823d40ebe
这适用于小型数据库。但如果你有很多记录,这可能会变得非常缓慢。