示例
要求
查询告警时间范围在[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;
结果
思路
- 使用告警时间与起始时间做小时差值,然后除以3,通过这种方式就可以将日期进行分段,分段的间隔是3小时
TIMESTAMPDIFF(
HOUR,
'2020-05-06 07:57:08',
ALARM_TIME
) DIV 3
- 根据分段日期进行数据分组查询
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的了解有限,不确定写出的方案在性能方面存在什么问题,欢迎大家指正其中的缺陷和不合理的地方!