DDR爱好者之家

mysql查询每小时数据和上小时数据的差值实现思路详解
站长资源 2024/11/24 佚名
18 1538
DDR爱好者之家 Design By 杰米

一、前言

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

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

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

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

      可以看到确实是成功获取到了差值。如果要获取差值的比率的话，直接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同学有时候也不靠谱，还是要靠自己啊

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

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

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

DDR爱好者之家 Design By 杰米
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
DDR爱好者之家 Design By 杰米
ddrfans.com DDR爱好者之家
8,675无损音乐
1,324高清电影
213破解软件
70,141收录资讯

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网
DDR爱好者之家 Design By 杰米