mysql之日志时间统计---每类日志时间间隔(间隔超过多少分钟统计出来)

悠悠 2021-09-21 09:08 440阅读 0赞

前提:因笔者业务需求,在某一种日志类型中,记录某个机器的上线掉线记录.根据上线掉线时间间隔进行筛选.然后查询总结了一下如下sql

1mybatis中的xml内容

<select id="getLogType4" resultType="com.mlf.entity.HoldLog" parameterType="com.mlf.entity.HoldLog">
    		SELECT A.id, A.remarks, A.log_type, A.userId, A.userName, A.deviceId, A.typec, A.levelc, A.content, A.request_ip, A.request_uri, A.params, A.methodc, A.create_date, A.detailType,
    			A.twoAuthUserId, A.userType, todd.ADDRESS AS address, TIMESTAMPDIFF( MINUTE, A.create_date, B.create_date ) sub_seconds
    		FROM
    			( SELECT a.*, ( @i := @i + 1 ) AS ord_num
    				FROM
    				( SELECT *  FROM hold_log h  WHERE h.log_type = 4  AND h.deviceId = '1009'  AND ( h.detailType = 1 OR h.detailType = 2 )  ) AS a, ( SELECT @i := 1 ) d
    					ORDER BY id, create_date
    				) AS A
    			LEFT JOIN
    			( SELECT c.id, c.detailType, c.log_type, c.create_date, c.deviceId, ( @j := @j + 1 ) AS ord_num
    				FROM
    				( SELECT *  FROM hold_log hl  WHERE hl.log_type = 4  AND hl.deviceId = #{deviceId}
    					AND ( hl.detailType = 1 OR hl.detailType = 2 )
    				) AS c, ( SELECT @j := 0 ) e
    				ORDER BY id, create_date
    			) AS B ON A.ord_num = B.ord_num
    			LEFT JOIN t_order_device_detail todd ON todd.DEVICE_ID = A.deviceId
    			WHERE 1=1
    			<if test="dateStart != null and dateStart != ''">
    				AND A.create_date > #{dateStart}
    			</if>
    			<if test="dateEnd != null and dateEnd != '' ">
    				AND A.create_date < #{dateEnd}
    			</if>
    		HAVING  sub_seconds > #{timeJ}

2sql语句

SELECT A.id, A.remarks, A.log_type, A.userId, A.userName, A.deviceId, A.typec, A.levelc, A.content, A.request_ip, A.request_uri, A.params, A.methodc, A.create_date, A.detailType,
    			A.twoAuthUserId, A.userType, todd.ADDRESS AS address, TIMESTAMPDIFF( MINUTE, A.create_date, B.create_date ) sub_seconds
    		FROM
    			( SELECT a.*, ( @i := @i + 1 ) AS ord_num
    				FROM
    				( SELECT *  FROM hold_log h  WHERE h.log_type = 4  AND h.deviceId =  #{deviceId}
    					AND ( h.detailType = 1 OR h.detailType = 2 )  ) AS a, ( SELECT @i := 1 ) d
    					ORDER BY id, create_date
    				) AS A
    			LEFT JOIN
    			( SELECT c.id, c.detailType, c.log_type, c.create_date, c.deviceId, ( @j := @j + 1 ) AS ord_num
    				FROM
    				( SELECT *  FROM hold_log hl  WHERE hl.log_type = 4  AND hl.deviceId = #{deviceId}
    					AND ( hl.detailType = 1 OR hl.detailType = 2 )
    				) AS c, ( SELECT @j := 0 ) e
    				ORDER BY id, create_date
    			) AS B ON A.ord_num = B.ord_num
    			LEFT JOIN t_order_device_detail todd ON todd.DEVICE_ID = A.deviceId
    			WHERE 1=1
    				AND A.create_date > #{dateStart}
    				AND A.create_date < #{dateEnd}
    		HAVING  sub_seconds > #{timeJ}

3注意

sql的目的是将此日志表复制两份,然后将每条记录的相邻下一条记录与原表进行整合成一个大表(是通过mysql变量进行整合的)

然后通过

TIMESTAMPDIFF( MINUTE, A.create_date, B.create_date ) sub_seconds计算时间差,最后根据时间差进行筛选.

SELECT @i := 1 解释:mysql赋值有两种,一种是 = ,另外一种是:= ,区别是前者是set 字段使用,也就是我们常用的更新.后者是查询语句赋值,也就是在select中使用.而@i 的解释@是mysql定义变量的符号,i是变量名称 ,所以select @i := 1 就是给一个变量名称为i的变量赋值 为1.注意(这个变量如果后期使用操作会改变的)---以上是笔者自己总结.如有不对的地方,请略过.