欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

mysql查詢每小時數(shù)據(jù)和上小時數(shù)據(jù)的差值實現(xiàn)思路詳解

 更新時間:2020年04月24日 09:40:44   作者:鐵柱同學(xué)  
這篇文章主要介紹了mysql查詢每小時數(shù)據(jù)和上小時數(shù)據(jù)的差值,本文給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下

一、前言

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

      剛開始沒思路,就去問DBA同學(xué),結(jié)果DBA說他不會,讓我寫php腳本去計算,,這就有點過分了,我只是想臨時查個數(shù)據(jù),就不信直接用sql查不出來,行叭,咱們邊走邊試。

博主這里用的是笨方法實現(xiàn)的,各位大佬要是有更簡單的方式,請不吝賜教,評論區(qū)等你!

mysql版本:

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

二、查詢每個小時和上小時的差值

1、拆分需求

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

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

這里為了方便展示,直接合并了下,只顯示01-12時的數(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ù)據(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ù)據(jù)用的是date_sub()函數(shù),date_sub(日期,interval -1 hour)代表獲取日期參數(shù)的上個小時,具體參考手冊:https://www.w3school.com.cn/sql/func_date_sub.asp
2)這里最外層嵌套了個date_format是為了保持格式和上面的一致,如果不加這個date_format的話,查詢出來的日期格式是:2020-04-19 04:00:00的,不方便對比。

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)的時候先用nums的值,匹配所有nums1的值。類似于php程序中的:

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

 }
}

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

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

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

      結(jié)果看到有大量的0,也有一部分計算出的結(jié)果,不過如果排除掉這些0的話,看起來好像有戲的。

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

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

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;

結(jié)果:
+------+
| diff |
+------+
| -328 |
| 212 |
| 576 |
| -279 |
| 165 |
| -622 |
| -221 |
| -48 |
| 261 |
| 191 |
| 347 |
| -254 |
+------+

      這里看到計算出了結(jié)果,那大概對比下吧,下面是手動列出來的部分?jǐn)?shù)據(jù):

當(dāng)前小時和上個小時的差值: 當(dāng)前小時  -上個小時

本小時 上個小時  差值
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、獲取本小時和上小時數(shù)據(jù)的降幅,并展示各個降幅范圍的個數(shù)

      在原來的case..when的基礎(chǔ)上引申一下,繼續(xù)增加條件劃分范圍,并且最后再按照降幅范圍進行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;

結(jié)果:

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

三、總結(jié)

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

補充介紹:MySQL數(shù)據(jù)庫時間和實際時間差8個小時

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

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

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

相關(guān)文章

  • mysql中binlog_format模式與配置詳細分析

    mysql中binlog_format模式與配置詳細分析

    這篇文章主要介紹了mysql中binlog_format模式與配置的相關(guān)內(nèi)容,詳細介紹了binlog的三種格式與SBR、 RBR 兩種模式各自的優(yōu)缺點,需要的朋友可以參考。
    2017-10-10
  • 使用mydumper多線程備份MySQL數(shù)據(jù)庫

    使用mydumper多線程備份MySQL數(shù)據(jù)庫

    MySQL在備份方面包含了自身的mysqldump工具,但其只支持單線程工作,這就使得它無法迅速的備份數(shù)據(jù)。而 mydumper作為一個實用工具,能夠良好支持多線程工作,這使得它在處理速度方面十倍于傳統(tǒng)的
    2013-11-11
  • 保存圖片到MySQL以及從MySQL讀取圖片全過程

    保存圖片到MySQL以及從MySQL讀取圖片全過程

    有人喜歡使用mysql來存儲圖片,而有的人喜歡把圖片存儲在文件系統(tǒng)中,而當(dāng)我們要處理成千上萬的圖片時,會引起技術(shù)問題,下面這篇文章主要給大家介紹了關(guān)于如何保存圖片到MySQL以及從MySQL讀取圖片的相關(guān)資料,需要的朋友可以參考下
    2023-05-05
  • mysql服務(wù)器無法啟動的解決方法

    mysql服務(wù)器無法啟動的解決方法

    本文主要介紹了mysql服務(wù)器無法啟動的解決方法,mysql服務(wù)器無法啟動時,一般時配置文件和路徑的問題,下面就來介紹一下解決方法,感興趣的可以了解一下
    2023-09-09
  • Mysql數(shù)據(jù)庫時間查詢舉例詳解

    Mysql數(shù)據(jù)庫時間查詢舉例詳解

    在項目開發(fā)中,一些業(yè)務(wù)表字段經(jīng)常使用日期和時間類型,而且后續(xù)還會牽涉到這類字段的查詢,下面這篇文章主要給大家介紹了關(guān)于Mysql數(shù)據(jù)庫時間查詢的相關(guān)資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下
    2023-05-05
  • mysql如何處理varchar與nvarchar類型中的特殊字符

    mysql如何處理varchar與nvarchar類型中的特殊字符

    這篇文章主要介紹了mysql如何處理varchar與nvarchar類型中的特殊字符,需要的朋友可以參考下
    2014-12-12
  • Mysql:The user specified as a definer (''xxx@''%'') does not exist的解決方案

    Mysql:The user specified as a definer (''xxx@''%'') does not

    今天小編就為大家分享一篇關(guān)于Mysql:The user specified as a definer ('xxx@'%') does not exist的解決方案,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧
    2018-12-12
  • 登錄MySQL數(shù)據(jù)庫最快幾步(圖文步驟詳解)

    登錄MySQL數(shù)據(jù)庫最快幾步(圖文步驟詳解)

    當(dāng)?MySQL?服務(wù)開啟后,就可以通過客戶端來登錄?MySQL?數(shù)據(jù)庫了。在?Windows?操作系統(tǒng)下可以使用?DOS?命令登錄數(shù)據(jù)庫,本節(jié)將介紹使用命令方式登錄?MySQL?數(shù)據(jù)庫的方法
    2023-10-10
  • MySql?explain命令返回結(jié)果詳細介紹

    MySql?explain命令返回結(jié)果詳細介紹

    explain?是MySql提供的SQL語句查詢性能的工具,是我們優(yōu)化SQL的重要指標(biāo)手段,要看懂explain返回的結(jié)果集就尤為重要,這篇文章主要介紹了MySql?explain命令返回結(jié)果解讀,需要的朋友可以參考下
    2023-09-09
  • MySQL錯誤Forcing close of thread的兩種解決方法

    MySQL錯誤Forcing close of thread的兩種解決方法

    這篇文章主要介紹了MySQL錯誤Forcing close of thread的兩種解決方法,需要的朋友可以參考下
    2014-11-11

最新評論