Java 后端接口 ECharts 日历表统计每年每月每日的数据条数 ECharts Java 后端接口统计 利用日历表统计 MySQL 数据条数

由于需求问题需要一个统计的接口，怕以后忘记以此记录保存。

平时做的统计一般很容易就能做好，一般就是分一下组，做一下聚合函数就可以完成。但是有些情况下我们需要统计没有数据的某些天、某些月、某些年，也就是为没有数据的那些天、那些月、那些年进行一个补 0 操作。

平常的统计查询这样就可以完成：

一般的统计查询这样也够用了

但是如果说我们用在 ECharts 中会有点儿不足，因为我们会发现 7 月 10 号和 7月 12 号这两天没有数据，然后也不会显示出来。这就很麻烦，一个统计图总不能缺胳膊少腿吧，也就是说没有数据的这两天我们需要进行一个补0的一个操作。

 我们想要的查询出来的数据格式是这样的：

可以看到 7 月 10 号和 7 月 12 号即使那两天没有数据也可以进行统计出来，这也就是我们想要的数据格式，（有点儿懒就填充了这几条数据）

接下来看实现步骤

主要分为三个部分数据库、后端、前端。分别使用的是 MySQL、Java、Vue、ElementUI、ECharts

代码：

一、数据库

先用 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是字段名

利用以下 SQL 向 calendar 日历表插入个几百年的数据

-- 这里是生成并插入日期数据
INSERT INTO calendar(datelist) SELECT
    adddate(
        (   -- 这里是生成的起始日期，你可以换成当前日期
            DATE_FORMAT("2022-01-01", '%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;

执行成功后查看

我这里生成了 100 页的数据 时间到了 2295 年，有点恐怖，这肯定是够用了 . . .

感觉有点太多了，还是删除一下多余的数据吧，我这里留了 100 年，把 2122 年后的数据都删了，可以酌情调整

delete from calendar where datelist > '2122-12-31';

 现在日历表也做好了，开始写 SQL

1.现在统计一下本周每一天的数据，时间我这里写成固定的了，等下下方我会贴出获取本周周一到周日的时间的获取方法：

-- 需要修改成自己的地方有三处 no.1、no.2、no.3
SELECT c.datelist as date, COUNT(b.id) as count
        FROM calendar AS c -- 日历表，起别名为 c
        LEFT JOIN (
            SELECT * FROM table_name -- 要连接的表名称  no.1
            WHERE table_id = '1' -- 这里可以拼接自己的条件  no.2
        ) AS b ON c.datelist = DATE(b.create_time) -- DATE(这里放字段名) 因为我是年月日时分秒形式的，所以需要用 DATE 函数格式化一下
        WHERE c.datelist >= '2022-07-04' and c.datelist <= '2022-07-10' -- 这里限制一下查询出来数据的时间  no.3
        GROUP BY datelist 
        ORDER BY datelist 

注意：写到 mapper.xml 的时候需要把 where 条件后面的 >= 和 <= 改成对应的转义符 &gt;= 和 &lt;=

获取本周周一的 00:00:00 到周日的 23:59:59 的时间方法代码如下：

    /**
     * 获取当前周的第一天和最后一天
     * @date 2022/7/14 10:21
     * @return map
     **/
    public Map getCurrentWeekTimeFrame() {
        Calendar calendar = Calendar.getInstance();
        calendar.setTimeZone(TimeZone.getTimeZone("GMT+8"));
        //start of the week
        calendar.add(Calendar.DAY_OF_WEEK, -(calendar.get(Calendar.DAY_OF_WEEK) - 2));
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        long startTime = calendar.getTimeInMillis();
        //end of the week
        calendar.add(Calendar.DAY_OF_WEEK, 6);
        calendar.set(Calendar.HOUR_OF_DAY, 23);
        calendar.set(Calendar.MINUTE, 59);
        calendar.set(Calendar.SECOND, 59);
        calendar.set(Calendar.MILLISECOND, 999);
        long endTime = calendar.getTimeInMillis();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String begin = sdf.format(startTime);
        String end = sdf.format(endTime);
        Map<String, String> map = new HashMap<String, String>(16);
        map.put("begin", begin);
        map.put("end", end);
        return map;
    }

接下来执行 SQL ，结果：

符合预期结果

2.统计本月每一天的数据，类似于上面的统计本周数据：

-- 需要修改成自己的地方有三处 no.1、no.2、no.3
SELECT c.datelist as date, COUNT(b.id) as count
        FROM calendar AS c -- 日历表，起别名为 c
        LEFT JOIN (
            SELECT * FROM table_name -- 要连接的表名称  no.1
            WHERE table_id = '1' -- 这里可以拼接自己的条件  no.2
        ) AS b ON c.datelist = DATE(b.create_time) -- DATE(这里放字段名) 因为我是年月日时分秒形式的，所以需要用 DATE 函数格式化一下
        WHERE c.datelist like '2022-07%' -- 这里限制一下查询出来数据的时间，使用模糊查询  no.3
        GROUP BY datelist 
        ORDER BY datelist 

其他的都不变，与上面不同的是修改的地方 no.3 把区间查询改为了模糊查询，可以获取当前时间，然后截取一下字符串，把年月留下来当为参数传入就可以了

查本月每一天和今年每一月的时候千万记住 like 后面跟的时间的参数最后要带一个百分号，这样才能做到模糊查询的效果，数据才可能是正确的，切记！

 查询结果：

3.统计今年每月的数据，和上方统计本月每日的统计方法几乎一样，就是把模糊查询的时间从年月，变成了年