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

2023-07-26 18:34:03 80

一、前言

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

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

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

mysql版本：

mysql>selectversion();
+---------------------+
|version()|
+---------------------+
|10.0.22-MariaDB-log|
+---------------------+
1rowinset(0.00sec)

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

1、拆分需求

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

（1）获取每小时的数据量

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

selectcount(*)asnums,date_format(log_time,'%Y-%m-%d%h')asdaysfromtestwhere1andlog_time>='2020-04-1900:00:00'andlog_time<='2020-04-2000:00:00'groupbydays;
+-------+---------------+
|nums|days|
+-------+---------------+
|15442|2020-04-1901|
|15230|2020-04-1902|
|14654|2020-04-1903|
|14933|2020-04-1904|
|14768|2020-04-1905|
|15390|2020-04-1906|
|15611|2020-04-1907|
|15659|2020-04-1908|
|15398|2020-04-1909|
|15207|2020-04-1910|
|14860|2020-04-1911|
|15114|2020-04-1912|
+-------+---------------+

（2）获取上小时的数据量

selectcount(*)asnums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d%h'),interval-1hour),'%Y-%m-%d%h')asdaysfromtestwhere1andlog_time>='2020-04-1900:00:00'andlog_time<='2020-04-2000:00:00'groupbydays;
+-------+---------------+
|nums1|days|
+-------+---------------+
|15114|2020-04-1901|
|15442|2020-04-1902|
|15230|2020-04-1903|
|14654|2020-04-1904|
|14933|2020-04-1905|
|14768|2020-04-1906|
|15390|2020-04-1907|
|15611|2020-04-1908|
|15659|2020-04-1909|
|15398|2020-04-1910|
|15207|2020-04-1911|
|14860|2020-04-1912|
+-------+---------------+

注意：

1）获取上小时数据用的是date_sub(）函数，date_sub(日期，interval-1hour）代表获取日期参数的上个小时，具体参考手册：https://www.w3school.com.cn/sql/func_date_sub.asp
2）这里最外层嵌套了个date_format是为了保持格式和上面的一致，如果不加这个date_format的话，查询出来的日期格式是：2020-04-1904:00:00的，不方便对比。

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

selectnums,nums1,days,days1
from
(selectcount(*)asnums,date_format(log_time,'%Y-%m-%d%h')asdaysfromtestwhere1andlog_time>='2020-04-1900:00:00'andlog_time<='2020-04-2000:00:00'groupbydays)asm,
(selectcount(*)asnums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d%h'),interval-1hour),'%Y-%m-%d%h')asdays1fromtestwhere1andlog_time>='2020-04-1900:00:00'andlog_time<='2020-04-2000:00:00'groupbydays1)asn;

+-------+-------+---------------+---------------+
|nums|nums1|days|days1|
+-------+-------+---------------+---------------+
|15442|15114|2020-04-1901|2020-04-1901|
|15442|15442|2020-04-1901|2020-04-1902|
|15442|15230|2020-04-1901|2020-04-1903|
|15442|14654|2020-04-1901|2020-04-1904|
|15442|14933|2020-04-1901|2020-04-1905|
|15442|14768|2020-04-1901|2020-04-1906|
|15442|15390|2020-04-1901|2020-04-1907|
|15442|15611|2020-04-1901|2020-04-1908|
|15442|15659|2020-04-1901|2020-04-1909|
|15442|15398|2020-04-1901|2020-04-1910|
|15442|15207|2020-04-1901|2020-04-1911|
|15442|14860|2020-04-1901|2020-04-1912|
|15230|15114|2020-04-1902|2020-04-1901|
|15230|15442|2020-04-1902|2020-04-1902|
|15230|15230|2020-04-1902|2020-04-1903|

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

foreach($arras$k=>$v){
foreach($arr1as$k1=>$v1){

}
}

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

3、使用case…when计算差值

select(casewhendays=days1then(nums-nums1)else0end)asdiff
from
(selectcount(*)asnums,date_format(log_time,'%Y-%m-%d%h')asdaysfromtestwhere1andlog_time>='2020-04-1900:00:00'andlog_time<='2020-04-2000:00:00'groupbydays)asm,
(selectcount(*)asnums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d%h'),interval-1hour),'%Y-%m-%d%h')asdays1fromtestwhere1andlog_time>='2020-04-1900:00:00'andlog_time<='2020-04-2000:00:00'groupbydays1)asn;

效果：
+------+
|diff|
+------+
|328|
|0|
|0|
|0|
|0|
|0|
|0|
|0|
|0|
|0|
|0|
|0|
|0|
|-212|
|0|
|0

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

	foreach($arras$k=>$v){
foreach($arr1as$k1=>$v1){
if($k==$k1){
//求差值
}
}
}

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

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

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

select(casewhendays=days1then(nums1-nums)else0end)asdiff
from
(selectcount(*)asnums,date_format(log_time,'%Y-%m-%d%h')asdaysfromtestwhere1andlog_time>='2020-04-1900:00:00'andlog_time<='2020-04-2000:00:00'groupbydays)asm,
(selectcount(*)asnums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d%h'),interval-1hour),'%Y-%m-%d%h')asdays1fromtestwhere1andlog_time>='2020-04-1900:00:00'andlog_time<='2020-04-2000:00:00'groupbydays1)asnhavingdiff<>0;

结果：
+------+
|diff|
+------+
|-328|
|212|
|576|
|-279|
|165|
|-622|
|-221|
|-48|
|261|
|191|
|347|
|-254|
+------+

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

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

本小时上个小时差值
15442 15114 -328
15230 15442 212
14654 15230 576
14933 14654 -279
14768 14933 165

可以看到确实是成功获取到了差值。如果要获取差值的比率的话，直接casewhendays=days1then(nums1-nums)/nums1else0end即可。

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

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

selectcase
whendays=days1and(nums1-nums)/nums1<0.1then0.1
whendays=days1and(nums1-nums)/nums1>0.1and(nums1-nums)/nums1<0.2then0.2
whendays=days1and(nums1-nums)/nums1>0.2and(nums1-nums)/nums1<0.3then0.3
whendays=days1and(nums1-nums)/nums1>0.3and(nums1-nums)/nums1<0.4then0.4
whendays=days1and(nums1-nums)/nums1>0.4and(nums1-nums)/nums1<0.5then0.5
whendays=days1and(nums1-nums)/nums1>0.5then0.6
else0endasdiff,count(*)asdiff_nums
from
(selectcount(*)asnums,date_format(log_time,'%Y-%m-%d%h')asdaysfromtestwhere1andlog_time>='2020-03-2000:00:00'andlog_time<='2020-04-2000:00:00'groupbydays)asm,
(selectcount(*)asnums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d%h'),interval-1hour),'%Y-%m-%d%h')asdays1fromtestwhere1andlog_time>='2020-03-2000:00:00'andlog_time<='2020-04-2000:00:00'groupbydays1)asngroupbydiffhavingdiff>0;

结果：

+------+-----------+
|diff|diff_nums|
+------+-----------+
| 0.1| 360|
| 0.2| 10|
| 0.3| 1|
| 0.4| 1|
+------+-----------+

三、总结

1、sql其实和程序代码差不多，拆分需求一步步组合，大部分需求都是可以实现的。一开始就怂了，那自然是写不出的。
2、不过复杂的计算，一般是不建议用sql来写，用程序写会更快，sql越复杂，效率就会越低。
3、DBA同学有时候也不靠谱，还是要靠自己啊

补充介绍：MySQL数据库时间和实际时间差8个小时

url=jdbc:mysql://127.0.0.1:3306/somedatabase?characterEncoding=utf-8&serverTimezone=GMT%2B8

数据库配置后面加上&serverTimezone=GMT%2B8

到此这篇关于mysql查询每小时数据和上小时数据的差值的文章就介绍到这了,更多相关mysql每小时数据差值内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

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

热门推荐

随机推荐