每小时的数据mysql_荐 mysql查询每小时数据和上小时数据的差值

最新推荐文章于 2023-05-05 12:58:26 发布
最新推荐文章于 2023-05-05 12:58:26 发布
阅读量223 收藏
点赞数
文章标签： 每小时的数据mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_31861847/article/details/113346207
版权

一、前言

需求是获取某个时间范围内每小时数据和上小时数据的差值以及比率。本来以为会是一个很简单的 sql ，结果思考两分钟发现并不简单，网上也没找到参考的方案，那就只能自己慢慢分析了。

刚开始没思路，就去问 DBA 同学，结果 DBA 说他不会，让我写 php 脚本去计算，，这就有点过分了，我只是想临时查个数据，就不信直接用 sql 查不出来，行叭，咱们边走边试。

博主这里用的是笨方法实现的，各位大佬要是有更简单的方式，请不吝赐教，评论区等你！

mysql版本：

mysql> select version();

+---------------------+

| version() |

+---------------------+

| 10.0.22-MariaDB-log |

+---------------------+

1 row in set (0.00 sec)

二、查询每个小时和上小时的差值

1、拆分需求

这里先分开查询下，看看数据都是多少，方便后续的组合。

(1)获取每小时的数据量

这里为了方便展示，直接合并了下，只显示 01-12 时的数据，并不是 bug 。。

select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days;

+-------+---------------+

| nums | days |

+-------+---------------+

| 15442 | 2020-04-19 01 |

| 15230 | 2020-04-19 02 |

| 14654 | 2020-04-19 03 |

| 14933 | 2020-04-19 04 |

| 14768 | 2020-04-19 05 |

| 15390 | 2020-04-19 06 |

| 15611 | 2020-04-19 07 |

| 15659 | 2020-04-19 08 |

| 15398 | 2020-04-19 09 |

| 15207 | 2020-04-19 10 |

| 14860 | 2020-04-19 11 |

| 15114 | 2020-04-19 12 |

+-------+---------------+

(2)获取上小时的数据量

select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days;

+-------+---------------+

| nums1 | days |

+-------+---------------+

| 15114 | 2020-04-19 01 |

| 15442 | 2020-04-19 02 |

| 15230 | 2020-04-19 03 |

| 14654 | 2020-04-19 04 |

| 14933 | 2020-04-19 05 |

| 14768 | 2020-04-19 06 |

| 15390 | 2020-04-19 07 |

| 15611 | 2020-04-19 08 |

| 15659 | 2020-04-19 09 |

| 15398 | 2020-04-19 10 |

| 15207 | 2020-04-19 11 |

| 14860 | 2020-04-19 12 |

+-------+---------------+

注意：

1)获取上小时数据用的是date_sub()函数，date_sub(日期，interval -1 hour)代表获取日期参数的上个小时，具体参考手册：https://www.w3school.com.cn/sql/func_date_sub.asp

2)这里最外层嵌套了个date_format是为了保持格式和上面的一致，如果不加这个date_format的话，查询出来的日期格式是：2020-04-19 04:00:00的，不方便对比。

2、把这两份数据放到一起看看

select nums ,nums1,days,days1

from

(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as m,

(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n;

+-------+-------+---------------+---------------+

| nums | nums1 | days | days1 |

+-------+-------+---------------+---------------+

| 15442 | 15114 | 2020-04-19 01 | 2020-04-19 01 |

| 15442 | 15442 | 2020-04-19 01 | 2020-04-19 02 |

| 15442 | 15230 | 2020-04-19 01 | 2020-04-19 03 |

| 15442 | 14654 | 2020-04-19 01 | 2020-04-19 04 |

| 15442 | 14933 | 2020-04-19 01 | 2020-04-19 05 |

| 15442 | 14768 | 2020-04-19 01 | 2020-04-19 06 |

| 15442 | 15390 | 2020-04-19 01 | 2020-04-19 07 |

| 15442 | 15611 | 2020-04-19 01 | 2020-04-19 08 |

| 15442 | 15659 | 2020-04-19 01 | 2020-04-19 09 |

| 15442 | 15398 | 2020-04-19 01 | 2020-04-19 10 |

| 15442 | 15207 | 2020-04-19 01 | 2020-04-19 11 |

| 15442 | 14860 | 2020-04-19 01 | 2020-04-19 12 |

| 15230 | 15114 | 2020-04-19 02 | 2020-04-19 01 |

| 15230 | 15442 | 2020-04-19 02 | 2020-04-19 02 |

| 15230 | 15230 | 2020-04-19 02 | 2020-04-19 03 |

可以看到这样组合到一起是类似于程序中的嵌套循环效果，相当于 nums 是外层循环， nums1 是内存循环。循环的时候先用 nums 的值，匹配所有 nums1 的值。类似于 php 程序中的：

foreach($arr as $k=>$v){

foreach($arr1 as $k1=>$v1){

}

}

既然如此，那我们是否可以像平时写程序的那样，找到两个循环数组的相同值，然后进行求差值呢？很明显这里的日期是完全一致的，可以作为对比的条件。

3、使用case …when 计算差值

select (case when days = days1 then (nums - nums1) else 0 end) as diff

from

(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as m,

(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n;

效果：

+------+

| diff |

+------+

| 328 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| -212 |

| 0 |

| 0

可以看到这里使用 case..when 实现了当两个日期相等的时候，就计算差值，近似于 php 程序的：

foreach($arr as $k=>$v){

foreach($arr1 as $k1=>$v1){

if($k == $k1){

//求差值

}

}

}

结果看到有大量的 0 ，也有一部分计算出的结果，不过如果排除掉这些0的话，看起来好像有戏的。

4、过滤掉结果为0 的部分，对比最终数据

这里用 having 来对查询的结果进行过滤。 having 子句可以让我们筛选成组后的各组数据，虽然我们的 sql 在最后面没有进行 group by ，不过两个子查询里面都有 group by 了，理论上来讲用 having 来筛选数据是再合适不过了，试一试

select (case when days = days1 then (nums1 - nums) else 0 end) as diff

from

(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as m,

(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n having diff <>0;

结果：

+------+

| diff |

+------+

| -328 |

| 212 |

| 576 |

| -279 |

| 165 |

| -622 |

| -221 |

| -48 |

| 261 |

| 191 |

| 347 |

| -254 |

+------+

这里看到计算出了结果，那大概对比下吧，下面是手动列出来的部分数据：

当前小时和上个小时的差值： 当前小时 -上个小时

本小时上个小时差值

1544215114-328

1523015442212

1465415230576

1493314654-279

1476814933165

可以看到确实是成功获取到了差值。如果要获取差值的比率的话，直接 case when days = days1 then (nums1 - nums)/nums1 else 0 end 即可。

5、获取本小时和上小时数据的降幅，并展示各个降幅范围的个数

在原来的 case..when 的基础上引申一下，继续增加条件划分范围，并且最后再按照降幅范围进行 group by 求和即可。这个 sql 比较麻烦点，大家有需要的话可以按需修改下，实际测试是可以用的。

select case

when days = days1 and (nums1 - nums)/nums1 < 0.1 then 0.1

when days = days1 and (nums1 - nums)/nums1 > 0.1 and (nums1 - nums)/nums1 < 0.2 then 0.2

when days = days1 and (nums1 - nums)/nums1 > 0.2 and (nums1 - nums)/nums1 < 0.3 then 0.3

when days = days1 and (nums1 - nums)/nums1 > 0.3 and (nums1 - nums)/nums1 < 0.4 then 0.4

when days = days1 and (nums1 - nums)/nums1 > 0.4 and (nums1 - nums)/nums1 < 0.5 then 0.5

when days = days1 and (nums1 - nums)/nums1 > 0.5 then 0.6

else 0 end as diff,count(*) as diff_nums

from

(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-03-20 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as m,

(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-03-20 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n group by diff having diff >0;

结果：

+------+-----------+

| diff | diff_nums |

+------+-----------+

| 0.1 | 360 |

| 0.2 | 10 |

| 0.3 | 1 |

| 0.4 | 1 |

+------+-----------+

三、总结

1、 sql 其实和程序代码差不多，拆分需求一步步组合，大部分需求都是可以实现的。一开始就怂了，那自然是写不出的。

2、 不过复杂的计算，一般是不建议用 sql 来写，用程序写会更快， sql 越复杂，效率就会越低。

3、 DBA 同学有时候也不靠谱，还是要靠自己啊

end

weixin_31861847
关注
mysql中统计每小时，每天，每月，每年注册人数
03-10 1229
此方式很推荐，因为可以随意查询指定年份指定月份的数据。用 between and。
mysql简单数据查询——数采数据电量与耗料的日统计
dlwlrma_516的博客
01-14 1478
mysql简单数据查询——数采数据电量与耗料的日统计
mysql查询每小时数据和上小时数据的差值实现思路详解
09-08
主要介绍了mysql查询每小时数据和上小时数据的差值,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
mysql查询过去24小时内每小时的数据量（精确到分钟）
asdzhouwei的博客
03-31 8770
mysql查询过去24小时内每小时的数据量
Mysql 查询每小时数据的总数
qq_35745341的博客
07-30 4623
1.通过sql查询每小时的数据总数，没有先不管 SELECT hour(表_时间) hour, count(表_id) count FROM 表_name WHERE date_format(表_时间, '%Y-%m-%d') = current_date//格式化时间 GROUP BY date_format(表_时间, '%Y%m%d-%H'), hour//按每小时分组 2.通过map，数组工具把没有数据的小时置为0； ...
MySQL统计每小时、每分钟的数据
u013624149的博客
09-01 3077
# 每小时数量 SELECT create_time,HOUR(create_time) as Hour,count(1) as Count FROM table_name WHERE create_time between '2021-09-01 00:00:00' and now() GROUP BY HOUR(create_time) ORDER BY Hour(create_time); # 每分钟数量 SELECT create_time,MINUTE(create_time) as MINUTE
mysql实现每小时数据差值查询与分析：笨办法与优化探讨
本文档主要探讨了如何使用MySQL查询每小时数据和上小时数据的差值及比率，尤其是在一个特定的时间范围内。作者在遇到这个需求时，起初认为SQL查询会很简单，但实际上发现没有现成的解决方案，因此决定通过笨方法来...
laravel实现按月或天或小时统计mysql数据的方法
10-16
在本文中，我们将深入探讨如何使用 Laravel 框架实现按月、按天或按小时统计 MySQL 数据的方法。Laravel 是一个流行的 PHP 开发框架，它提供了丰富的工具和功能，使得处理数据库操作变得简单易行。 首先，我们需要...
mysql 按小时查询差_MySQL 每小时数据差，每天数据差，每年数据差
weixin_27207591的博客
01-19 175
有一张表：id(int)time(varchar)value(double)1201406010000000220140601000100432014060100020010420140601000300125201406010004002062014060100050040……………………………...有一张表：id(int) time(varchar) ...
MYSQL按时间段(按小时分组)分组查询当天小时内数据
zuihongyan518的博客
06-28 2万+
运营一个需求，要查询一些数据，每1小时一次的数据 ，Excel表格如下： 第一肯定要是用 GROUP BY 然后 一、单表 SELECT DATE_FORMAT( create_time, '%Y-%m-%d %H' ) as time, COUNT( DISTINCT member_id) as count FROM tmp_wp_table GROUP BY time ...
mysql 统计一天24小时数据默认补0SQL
10-10
利用mysql的函数实现统计查询一天24小时的数据，然后统计时间没有的数据默认补上了零。
Mysql语句查询统计一天中每个小时数据的数量统计
追随内心
07-20 4136
floor函数是mysql的向下取整函数如floor(1.2)=1。Mysql查询一天中每半小时记录的数量。每个小时内卖出的订单总数。
mysql 获取当前时间 前、后一小时的时间
weixin_39243289的博客
02-25 8713
取当前时间： mysql&gt; select now(); 前一小时的时间： mysql&gt; select date_sub(now(), interval 1 hour); 后一小时的时间： mysql&gt; select date_add(now(), interval 1 hour);
mysql计算时间小时数_MySQL-按时间统计每个小时的记录数
weixin_34353619的博客
02-10 2761
-------------------------------------------------------------------------------------------------正文-----------------------------------------------------------------------------------------------------...
mysql每小时用户在线数_MySql显示每小时的数据
weixin_30019517的博客
02-28 180
需求显示统计前八小时用户在线数据select * from(select IFNULL(tt.c,0),d1.time from (select '2018-4-4 09:00:00' timeunion select '2018-4-4 10:00:00' timeunion select '2018-4-4 11:00:00' timeunion select '2018-4-4 12:00:...
mysql统计每小时订单_mysql查询每小时数据和上小时数据的差值实现思路详解
weixin_39625782的博客
01-15 576
一、前言需求是获取某个时间范围内每小时数据和上小时数据的差值以及比率。本来以为会是一个很简单的sql，结果思考两分钟发现并不简单，网上也没找到参考的方案，那就只能自己慢慢分析了。刚开始没思路，就去问DBA同学，结果DBA说他不会，让我写php脚本去计算，，这就有点过分了，我只是想临时查个数据，就不信直接用sql查不出来，行叭，咱们边走边试。博主这里用的是笨方法实现的，各位大佬要是有更简单的方式，请...
Mysql 查询一天中，每个小时数据的数量
热门推荐
刘宇(LY)个人笔记
09-05 3万+
SELECT HOUR(e.time) as Hour,count(*) as Count FROM error_log e WHERE e.date = '2017-09-02' GROUP BY HOUR(e.time) ORDER BY Hour(e.time); 下面是查询结果截图 在另一篇文章里，我总结了查询每半小时统计一次的方法。Mysql...
mysql 空位补0_Mysql查询时间范围内的数据如果为空则补全为0
weixin_36352315的博客
01-12 1689
mysql 查询出来的数据image.png因为日志要做统计，所以我需要每天都展示出来，如果为空，则展示为0，显然上图中的10-18日没有展示出来在网上看了一圈都是基于数据库实现的，感觉有些麻烦，本文介绍的是代码实现，亲测可用，废话不多说：DO 类 数据库的结果集@Datapublic class Demo{@JsonFormat( pattern="yyyy-MM-dd", timezone =...
MySQL 获取前24小时每小时数据总数
最新发布
我的名字叫大白
05-05 1940
表中过去24小时内每个小时的数据进行统计，并按照小时分组。函数用于格式化时间，将分钟和秒钟部分都设置为0，只保留小时部分。是你表中记录创建时间的字段名，你需要将其替换成实际的字段名。函数用于统计每个小时内的数据量。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值