MYSQL8 时序信号事件触发及持续时长

最新推荐文章于 2025-05-17 16:00:52 发布

AI小白白猫

最新推荐文章于 2025-05-17 16:00:52 发布

阅读量482

点赞数 4

分类专栏：大数据文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/weixin_46449024/article/details/137205335

版权

大数据专栏收录该内容

12 篇文章

订阅专栏

文章描述了如何使用SQL操作（如droptable、createtable和INSERTINTO）在数据库中管理源信号事件表，特别关注设备状态从离线到在线的变化，并通过Kafka和时间戳分析来跟踪和记录信号的持续离线时长。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

源信号事件表（模拟时序数据，kafka，log）：

drop table HB_TBL;

create table HB_TBL(
	ID           VARCHAR(256),
	DEVICE_ID    VARCHAR(256),
	FLAG         INT COMMENT '1:ONLINE, 0:OFFLINE',
	EVENT_TIME   DATETIME,
	primary KEY(ID)
)
;


delete from HB_TBL;
delete from HB_EVENT_SINK_TBL;

insert into HB_TBL
VALUES
	('1','D001',1,'2024-03-31 00:01:00'),
	('2','D001',1,'2024-03-31 00:02:00'),
	('3','D001',0,'2024-03-31 00:03:00'),
	('4','D001',0,'2024-03-31 00:04:00'),
	('5','D001',1,'2024-03-31 00:05:00'), -- 1) resume
	('6','D001',1,'2024-03-31 00:06:00'),
	('7','D001',0,'2024-03-31 00:07:00'),
	('8','D001',1,'2024-03-31 00:08:00'),  -- 1) resume
	('9','D001',1,'2024-03-31 00:09:00'),  -- 1) resume
	('10','D001',0,'2024-03-31 00:10:00')  -- 1) resume
;


select * from HB_TBL;

ID	DEVICE_ID	FLAG	EVENT_TIME
1	D001	1	2024-03-31 00:01:00
2	D001	1	2024-03-31 00:02:00
3	D001	0	2024-03-31 00:03:00
4	D001	0	2024-03-31 00:04:00
5	D001	1	2024-03-31 00:05:00
6	D001	1	2024-03-31 00:06:00
7	D001	0	2024-03-31 00:07:00
8	D001	1	2024-03-31 00:08:00

信号事件表，写入发现offline后再次online的事件：

drop table HB_EVENT_SINK_TBL;

create table HB_EVENT_SINK_TBL(
	EVENT_ID     VARCHAR(256),
	DEVICE_ID    VARCHAR(256),
	STOP_TIME    DATETIME,
	RESUME_TIME  DATETIME,
	primary KEY(EVENT_ID)
)
;


insert into HB_EVENT_SINK_TBL(EVENT_ID,
	DEVICE_ID,
	STOP_TIME,
	RESUME_TIME)
with HB_VW AS(
	select
		ID ,
		DEVICE_ID ,
		FLAG ,
		cast(EVENT_TIME as DATETIME) EVENT_TIME,
		LAG(FLAG) over(partition by DEVICE_ID order by EVENT_TIME) NEXT_FLAG ,
		LAG(EVENT_TIME) over(partition by DEVICE_ID order by EVENT_TIME) NEXT_EVENT_TIME
	from
		HB_TBL 
	order by EVENT_TIME
)
select concat(x.DEVICE_ID,'|',uuid()) EVENT_ID, x.DEVICE_ID, x.NEXT_EVENT_TIME, x.EVENT_TIME 
from HB_VW x 
where x.FLAG = 1 and x.NEXT_FLAG = 0 
;

EVENT_ID	DEVICE_ID	STOP_TIME	RESUME_TIME
D001\|f8aa7800-ef46-11ee-9fa3-70a6ccc6a40c	D001	2024-03-31 00:04:00	2024-03-31 00:05:00
D001\|f8aa9482-ef46-11ee-9fa3-70a6ccc6a40c	D001	2024-03-31 00:07:00	2024-03-31 00:08:00

进一步可以分析出，offline信号的持续时长：

with HB_VW AS(
	select
		ID ,
		DEVICE_ID ,
		FLAG ,
		cast(EVENT_TIME as DATETIME) EVENT_TIME,
		LAG(FLAG) over(partition by DEVICE_ID order by EVENT_TIME) NEXT_FLAG ,
		LAG(EVENT_TIME) over(partition by DEVICE_ID order by EVENT_TIME) NEXT_EVENT_TIME
	from
		HB_TBL 
)
, EVENT_VW01 as (
	select
		concat(x.DEVICE_ID,'|', uuid()) EVENT_ID,
		x.DEVICE_ID,
		x.NEXT_EVENT_TIME last_stop_time,
		x.EVENT_TIME,
	    (select max(event_time) from HB_VW v where v.event_time<x.EVENT_TIME and flag=1) previous_resume_event_time
	from HB_VW x
	where
		x.FLAG = 1 and x.NEXT_FLAG = 0
)
select x.*
,  (select min(event_time) from HB_VW v where v.event_time>x.previous_resume_event_time and flag=0) first_stop_time
,  (select count(1) from HB_VW v where v.event_time>ifnull(x.previous_resume_event_time,'20200101') and v.event_time < x.EVENT_TIME and flag=0) stop_hb_cnt
from EVENT_VW01 x
;

EVENT_ID	DEVICE_ID	last_stop_time	EVENT_TIME	previous_resume_event_time	first_stop_time	stop_hb_cnt
D001\|03ed1716-ef47-11ee-9fa3-70a6ccc6a40c	D001	2024-03-31 00:04:00	2024-03-31 00:05:00	2024-03-31 00:02:00	2024-03-31 00:03:00	2
D001\|03ed17d2-ef47-11ee-9fa3-70a6ccc6a40c	D001	2024-03-31 00:07:00	2024-03-31 00:08:00	2024-03-31 00:06:00	2024-03-31 00:07:00	1