在MySQL上实现间隔5分钟汇总取数及相关字符串、时间处理方法实践

1. 实践案例需求描述

查询mysql数据库，按每5分钟分组获取3个小时内的电量数据，参考SQL语句如下。

select sd.RecordTime RecordTime, sd.sddl sddl,sd.pvdl ,cap.capdl capdl from
((SELECT CONCAT(DATE_FORMAT(RecordTime,'%Y-%m-%d %H:'), LPAD(floor(MINUTE(RecordTime)/5)*5,2,'0')) RecordTime,
sum(case when EquipmentId='db_sddb1' then ThisPower else 0 end) sddl, 
sum(case when EquipmentId='db_syfd1' then ThisPower else 0 end) pvdl
FROM electricitymeter_history
where RecordTime > date_sub(str_to_date('2023-11-06 00', '%Y-%m-%d %H'), interval 3 hour)
group by CONCAT(DATE_FORMAT(RecordTime,'%Y-%m-%d %H:'),  LPAD(floor(MINUTE(RecordTime)/5)*5,2,'0'))) sd
left join
(SELECT CONCAT(DATE_FORMAT(RecordTime,'%Y-%m-%d %H:'), LPAD(floor(MINUTE(RecordTime)/5)*5,2,'0')) RecordTime,sum(ThisChargingCapacity) capdl
FROM storedenergy_history
WHERE 
RecordTime > date_sub(str_to_date('2023-11-06 00', '%Y-%m-%d %H'), interval 3 hour)
group by CONCAT(DATE_FORMAT(RecordTime,'%Y-%m-%d %H:'),  LPAD(floor(MINUTE(RecordTime)/5)*5,2,'0'))) cap
on sd.RecordTime = cap.RecordTime )
order by RecordTime desc

其中，5分钟间隔是这样约束的：

时间01:30:00至01:34:59的数据进行合计，时间计为01:30，时间01:35:00至01:39:59的数据进行合计，时间计计为01:35。

2. 解决方法

2.1. 间隔5分钟算法

时间

=

向下取整

(

分钟数

5

)

×

5

时间 = 向下取整( frac{分钟数}{5} )times5

时间=向下取整(5分钟数​)×5

select floor(MINUTE(now())/5)*5

2.2. 分类转数据列

使用case when，符合条件的取出值，不满足赋值0，例如：

sum(case when EquipmentId='db_sddb1' then ThisPower else 0 end) sddl, 
sum(case when EquipmentId='db_syfd1' then ThisPower else 0 end) pvdl

3. 主要相关mysql技术

3.1. 取整

1. 向下取整（Floor）：

使用Floor函数可以将一个数值向下取整为最接近的小于或等于该数值的整数。例如，Floor(3.8)的结果为3。

2. 向上取整（Ceiling）：

使用Ceiling函数可以将一个数值向上取整为最接近的大于或等于该数值的整数。例如，Ceiling(3.2)的结果为4。

3. 四舍五入（Round）：

使用Round函数可以将一个数值按照四舍五入的规则取整。例如，Round(3.5)的结果为4，Round(3.4)的结果为3。

4. 截断取整（Truncate）：

使用Truncate函数可以将一个数值截断为指定小数位数的整数。例如，Truncate(3.567, 2)的结果为3.56，表示将3.567截断为两位小数。

5. 向零取整（Trunc）：

使用Trunc函数可以将一个数值向零取整，即直接去掉小数部分。例如，Trunc(3.8)的结果为3，Trunc(-3.8)的结果为-3。

除了以上几种常用的取整方法外，MySQL还提供了其他一些函数来满足不同的需求，如Ceil函数用于向上取整，Div函数用于整除运算并返回整数部分，Mod函数用于取模运算并返回余数等。

3.2. 字符串补位

在 MySQL 数据库中，字符串补全位数是一种非常常见的操作。这个操作可以让你的数据更加规范化，也可以让你的代码更加清晰易懂。在本文中，我们将会介绍 MySQL 字符串补全位数的方法与技巧。

1. 使用 LPAD() 函数

LPAD() 函数是 MySQL 中用来补全字符串的函数。

是最终字符串的长度，pad 是用来补全的字符。例如，可以使用如下的 SQL 语句：

SELECT LPAD('123',5,'0');

这个语句会返回 ‘00123’。LPAD() 函数会在字符串的左侧使用指定的字符进行补全。

2. 使用 RPAD() 函数

除了 LPAD() 函数之外，MySQL 还提供了 RPAD() 函数。这个函数的作用和 LPAD() 函数类似，不同之处在于它是在字符串的右侧进行补全。例如，可以使用如下的 SQL 语句：

SELECT RPAD('123',5,'0');

3.3. 区分字符大小写

在mysql中，可以利用select查询语句配合binary关键字来区分大小写，select语句用于查询数据，binary关键字用于区分大小写，语法为“select * from 表名 WHERE binary 字段=字段值”。

SELECT * FROM electricitymeter_history where binary EquipmentId = 'db1';

3.4. 设置时间间隔

例如取三小时之前的时间：

select date_sub(now(), interval 3 hour)

其中的 interval 是一个时间间隔函数，用于在日期和时间上进行加减计算。它非常适合用于处理时间序列数据、执行标志和报告日期等操作。INTERVAL 可以接受的时间单位包括年（YEAR）、季度（QUARTER）、月（MONTH）、周（WEEK）、天（DAY）、时（HOUR）、分（MINUTE）、秒（SECOND）。

interval 函数的语法：

DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)