MySQL按天分組統(tǒng)計一定時間內(nèi)的數(shù)據(jù)實例(沒有數(shù)據(jù)補(bǔ)0)
簡介
工作中偶爾會出現(xiàn)一個查詢數(shù)據(jù)的需求,那就是需要按天統(tǒng)計近一個月或其它一段時間內(nèi)每天的所有記錄或者分組數(shù)據(jù),沒有數(shù)據(jù)則自動補(bǔ)0。
一般情況下我們都會過濾沒有數(shù)據(jù)的時間,但前端拿到后端返回的數(shù)據(jù)想直接展示連續(xù)性數(shù)據(jù)變化時要進(jìn)行再處理,下面介紹如何通過sql語句直接滿足需求。
思路分析
- 如果能每天都有數(shù)據(jù),可以直接使用最簡單的查詢節(jié)省時間。
- 要能夠展示每天的日期就要創(chuàng)建一張日期虛擬表作為連接表。
- 要使每天數(shù)據(jù)不為null,使用
IFNULL(count,0)
函數(shù)進(jìn)行判斷是否補(bǔ)零。
SQL實現(xiàn)
下面以查詢近一個月每天的數(shù)據(jù)為示例展示SQL實現(xiàn)。
按天統(tǒng)計數(shù)據(jù)
1.沒數(shù)據(jù)的一天過濾
SELECT DATE(CREATE_DATE) as date, COUNT(1) as count FROM 表 WHERE 字段 = '1' AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d') GROUP BY DATE(CREATE_DATE);
查詢結(jié)果:
2.沒數(shù)據(jù)的一天有日期總數(shù)為null
SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,data.num AS count FROM ( SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY) FROM 表 ) day WHERE DATE_FORMAT(@days, '%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d') ORDER BY date ) dates LEFT JOIN ( SELECT COUNT(1) AS num, DATE(CREATE_DATE) AS time FROM audit_work_sheet WHERE 字段 = '1' AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S') GROUP BY DATE(CREATE_DATE) ) data ON DATE(time) = date ORDER BY date;
查詢結(jié)果:
3.沒數(shù)據(jù)的一天有日期總數(shù)為0
SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,IFNULL(data.num, 0) AS count FROM ( SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY) FROM 表 ) day WHERE DATE_FORMAT(@days, '%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d') ORDER BY date ) dates LEFT JOIN ( SELECT COUNT(1) AS num, DATE(CREATE_DATE) AS time FROM audit_work_sheet WHERE 字段 = '1' AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S') GROUP BY DATE(CREATE_DATE) ) data ON DATE(time) = date ORDER BY date;
查詢結(jié)果
4.加入其它分組字段沒數(shù)據(jù)的一天有日期總數(shù)為0
SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,data.level AS level, IFNULL(data.num, 0) AS count FROM ( SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY) FROM 表 ) day WHERE DATE_FORMAT(@days, '%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d') ORDER BY date ) dates LEFT JOIN ( SELECT RISK_LEVEL AS level, COUNT(1) AS num, DATE(CREATE_DATE) AS time FROM audit_work_sheet WHERE 字段 = '1' AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S') GROUP BY DATE(CREATE_DATE),level ) data ON DATE(time) = date ORDER BY date,level;
查詢結(jié)果
(中間過多數(shù)據(jù)不展示)
以上就是按天統(tǒng)計數(shù)據(jù)沒有數(shù)據(jù)補(bǔ)零的SQL語句,按月按年等其它時間段,或其它分組條件,都可以直接修改參數(shù)后查詢出結(jié)果。
附:Mysql按日、周、月進(jìn)行分組統(tǒng)計
DATE_FORMAT 是 MySQL 內(nèi)置的一個函數(shù),作用是以不同的格式顯示日期/時間數(shù)據(jù)。具體的語法如下:
DATE_FORMAT(date,format)
其中
- date:合法的日期
- format:規(guī)定日期/時間的輸出格式,其中format可使用的格式可以查看以下鏈接
下面我們通過具體例子來看如何通過 DATE_FORMAT 進(jìn)行分組統(tǒng)計:
下表兩列分別代表產(chǎn)品買出的準(zhǔn)確時間(精確到秒),和買出的產(chǎn)品類型。
start_time product_no
2017/12/1 00:00:112A
2017/12/3 07:51:113C
2017/12/3 07:59:253C
2017/12/5 15:40:456C
現(xiàn)在我們需要對每天,每周,每月各個產(chǎn)品的銷量進(jìn)行統(tǒng)計,
1)按天統(tǒng)計:
select DATE_FORMAT(start_time,'%Y%m%d') days,count(product_no) count from test group by days;
2)按周統(tǒng)計:
select DATE_FORMAT(start_time,'%Y%u') weeks,count(product_no) count from test group by weeks;
3)按月統(tǒng)計:
select DATE_FORMAT(start_time,'%Y%m') months,count(product_no) count from test group bymonths;
總結(jié)
到此這篇關(guān)于MySQL按天分組統(tǒng)計一定時間內(nèi)的數(shù)據(jù)(沒有數(shù)據(jù)補(bǔ)0)的文章就介紹到這了,更多相關(guān)MySQL按天分組統(tǒng)計數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql中獲取一天、一周、一月時間數(shù)據(jù)的各種sql語句寫法
今天抽時間整理了一篇mysql中與天、周、月有關(guān)的時間數(shù)據(jù)的sql語句的各種寫法,部分是收集資料,全部手工整理,自己學(xué)習(xí)的同時,分享給大家,并首先默認(rèn)創(chuàng)建一個表、插入2條數(shù)據(jù),便于部分?jǐn)?shù)據(jù)的測試,其中部分名詞或函數(shù)進(jìn)行了解釋說明。直入主題2014-05-05MySQL性能優(yōu)化之max_connections配置參數(shù)淺析
這篇文章主要介紹了MySQL性能優(yōu)化之max_connections配置參數(shù)淺析,本文著重講解了3種配置max_connections參數(shù)的方法,需要的朋友可以參考下2014-07-07MySQL 數(shù)據(jù)查重、去重的實現(xiàn)語句
這篇文章主要介紹了MySQL 數(shù)據(jù)查重、去重的實現(xiàn)語句,幫助大家更好的理解和學(xué)習(xí)MySQL數(shù)據(jù)庫,感興趣的朋友可以了解下2020-09-09mysql中刪除數(shù)據(jù)的四種方法小結(jié)
在MySQL數(shù)據(jù)庫中,刪除數(shù)據(jù)是一個常見的操作,它允許從表中移除不再需要的數(shù)據(jù),本文就來介紹一下四種方法,具有一定的參考價值,感興趣的可以了解一下2023-10-10Mysql中json類型數(shù)據(jù)查詢的實現(xiàn)
MySQL5.7開始支持JSON格式的數(shù)據(jù)類型,可以存儲和處理JSON類型的數(shù)據(jù),本文主要介紹一些關(guān)于json數(shù)據(jù)類型的查詢操作,具有一定的參考價值,感興趣的可以了解一下2023-10-10