欢迎来到站长教程网!

MySQL

当前位置:主页 > 数据库 > MySQL >

mysql查询每小时数据和上小时数据的差值实现思路

时间:2020-04-24|栏目:MySQL|点击:

需求是获取某个时间范围内每小时数据和上小时数据的差值以及比率。本来以为会是一个很简单的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 的部分,对比最终数据

上一篇:mysql条件查询and or使用方法及优先级实例分析

栏    目:MySQL

下一篇:MySQL使用mysqldump+binlog完整恢复被删除的数据库原

本文标题:mysql查询每小时数据和上小时数据的差值实现思路

本文地址:http://www.blue1000.com/shujuku/MySQL/499.html

广告投放 | 联系我们 | 版权申明

重要申明:本站所有的文章、图片、评论等,均由网友发表或上传并维护或收集自网络,属个人行为,与本站立场无关。

如果侵犯了您的权利,请与我们联系,我们将在24小时内进行处理、任何非本站因素导致的法律后果,本站均不负任何责任。

联系QQ:1961407250 | 邮箱:1961407250#qq.com(#换成@)

Copyright © 2002-2022 BK网络学院版权所有 Power by DedeCms 粤ICP备19027147号-7