mysql按日期分组(group by)查询统计的时候,没有数据补0的解决办法。

原创 2016年03月14日 16:53:49

写博客真实个费时费力的差事,好佩服那些博客阅读几十上百万的人。今天研究了半天按照日期分组统计,没有数据就为空了,我要让他显示0呀。想了办法,都没有找到一个好的,解决方案,然后用了一个很low的方法实现了,还是把它记录下来。方法是用于mysql,与开发语言无关。

1、案例中的数据结构和数据如下
这里写图片描述

2、在没有解决的时候,是这样的

SELECT date(downtime) AS dday, count(*) AS num FROM re_device GROUP BY dday

得到如下结果,如果那天没有数据,那麽就会没有记录
这里写图片描述
我们看到,时间不连续,没有2016-3-05这一天的,这样本来不是问题,但是,我拿出来的数据,还要画出图表呀,没有当然不行,我们需要的是下面这个样子的。
这里写图片描述

简单的说就是,没有数据,就要补充一个0.

3、下面我们讲实现

我们要生成一个日历的表,然后和原来的数据,联合查询,说到这里,大家就知道很low了,但是,限于我水平有限,研究这个问题,半天,这个是我找到的比较好的一种实现方式。如果你又更好的,也请你给我说下。
执行下面的sql,直接诶生成日历的表(calendar)

CREATE TABLE num (i int);-- 创建一个表用来储存0-9的数字
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- 生成0-9的数字,方便以后计算时间

CREATE TABLE  if not exists calendar(datelist date); -- 生成一个存储日期的表,datalist是字段名

-- 这里是生成并插入日期数据
INSERT INTO calendar(datelist) SELECT
    adddate(
        (   -- 这里的起始日期,你可以换成当前日期
            DATE_FORMAT("2016-1-1", '%Y-%m-%d') 
        ),
        numlist.id
    ) AS `date`
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
        CROSS JOIN num AS n10000
    ) AS numlist;

这里我用了100000条记录,算出来到2289年了,完全够用了,到那个时候,出问题,我也管不了了。

完成之后,请删除num的零时表

4、联合查询

SELECT
    date(dday) ddate,
    count(*) - 1 as num
FROM
    (
        SELECT
            datelist as dday
        FROM
            calendar 
            -- 这里是限制返回最近30天的数据
            where  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(datelist)&&date(datelist)<=CURDATE() 
        UNION ALL
            SELECT
                downtime
            FROM
                re_device
    ) a
GROUP BY ddate

好了,到这里,基本就完成了这个查询,出来的数据,我还是比较满意的。

5、其他解决方法
当然,应该还有其他的解决方案,但是博主就没有去写了,有时间可以去写一下。
我用的spring mvc,所以,也还是可以在java代码中补充完整的,因为数据返回的是个map对象,那麽我们要遍历这个对象,直接用calendar对象,生成日期作为key来遍历,如果没有数据,就put进去一个0,然后在限制一下,需要多少天的,就可以了。数据就完整了。
但是这样也有一个问题,那就是map里的数据顺序会有问题,所以,使用的时候,也必须是生成calendar对象,然后构造出来日期作为key来遍历。或者用Collections.sort()排序一下。

我个人觉得,还是上数据直接生成得比较好一点。

版权声明:本文为博主原创文章,未经博主允许不得转载。

mysql查询最近7天的数据,没有数据自动补0

问题描述查询数据库表中最近7天的记录select count(*),date(create_time) as date from task where datediff(now(),create_ti...
  • ouyang111222
  • ouyang111222
  • 2017年08月28日 10:05
  • 2037

mysql查询连续时间数据——无数据补0

用D3来画统计图,数据是从mysql数据库中查出来的。 统计图是关于某段时间内,每年/每月/每周的微博数量的折线图。数据表是一条微博为一条记录,每条记录有日期这个字段。 首先考虑用group by来统...
  • u012603073
  • u012603073
  • 2016年03月03日 11:38
  • 10571

【SQL心得】:在含有GROUP BY的SELECT语句中如何显示COUNT()为0的结果

【SQL心得】:在含有GROUP BY的SELECT语句中如何显示COUNT()为0的结果1.问题 ID Flag CategoryID 1 1 1 2 1 1 3...
  • u011011307
  • u011011307
  • 2015年04月19日 14:08
  • 3468

mysql group by 无数据补0 方法

来看下这个SQL,这个SQL的意思是获取需求ID的数量及需求价格 并按需求类型分组显示。 1 2 3 4 5 SELECT  `require_type_id` , C...
  • AyuSilence
  • AyuSilence
  • 2015年09月15日 16:09
  • 4271

mysql中group by分组后查询无数据补0;

mysql经常会用到Group By来进行分组查询,但也经常会遇到一个问题,就是当有where条件时,被where条件过滤的数据不显示了。 例如我有一组数据: 我想查询当日领取数量和当日核销数...
  • ljxbbss
  • ljxbbss
  • 2017年09月20日 12:04
  • 838

mysql group by 涉及到的字段有空数据问题

统计数据需要按省份分组,这是大家经常遇到的问题。今天遇到一奇葩问题,由于省份(province)字段数据不规范, 有的是省份名称,有的是 "" (空)  有的是 "未知"。这时 group by p...
  • wang740209668
  • wang740209668
  • 2015年11月12日 18:05
  • 2467

mysql 按月统计数据 没有数据按0补全

也借鉴了网上的做法,但不全相同,这里补全。 CREATE TABLE num (i int);-- 创建一个表用来储存0-9的数字 INSERT INTO num (i) VALUES (0)...
  • yuke98727
  • yuke98727
  • 2017年02月09日 18:03
  • 1877

sql的分组统计与group by 日期的处理

近几天补oracle的sql知识,这块记录下sql的分组统计 1.简单的分组统计 创建STUDENT表: CREATE TABLE STUDENT( "NAME" VARCHAR2(10 BYT...
  • u010652906
  • u010652906
  • 2015年07月27日 03:47
  • 936

sql分别用日期、月、年 分组 group by 分组,datepart函数

--以2013-12-10 12:56:55为例 --convert(nvarchar(10),CreateDate,120)      =>      2013-12-10 --DATEPART(m...
  • KingCruel
  • KingCruel
  • 2013年12月26日 15:31
  • 26583

ORACLE用GROUP BY 来分组日期字段 按月分组

SELECT to_char(日期字段,'yyyymm'),sum(*) FROM [TableName] group by to_char(日期字段,'yyyymm');...
  • linghao00
  • linghao00
  • 2012年03月08日 11:06
  • 13319
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql按日期分组(group by)查询统计的时候,没有数据补0的解决办法。
举报原因:
原因补充:

(最多只允许输入30个字)