mysql查询一个时间段每天数据数量，没有显示为0

比眉伴天荒 2023-05-22 08:54 1阅读 0赞

# 环境 #

jdk 1.8  
mysql 5.6  
mybatis 3.4.4  
springboot 2.2.6

# 需求 #

> 按照时间段来查询显示该时间段内每一天的数据量，如果某一天没有数据，显示数据量为0.

## 按照正常思路直接使用**GROUP BY**分组查询即可，运行如下sql ##

SELECT 
    	COUNT(1) as `count`,date_format(u.createtime,'%Y-%m-%d') as `date`
    FROM
    	user u
    GROUP BY date_format(u.createtime,'%Y-%m-%d')

果然，是不行的，没有数据的天数没有显示出来  
![在这里插入图片描述][2020041709130035.png]

## 解决方案 ##

*  查询日期表，外连接数据表来显示  
    网络上查询到生成日期表的方法有几种

1.  需要另建一张日期表，直接从此表查询（比较麻烦）
2.  使用笛卡尔积生成时间（推荐）  
    基础sql如下

select date_add('2020-04-01',interval @i:=@i+1 day) as date 
    from (
    select 1 
    union all select 1 
    union all select 1
    union all select 1 
    union all select 1 
    union all select 1 
    union all select 1 
    union all select 1) as tmp,
     (select @i:= -1) t

结果如下  
![在这里插入图片描述][20200417092816452.png]

这样我们就得到了一段连续的日期了，然后只需要关联查询即可显示了

select count(u.id) as count,s.date from 
    (select date_add('2020-04-01',interval @i:=@i+1 day) as date 
    from (
    select 1 
    union all select 1 
    union all select 1
    union all select 1 
    union all select 1 
    union all select 1 
    union all select 1 
    union all select 1) as tmp,
     (select @i:= -1) t
    ) s left join user u on s.date = date_format(u.createtime,'%Y-%m-%d')
    GROUP BY s.date

查询结果

![在这里插入图片描述][2020041711061043.png]  
现在还有一个问题，如何控制生成多少个日期，也就是**union all select 1** 的个数，

1.  使用存储过程（耦合性过高，不易维护，暂不考虑）
2.  mybatis 循环拼接（√）

选择了java代码计算日期差，使用mybatis的 **foreach** 标签实现sql拼接，全部代码如下

*  service

@Override
        public Object queryByDate(String[] date) { 
            String date1 = date[0];
            String date2 = date[1];
            int num = calcBetweenDate(date1, date2);
            String[] countArr = new String[num];
            return userMapper.queryByDate(date1,countArr);
        }
        
        public int calcBetweenDate(String start, String end) {   
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");  
            Date startDate = null;  
            Date endDate = null;  
            try {   
                startDate = df.parse(start);  
                endDate = df.parse(end);  
            } catch (Exception e) {   
                System.out.println("日期转换出错");  
            }  
            int count = (int) ((endDate.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000));  
            return count;  
        }

*  mapper

List<Map<String,Object>> queryByDate(@Param("date")String date, @Param("countArr")String[] countArr);

*  xml

<select id="queryByDate" resultType="java.util.HashMap">
    	   select count(u.id) as count,s.date from 
    		(select date_add('2020-04-01',interval @i:=@i+1 day) as date 
    		from (
    		select 1 
    	   	<foreach item="index" collection="countArr">
    		  union all select 1 
    		</foreach>
    		) as tmp,
    		 (select @i:= -1) t
    		) s left join user u on s.date = date_format(u.createtime,'%Y-%m-%d')
    		GROUP BY s.date
    	</select>

# 效率检测 #

我们使用postman查询，检测效率,

*  查询日期 **11**天，耗时 **10-20ms** 左右

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L05ld19ZYW8_size_16_color_FFFFFF_t_70]

*  查询日期 **365**天，耗时 **30-40ms** 左右  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L05ld19ZYW8_size_16_color_FFFFFF_t_70 1]
 *  查询日期 **3650**天，耗时 **222ms** 左右  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L05ld19ZYW8_size_16_color_FFFFFF_t_70 2]
 *  查询日期 **5000**天，爆炸。。

Cause: java.sql.SQLException: Thread stack overrun:  246144 bytes used of a 262144 byte stack, and 16000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.
    ; uncategorized SQLException; SQL state [HY000]; error code [1436]; Thread stack overrun:  246144 bytes used of a 262144 byte stack, and 16000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.; nested exception is java.sql.SQLException: Thread stack overrun:  246144 bytes used of a 262144 byte stack, and 16000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.]
    3662

# 注意事项 #

本方式仅适用于小范围时间查询，查询时间在上方，建议自己测试，若天数间隔大于10年及以上，可能会导致mybatis报错，若长时间段查询不建议使用此方式！

[2020041709130035.png]: /images/20230521/ca419a97a90f4842909a270979893140.png
[20200417092816452.png]: /images/20230521/c0f57daff41141aca53743550a1adaa7.png
[2020041711061043.png]: /images/20230521/e2374e5c06184f34b13fce5af7572db3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L05ld19ZYW8_size_16_color_FFFFFF_t_70]: /images/20230521/5b46842418ec4f6090549a4bbaed3cca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L05ld19ZYW8_size_16_color_FFFFFF_t_70 1]: /images/20230521/03b164d8d9ea4368ad8d5047d22716ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L05ld19ZYW8_size_16_color_FFFFFF_t_70 2]: /images/20230521/cfaecb8d81ec49f08298059214432361.png