快捷導(dǎo)航

MySQL按天分組統(tǒng)計(jì)一定時(shí)間內(nèi)的數(shù)據(jù)實(shí)例(沒有數(shù)據(jù)補(bǔ)0)

更新時(shí)間：2023年03月01日 09:25:02 作者：吃榴蓮不吐葡萄皮

我們在用Mysql制作數(shù)據(jù)可視化圖表時(shí)候,經(jīng)常需要按照天對數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì),下面這篇文章主要給大家介紹了關(guān)于MySQL按天分組統(tǒng)計(jì)一定時(shí)間內(nèi)的數(shù)據(jù),沒有數(shù)據(jù)補(bǔ)0的相關(guān)資料,需要的朋友可以參考下

簡介

工作中偶爾會(huì)出現(xiàn)一個(gè)查詢數(shù)據(jù)的需求，那就是需要按天統(tǒng)計(jì)近一個(gè)月或其它一段時(shí)間內(nèi)每天的所有記錄或者分組數(shù)據(jù)，沒有數(shù)據(jù)則自動(dòng)補(bǔ)0。

一般情況下我們都會(huì)過濾沒有數(shù)據(jù)的時(shí)間，但前端拿到后端返回的數(shù)據(jù)想直接展示連續(xù)性數(shù)據(jù)變化時(shí)要進(jìn)行再處理，下面介紹如何通過sql語句直接滿足需求。

思路分析

如果能每天都有數(shù)據(jù)，可以直接使用最簡單的查詢節(jié)省時(shí)間。
要能夠展示每天的日期就要?jiǎng)?chuàng)建一張日期虛擬表作為連接表。
要使每天數(shù)據(jù)不為null，使用IFNULL(count,0)函數(shù)進(jìn)行判斷是否補(bǔ)零。

SQL實(shí)現(xiàn)

下面以查詢近一個(gè)月每天的數(shù)據(jù)為示例展示SQL實(shí)現(xiàn)。

按天統(tǒng)計(jì)數(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)計(jì)數(shù)據(jù)沒有數(shù)據(jù)補(bǔ)零的SQL語句，按月按年等其它時(shí)間段，或其它分組條件，都可以直接修改參數(shù)后查詢出結(jié)果。

附：Mysql按日、周、月進(jìn)行分組統(tǒng)計(jì)

DATE_FORMAT 是 MySQL 內(nèi)置的一個(gè)函數(shù)，作用是以不同的格式顯示日期/時(shí)間數(shù)據(jù)。具體的語法如下：

DATE_FORMAT(date,format)

其中

date：合法的日期
format：規(guī)定日期/時(shí)間的輸出格式，其中format可使用的格式可以查看以下鏈接

下面我們通過具體例子來看如何通過 DATE_FORMAT 進(jìn)行分組統(tǒng)計(jì)：

下表兩列分別代表產(chǎn)品買出的準(zhǔn)確時(shí)間(精確到秒)，和買出的產(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)在我們需要對每天，每周，每月各個(gè)產(chǎn)品的銷量進(jìn)行統(tǒng)計(jì)，

1)按天統(tǒng)計(jì)：

select DATE_FORMAT(start_time,'%Y%m%d') days,count(product_no) count from test group by days;

2)按周統(tǒng)計(jì)：

select DATE_FORMAT(start_time,'%Y%u') weeks,count(product_no) count from test group by weeks;

3)按月統(tǒng)計(jì):

select DATE_FORMAT(start_time,'%Y%m') months,count(product_no) count from test group bymonths;

總結(jié)

到此這篇關(guān)于MySQL按天分組統(tǒng)計(jì)一定時(shí)間內(nèi)的數(shù)據(jù)(沒有數(shù)據(jù)補(bǔ)0)的文章就介紹到這了,更多相關(guān)MySQL按天分組統(tǒng)計(jì)數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: