MySQL按天分组统计一定时间内的数据实例(没有数据补0)
简介
工作中偶尔会出现一个查询数据的需求，那就是需要按天统计近一个月或其它一段时间内每天的所有记录或者分组数据，没有数据则自动补0。
一般情况下我们都会过滤没有数据的时间，但前端拿到后端返回的数据想直接展示连续性数据变化时要进行再处理，下面介绍如何通过sql语句直接满足需求。
思路分析
- 如果能每天都有数据，可以直接使用最简单的查询节省时间。
- 要能够展示每天的日期就要创建一张日期虚拟表作为连接表。
- 要使每天数据不为null，使用
IFNULL(count,0)函数进行判断是否补零。
SQL实现
下面以查询近一个月每天的数据为示例展示SQL实现。
按天统计数据
1.没数据的一天过滤
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);
查询结果：
2.没数据的一天有日期总数为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;
查询结果：
3.没数据的一天有日期总数为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;
查询结果
4.加入其它分组字段没数据的一天有日期总数为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;
查询结果
(中间过多数据不展示)
以上就是按天统计数据没有数据补零的SQL语句，按月按年等其它时间段，或其它分组条件，都可以直接修改参数后查询出结果。
附：Mysql按日、周、月进行分组统计
DATE_FORMAT 是 MySQL 内置的一个函数，作用是以不同的格式显示日期/时间数据。具体的语法如下：
DATE_FORMAT(date,format)
其中
- date：合法的日期
- format：规定日期/时间的输出格式，其中format可使用的格式可以查看以下链接
下面我们通过具体例子来看如何通过 DATE_FORMAT 进行分组统计：
下表两列分别代表产品买出的准确时间(精确到秒)，和买出的产品类型。
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
现在我们需要对每天，每周，每月各个产品的销量进行统计，
1)按天统计：
select DATE_FORMAT(start_time,'%Y%m%d') days,count(product_no) count from test group by days;2)按周统计：
select DATE_FORMAT(start_time,'%Y%u') weeks,count(product_no) count from test group by weeks;3)按月统计:
select DATE_FORMAT(start_time,'%Y%m') months,count(product_no) count from test group bymonths;总结
到此这篇关于MySQL按天分组统计一定时间内的数据(没有数据补0)的文章就介绍到这了,更多相关MySQL按天分组统计数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/51.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
