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 | +-------+---------------+

注意：

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 <= 1="" 2020-04-20="" group="" by="" as="" select="" d="" interval="" -1="" days1="" from="" test="" where="" 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 <= 1="" 2020-04-20="" group="" by="" as="" select="" d="" interval="" -1="" days1="" from="" test="" where="" 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 <= 1="" 2020-04-20="" group="" by="" as="" select="" d="" interval="" -1="" days1="" from="" test="" where="" 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 <= 1="" 2020-04-20="" group="" by="" as="" select="" d="" interval="" -1="" days1="" from="" test="" where="" and="" log_time="">='2020-03-20 00:00:00' and log_time <= 2020-04-20="" group="" by="" as="" n="" diff="" having="">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