一、前言

需求是獲取某個(gè)時(shí)間范圍內(nèi)每小時(shí)數(shù)據(jù)和上小時(shí)數(shù)據(jù)的差值以及比率。本來(lái)以為會(huì)是一個(gè)很簡(jiǎn)單的sql，結(jié)果思考兩分鐘發(fā)現(xiàn)并不簡(jiǎn)單，網(wǎng)上也沒(méi)找到參考的方案，那就只能自己慢慢分析了。

剛開始沒(méi)思路，就去問(wèn)DBA同學(xué)，結(jié)果DBA說(shuō)他不會(huì)，讓我寫php腳本去計(jì)算，，這就有點(diǎn)過(guò)分了，我只是想臨時(shí)查個(gè)數(shù)據(jù)，就不信直接用sql查不出來(lái)，行叭，咱們邊走邊試。

博主這里用的是笨方法實(shí)現(xiàn)的，各位大佬要是有更簡(jiǎn)單的方式，請(qǐng)不吝賜教，評(píng)論區(qū)等你！

mysql版本：

mysql> select version();+---------------------+| version() |+---------------------+| 10.0.22-MariaDB-log |+---------------------+1 row in set (0.00 sec)

二、查詢每個(gè)小時(shí)和上小時(shí)的差值

1、拆分需求

這里先分開查詢下，看看數(shù)據(jù)都是多少，方便后續(xù)的組合。

（1）獲取每小時(shí)的數(shù)據(jù)量

這里為了方便展示，直接合并了下，只顯示01-12時(shí)的數(shù)據(jù)，并不是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）獲取上小時(shí)的數(shù)據(jù)量

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）獲取上小時(shí)數(shù)據(jù)用的是date_sub(）函數(shù)，date_sub(日期，interval -1 hour）代表獲取日期參數(shù)的上個(gè)小時(shí)，具體參考手冊(cè)：https://www.w3school.com.cn/sql/func_date_sub.asp2）這里最外層嵌套了個(gè)date_format是為了保持格式和上面的一致，如果不加這個(gè)date_format的話，查詢出來(lái)的日期格式是：2020-04-19 04:00:00的，不方便對(duì)比。

2、把這兩份數(shù)據(jù)放到一起看看

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 |

可以看到這樣組合到一起是類似于程序中的嵌套循環(huán)效果，相當(dāng)于nums是外層循環(huán)，nums1是內(nèi)存循環(huán)。循環(huán)的時(shí)候先用nums的值，匹配所有nums1的值。類似于php程序中的：

foreach($arr as $k=>$v){ foreach($arr1 as $k1=>$v1){ }}

既然如此，那我們是否可以像平時(shí)寫程序的那樣，找到兩個(gè)循環(huán)數(shù)組的相同值，然后進(jìn)行求差值呢？很明顯這里的日期是完全一致的，可以作為對(duì)比的條件。

3、使用case …when 計(jì)算差值

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實(shí)現(xiàn)了當(dāng)兩個(gè)日期相等的時(shí)候，就計(jì)算差值，近似于php程序的：

foreach($arr as $k=>$v){ foreach($arr1 as $k1=>$v1){ if($k == $k1){ //求差值 } }}

結(jié)果看到有大量的0，也有一部分計(jì)算出的結(jié)果，不過(guò)如果排除掉這些0的話，看起來(lái)好像有戲的。

4、過(guò)濾掉結(jié)果為0 的部分，對(duì)比最終數(shù)據(jù)

這里用having來(lái)對(duì)查詢的結(jié)果進(jìn)行過(guò)濾。having子句可以讓我們篩選成組后的各組數(shù)據(jù)，雖然我們的sql在最后面沒(méi)有進(jìn)行g(shù)roup by，不過(guò)兩個(gè)子查詢里面都有g(shù)roup by了，理論上來(lái)講用having來(lái)篩選數(shù)據(jù)是再合適不過(guò)了，試一試

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;結(jié)果：+------+| diff |+------+| -328 || 212 || 576 || -279 || 165 || -622 || -221 || -48 || 261 || 191 || 347 || -254 |+------+

這里看到計(jì)算出了結(jié)果，那大概對(duì)比下吧，下面是手動(dòng)列出來(lái)的部分?jǐn)?shù)據(jù)：

當(dāng)前小時(shí)和上個(gè)小時(shí)的差值： 當(dāng)前小時(shí) -上個(gè)小時(shí)

本小時(shí) 上個(gè)小時(shí) 差值15442 15114 -32815230 15442 21214654 15230 57614933 14654 -27914768 14933 165

可以看到確實(shí)是成功獲取到了差值。如果要獲取差值的比率的話，直接case when days = days1 then (nums1 - nums)/nums1 else 0 end 即可。

5、獲取本小時(shí)和上小時(shí)數(shù)據(jù)的降幅，并展示各個(gè)降幅范圍的個(gè)數(shù)

在原來(lái)的case..when的基礎(chǔ)上引申一下，繼續(xù)增加條件劃分范圍，并且最后再按照降幅范圍進(jìn)行g(shù)roup by求和即可。這個(gè)sql比較麻煩點(diǎn)，大家有需要的話可以按需修改下，實(shí)際測(cè)試是可以用的。

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;

結(jié)果：

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

三、總結(jié)

1、 sql其實(shí)和程序代碼差不多，拆分需求一步步組合，大部分需求都是可以實(shí)現(xiàn)的。一開始就慫了，那自然是寫不出的。2、 不過(guò)復(fù)雜的計(jì)算，一般是不建議用sql來(lái)寫，用程序?qū)憰?huì)更快，sql越復(fù)雜，效率就會(huì)越低。3、 DBA同學(xué)有時(shí)候也不靠譜，還是要靠自己啊

補(bǔ)充介紹：MySQL數(shù)據(jù)庫(kù)時(shí)間和實(shí)際時(shí)間差8個(gè)小時(shí)

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

數(shù)據(jù)庫(kù)配置后面加上&serverTimezone=GMT%2B8

到此這篇關(guān)于mysql查詢每小時(shí)數(shù)據(jù)和上小時(shí)數(shù)據(jù)的差值的文章就介紹到這了,更多相關(guān)mysql 每小時(shí)數(shù)據(jù)差值內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！