【mysql】统计两个相邻任务/事件的间隔时间以及每个任务的平均用时

准备

• 数据：任务/事件日志。
• 查询工具：Navicat

步骤

1. 设置查询参数部分

1.1 设置需要分析的起始时间

SET @st = '2024-10-13 11:00:00';

1.2. 设置需要分析的时间的长度（分析的结束时间）

分析30分钟的数据

SET @et = DATE_ADD(@st,INTERVAL 30 MINUTE);

分析30秒的数据

SET @et = DATE_ADD(@st,INTERVAL 30 SECOND);

分析12小时的数据

SET @et = DATE_ADD(@st,INTERVAL 12 HOUR);

1.3. 设置分析内容

SET @eventname = '微信支付';

1.4. 设置需要分析的表和字段

1. 将 user_log 数据表转化为后续自动分析使用的临时表 temp_table_time。
2. 将 create_time 需要分析的时间字段转化为后续自动分析使用的临时字段 temp_time。

DROP TABLE IF EXISTS temp_table_time;
CREATE TABLE IF NOT EXISTS temp_table_time
SELECT *,create_time AS `temp_time` FROM user_log 
WHERE create_time BETWEEN @st and @et AND event_name =@eventname ;

2. 自动计算分析

2.1 设置起始序号

SET @row_index = 0;

2.2. 筛选user_log表数据并生成带序号的临时表temp_ria

DROP TABLE IF EXISTS temp_ria;
CREATE TABLE IF NOT EXISTS temp_ria
SELECT (@row_index:=@row_index + 1) AS ria,t.* 
FROM (SELECT * FROM temp_table_time) AS t

2.3. 通过临时表temp_ria 生成序号减一的临时表temp_rib

DROP TABLE IF EXISTS temp_rib;
CREATE TABLE IF NOT EXISTS temp_rib
SELECT ria-1 AS rib,temp_ria.* FROM temp_ria;

2.4. 关联通过序号 temp_ria 与 temp_rib，并计算出需要分析的时间create_time的时间差。

使用 TIMESTAMPDIFF 函数计算两个时间的时间差，并使用 SEC_TO_TIME 函数将计算结果转换为时:分:秒的格式。

DROP TABLE IF EXISTS temp_diff;
CREATE TABLE IF NOT EXISTS temp_diff
SELECT 
a.ria,
a.create_time as create_time_a,
b.create_time as create_time_b,
TIMESTAMPDIFF(SECOND, a.create_time,b.create_time) as diff,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, a.create_time,b.create_time)) as difftimeFROM temp_ria AS a
LEFT JOIN temp_rib as b ON a.ria = b.rib WHERE b.rib is not NULL;

2.5. 查询分析结果

SELECT * FROM temp_diff ORDER BY diff DESC;

2.6. 统计平均用时

使用 AVG 函数计算平均时间差，并使用 SEC_TO_TIME 函数将计算结果转换为时:分:秒的格式 。

SELECT SEC_TO_TIME(AVG(diff)) FROM temp_diff;

2.7. 删除临时表

DROP TABLE IF EXISTS temp_diff;
DROP TABLE IF EXISTS temp_rib;
DROP TABLE IF EXISTS temp_ria;
DROP TABLE IF EXISTS temp_table_time;

完成查询语句

# 1. 设置查询参数部分
SET @st = '2024-10-13 11:00:00';
SET @et = DATE_ADD(@st,INTERVAL 30 MINUTE);
SET @eventname = '微信支付';-- 生成临时表 temp_table_time
DROP TABLE IF EXISTS temp_table_time;
CREATE TABLE IF NOT EXISTS temp_table_time
SELECT *,create_time AS `temp_time` FROM user_log 
WHERE create_time BETWEEN @st and @et AND event_name =@eventname ;# 2.自动计算部分
SET @row_index = 0;
-- 生成临时表temp_ria
DROP TABLE IF EXISTS temp_ria;
CREATE TABLE IF NOT EXISTS temp_ria
SELECT (@row_index:=@row_index + 1) AS ria,t.* 
FROM (SELECT * FROM temp_table_time) AS t;-- 生成临时表 temp_rib
DROP TABLE IF EXISTS temp_rib;
CREATE TABLE IF NOT EXISTS temp_rib
SELECT ria-1 AS rib,temp_ria.* FROM temp_ria;-- 生成分析结果 temp_diff
DROP TABLE IF EXISTS temp_diff;
CREATE TABLE IF NOT EXISTS temp_diff
SELECT 
a.ria,
a.temp_time as temp_time_a,
b.temp_time as temp_time_b,
TIMESTAMPDIFF(SECOND, a.temp_time,b.temp_time) as diff,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, a.temp_time,b.temp_time)) as difftimeFROM temp_ria AS a
LEFT JOIN temp_rib as b ON a.ria = b.rib WHERE b.rib is not NULL;-- 查询分析结果
SELECT * FROM temp_diff ORDER BY diff DESC;SELECT SEC_TO_TIME(AVG(diff)) FROM temp_diff;DROP TABLE IF EXISTS temp_diff;
DROP TABLE IF EXISTS temp_rib;
DROP TABLE IF EXISTS temp_ria;
DROP TABLE IF EXISTS temp_table_time;