Mysql 指定时间范围,根据时间单位分块分组查询数据

Mysql 指定时间范围,根据时间单位分块分组查询数据

示例

要求

查询告警时间范围在[2020-05-06 07:57:08, 2020-05-06 17:57:08]之间的数据,每3个小时的数据进行分组

SQL

SELECT
	MIN_ALARM_TIME,
	MAX_ALARM_TIME,
	DATE_ADD(
		'2020-05-06 07:57:08',
		INTERVAL GD.HOUR_T * 3 HOUR
	) START_DATE,

IF (
	TIMESTAMPDIFF(
		HOUR,
		'2020-05-06 15:57:08',
		DATE_ADD(
			'2020-05-06 07:57:08',
			INTERVAL (GD.HOUR_T + 1) * 3 HOUR
		)
	) < 0,
	DATE_ADD(
		'2020-05-06 07:57:08',
		INTERVAL (GD.HOUR_T + 1) * 3 HOUR
	),
	'2020-05-06 15:57:08'
) AS END_DATE,
 ALARM_GRADE,
 GD.ALARM_COUNT
FROM
	(
		SELECT
			MIN(ALARM_TIME) AS MIN_ALARM_TIME,
			MAX(ALARM_TIME) AS MAX_ALARM_TIME,
			TIMESTAMPDIFF(
				HOUR,
				'2020-05-06 07:57:08',
				ALARM_TIME
			) DIV 3 AS HOUR_T,
			ALARM_GRADE,
			COUNT(1) AS ALARM_COUNT
		FROM
			SEC_E_ALARMMANAGER_CONTENT
		WHERE
			ALARM_TIME BETWEEN '2020-05-06 07:57:08'
		AND '2020-05-06 15:57:08'
		GROUP BY
			HOUR_T,
			ALARM_GRADE
	) AS GD
ORDER BY
	GD.HOUR_T DESC;

结果

在这里插入图片描述

思路

  1. 使用告警时间与起始时间做小时差值,然后除以3,通过这种方式就可以将日期进行分段,分段的间隔是3小时
TIMESTAMPDIFF(
	HOUR,
	'2020-05-06 07:57:08',
	ALARM_TIME
) DIV 3
  1. 根据分段日期进行数据分组查询
SELECT
	MIN(ALARM_TIME) AS MIN_ALARM_TIME,
	MAX(ALARM_TIME) AS MAX_ALARM_TIME,
	TIMESTAMPDIFF(
		HOUR,
		'2020-05-06 07:57:08',
		ALARM_TIME
	) DIV 3 AS HOUR_T,
	ALARM_GRADE,
	COUNT(1) AS ALARM_COUNT
FROM
	SEC_E_ALARMMANAGER_CONTENT
WHERE
	ALARM_TIME BETWEEN '2020-05-06 07:57:08' AND '2020-05-06 15:57:08'
GROUP BY
	HOUR_T,
	ALARM_GRADE

在这里插入图片描述
3. 将分组后的数据进行日期补全(补全为规则的间隔三小时的方式)

  • 分段数据的起始时间:查询时间范围的起始时间(2020-05-06 07:57:08)+ HOUR_T * 分段间隔(3)
DATE_ADD(
	'2020-05-06 07:57:08',
	INTERVAL GD.HOUR_T * 3 HOUR
) AS START_DATE
  • 分段数据的截止时间:查询时间范围的起始时间(2020-05-06 07:57:08)+ (HOUR_T + 1) * 分段间隔(3)与 查询时间范围的截止时间比较,如果小于截止时间,就用计算后的结果,反之使用查询时间范围的截止时间
IF (
	TIMESTAMPDIFF(
		HOUR,
		'2020-05-06 15:57:08',
		DATE_ADD(
			'2020-05-06 07:57:08',
			INTERVAL (GD.HOUR_T + 1) * 3 HOUR
		)
	) < 0,
	DATE_ADD(
		'2020-05-06 07:57:08',
		INTERVAL (GD.HOUR_T + 1) * 3 HOUR
	),
	'2020-05-06 15:57:08'
) AS END_DATE

附加 Mybatis 的 XML 配置

自定义聚合的时间单位时间间隔

<sql id="listQuery">
    <choose>
        <when test='groupTimeUnit == "YEAR" or groupTimeUnit == "year"'>YEAR</when>
        <when test='groupTimeUnit == "MONTH" or groupTimeUnit == "month"'>MONTH</when>
        <when test='groupTimeUnit == "DAY" or groupTimeUnit == "day"'>DAY</when>
        <when test='groupTimeUnit == "MINUTE" or groupTimeUnit == "minute"'>MINUTE</when>
        <when test='groupTimeUnit == "SECOND" or groupTimeUnit == "second"'>SECOND</when>
        <otherwise>HOUR</otherwise>
    </choose>
</sql>
<select id="queryList" resultType="java.util.Map">
    SELECT
        DATE_ADD(#{startDate},  INTERVAL GD.HOUR_T * #{groupTime} <include refid="listQuery"/>) START_DATE,
        IF (
            TIMESTAMPDIFF(
                <include refid="listQuery"/>,  #{endDate},
                DATE_ADD(#{startDate}, INTERVAL (GD.HOUR_T + 1) * #{groupTime} <include refid="listQuery"/>)
            ) <![CDATA[<]]> 0,
            DATE_ADD(#{startDate}, INTERVAL (GD.HOUR_T + 1) * #{groupTime} <include refid="listQuery"/>),
            #{endDate}
        ) AS END_DATE,
        ALARM_GRADE,
        GD.ALARM_COUNT
    FROM(
        SELECT
            TIMESTAMPDIFF(<include refid="listQuery"/>,#{startDate},ALARM_TIME)DIV #{groupTime} AS HOUR_T,
            ALARM_GRADE,
            COUNT(1) AS ALARM_COUNT
        FROM <include refid="tableName"/>
        WHERE ALARM_TIME BETWEEN #{startDate} AND #{endDate}
        GROUP BY HOUR_T, ALARM_GRADE
    ) AS GD
    ORDER BY GD.HOUR_T DESC
</select>

总结

因为项目需要根据时间进行自定义分段分组查询,在网上搜索没有找到合适的案例,自己总结相似案例并查询SQL函数最后写出了文中的解决方案,因为个人对SQL的了解有限,不确定写出的方案在性能方面存在什么问题,欢迎大家指正其中的缺陷和不合理的地方!

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页