SQL按每5分钟或者10分钟分组统计数据

最新推荐文章于 2022-11-22 16:30:19 发布

一把编程的菜刀

最新推荐文章于 2022-11-22 16:30:19 发布

阅读量5.7k

点赞数 3

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/chinmo/article/details/125086197

版权

SQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

创建表：

CREATE TABLE `tb` (
  `时间` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `小数` decimal(7,5) NOT NULL DEFAULT '0.00000'
)

插入数据：

INSERT INTO `tb`(`时间`, `小数`) VALUES 
('2022-05-30 00:00:01',0.07523),
('2022-05-30 00:01:00',0.04416),
('2022-05-30 00:02:01',0.04429),
('2022-05-30 00:03:00',0.04348),
('2022-05-30 00:04:01',0.02853),
('2022-05-30 00:05:01',0.01429),
('2022-05-30 00:06:01',0.01409),
('2022-05-30 00:07:01',0.01517),
('2022-05-30 00:08:01',0.04371),
('2022-05-30 00:09:01',0.06100),
('2022-05-30 00:10:01',0.08211),
('2022-05-30 00:11:01',0.08391),
('2022-05-30 00:12:01',0.13065),
('2022-05-30 00:13:01',0.17940),
('2022-05-30 00:14:01',0.21385)

如图：

使用如下SQL语句实现：

SELECT substr(MAX(时间),12,5) as 时间,AVG(小数) as 每组平均数 FROM `tb` group by floor(MINUTE( 时间 )/ 10 ) * 10

或者如下语句实现：

SELECT date_format(MAX(时间),'%H:%i') as 时间,AVG(小数) as 每组平均数 FROM `tb` group by floor(MINUTE( 时间 )/ 10 ) * 10

按每10分钟分组最终结果如下：

sqlserver语句实现可以用：

  select CONVERT(varchar(5),max([时间 ]),8) as 时间 ,avg([小数]) as 每组平均数  from [tb] group by cast(floor(cast([时间] as float)*24*60/10)*10/60/24 as smalldatetime)

结果如下：