快捷導(dǎo)航

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

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

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

簡介

工作中偶爾會出現(xiàn)一個查詢數(shù)據(jù)的需求，那就是需要按天統(tǒng)計近一個月或其它一段時間內(nèi)每天的所有記錄或者分組數(shù)據(jù)，沒有數(shù)據(jù)則自動補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)行判斷是否補零。

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ù)補零的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ù)補0)的文章就介紹到這了,更多相關(guān)MySQL按天分組統(tǒng)計數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: