- 准备
- 步骤
- 1. 设置查询参数部分
- 1.1 设置需要分析的起始时间
- 1.2. 设置需要分析的时间的长度(分析的结束时间)
- 1.3. 设置分析内容
- 1.4. 设置需要分析的表和字段
- 2. 自动计算分析
- 2.1 设置起始序号
- 2.2. 筛选`user_log`表数据并生成带序号的临时表`temp_ria`
- 2.3. 通过临时表`temp_ria` 生成序号减一的临时表`temp_rib`
- 2.4. 关联通过序号 `temp_ria` 与 `temp_rib`,并计算出需要分析的时间`create_time`的时间差。
- 2.5. 查询分析结果
- 2.6. 统计平均用时
- 2.7. 删除临时表
- 完成查询语句
准备
- 数据:任务/事件日志。
- 查询工具: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. 设置需要分析的表和字段
- 将
user_log
数据表转化为后续自动分析使用的临时表temp_table_time
。 - 将
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;