场景:
IOT设备存在离线与恢复时间记录,每一次离线和恢复记为一个周期即一条数据, 现在需要统计出在某个时段存在离线记录的数据,如果目前未恢复,没有恢复时间,恢复时间置为9999-01-01 00:00:00
原始数据如下(t_offline_record):
id | device_id | offline_time | recover_time |
---|---|---|---|
1 | device_1 | 2022-10-10 10:00:00 | 2022-10-10 20:00:00 |
2 | device_1 | 2022-10-11 00:00:00 | 9999-01-01 00:00:00 |
需求一:
查询出某个时段存在离线的记录(注意不能只用offline_time来做判断,因为就id=1的数据而言,离线时间到恢复时间之间的时间段都属于离线阶段)。如查询2022-10-10 15:00:00 - 2022-10-10 23:00:00 之间存在离线的记录,需要命中id=1的数据
方法:可以参考拉链表查询方式,即:
select * from t_offline_record where recover_time >= '2022-10-10 15:00:00' and offline_time <= '2022-10-10 23:00:00'
能够查询出经过了一个时间段的所有离线数据,开始时间与恢复时间比较,离线时间与截止时间比较
需求二:
希望在上面查询出的离线记录中,只保留上班时段的离线记录,如早上8点到晚上20点区间内有离线的数据。
因为小时段存在跨天的情况,所以并不能单纯使用拉链表查询逻辑,但在同一天内是可以的,因此将它分为三个逻辑:
- 离线和恢复时间为同一天 - 一天内使用拉链表查询方式,没有跨天影响
- 离线和恢复时间差超过24小时 - 超过24小时的不管哪个时段肯定都经历过了
- 离线和恢复时间相差一天 - 即是离线时间在前一天,恢复时间在后一天,因此离线时间与恢复时间任意一个在8-20点之间就算命中
select * from t_offline_record where recover_time >= '2022-10-10 15:00:00' and offline_time <= '2022-10-10 23:00:00'
and (
#同一天
(
TO_DAYS(offline_time)=TO_DAYS(recover_time)
and
hour(recover_time)>=8 and hour(offline_time)<=20
)
or
#超过24小时
(
TIMESTAMPDIFF(HOUR, offline_time, recover_time)>=24
)
or
#相差一天
(
TO_DAYS(offline_time) != TO_DAYS(recover_time)
and
TIMESTAMPDIFF(HOUR, offline_time, recover_time)< 24
and
(
(hour(offline_time) between 8 and 20)
or
(hour(recover_time) between 8 and 20)
)
)
)
性能上算是废了,但是函数计算部份可以通过预先计算的形式获得,避免在查询过程中使用函数
例子:
在时间区间2022-10-10 15:00:00 至 2022-10-10 23:00:00之间的上班时段8点-20之间经历过离线的数据
原始数据及使用上面的SQL查询命中如下:
id | device_id | offline_time | recover_time | 查询命中 |
---|---|---|---|---|
1 | device_1 | 2022-10-10 10:00:00 | 2022-10-10 20:00:00 | √ |
2 | device_2 | 2022-10-10 05:00:00 | 2022-10-10 07:00:00 | - |
3 | device_3 | 2022-10-10 10:00:00 | 2022-10-11 07:00:00 | √ |
4 | device_4 | 2022-10-10 10:00:00 | 2022-10-12 07:00:00 | √ |
5 | device_5 | 2022-10-10 21:00:00 | 2022-10-11 07:00:00 | - |
6 | device_6 | 2022-10-10 21:00:00 | 9999-01-01 00:00:00 | √ |