ADSL拨号VPS包含了中国大陆（联通，移动，电信，）

中国香港，国外拨号VPS。

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 difffrom (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 difffrom (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.1when days = days1 and (nums1 - nums)/nums1 > 0.1 and (nums1 - nums)/nums1 < 0.2 then 0.2when days = days1 and (nums1 - nums)/nums1 > 0.2 and (nums1 - nums)/nums1 < 0.3 then 0.3when days = days1 and (nums1 - nums)/nums1 > 0.3 and (nums1 - nums)/nums1 < 0.4 then 0.4when days = days1 and (nums1 - nums)/nums1 > 0.4 and (nums1 - nums)/nums1 < 0.5 then 0.5when days = days1 and (nums1 - nums)/nums1 > 0.5 then 0.6 else 0 end as diff,count(*) as diff_numsfrom (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;

结果：