笛卡尔积 查询时间周期内数据统计列表

环境

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')

果然，是不行的，没有数据的天数没有显示出来

解决方案

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

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

结果如下

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

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

查询结果

现在还有一个问题，如何控制生成多少个日期，也就是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 左右

• 查询日期 365天，耗时 30-40ms 左右
  

• 查询日期 3650天，耗时 222ms 左右
  

• 查询日期 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报错，若长时间段查询不建议使用此方式！