请选择 进入手机版 | 继续访问电脑版
设为首页收藏本站

jeesite

 找回密码
 注册新会员
查看: 176|回复: 0

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

[复制链接]
admin 发表于 2018-7-11 11:03:28 | 显示全部楼层 |阅读模式
  1. https://blog.csdn.net/hblfyla/article/details/53198951?locationNum=2&fps=1
复制代码

https://blog.csdn.net/hblfyla/ar ... tionNum=2&fps=1

http://blog.sina.com.cn/s/blog_9e9f16d10102xch2.html

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

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

SELECT date(downtime) AS dday, count(*) AS num FROM re_device GROUP BY dday
1
1
得到如下结果,如果那天没有数据,那麽就会没有记录
这里写图片描述
我们看到,时间不连续,没有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()排序一下。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册新会员

本版积分规则

QQ|手机版|小黑屋|Archiver|jeesite 官方论坛. ( 吉ICP备12004769号  

GMT+8, 2018-11-17 11:25 , Processed in 0.134765 second(s), 22 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表