博客

我这边先展示下需求大概是什么样的:

根据时间区间,展示每个节点的数据,形成曲线图。

我的第一种方法是用mysql的函数:

SELECT
        @a :=@a + 1 AS `index`,
        DATE_FORMAT(ADDDATE( #{startDate} ,INTERVAL @a DAY),'%Y-%m-%d') AS date
        FROM
        mysql.help_topic,(SELECT @a:=-1) temp
        WHERE
        @a <  (YEAR( #{endDate} )-YEAR( #{startDate} ))*365 + (MONTH( #{endDate} )- MONTH( #{startDate} ))*30  + (DAY( #{endDate} )- DAY( #{startDate} ))

第一种情况是以每天为单位去统计,先利用mysql的help_topic表,生成以序号为key,时间为value的列表数据。

执行一下：

然后再连接我们自己的数据表(t1就是上面生成的数据表别名):

 LEFT JOIN
        (
        SELECT
        date_format(sel.login_time, '%Y-%m-%d') AS dateDay,
        count(*) as count
        FROM
        xxx_log sel
        WHERE
            sel.login_time > #{startDate} 
            AND sel.login_time < #{endDate}
        group by
        date_format(sel.login_time, '%Y-%m-%d')) AS t2
        ON t1.date = t2.dateDay

最后用select把整个sql合并,并在查询字段count里判断是否为空,为空就补0,最后根据字段去排序。

SELECT
        c.date AS dateMonth,
        IFNULL(c.count,0) AS countMonth FROM (
        SELECT * FROM ( {第一段sql} ) AS t1
        LEFT JOIN
        ({第二段sql}) AS t2  
        ON t1.date = t2.dateMonth) AS c ORDER BY DATEMONTH desc

执行一下:

结果:

完美~~从20年9月1号查到21年10月11号~~~~~

如果是以月或者年为间隔去做,就修改下sql,我写个参考吧。

年:

				
SELECT
        @a :=@a + 1 AS `index`,
        DATE_FORMAT(ADDDATE( #{startTime} ,INTERVAL @a MONTH),'%Y-%m') AS date
        FROM
        mysql.help_topic,(SELECT @a:=-1) temp
        WHERE
        @a < (YEAR( #{endTime} )-YEAR( #{startTime} ))*12+ MONTH( #{endTime} )- MONTH( #{startTime} )
        				

第二段代码把时间格式改为yyyy-mm,sql

:

结果:

年就更简单了:

这种方法虽然看上去只要操作数据库就可以完成,但是也是有限制性的,比如第一段sql,其实是查询的mysql.help_topic表,这张表里的数据是有限制的

所以第一段sql生成的序号是有限制的,超过了这个条数,就无法补0了。解决方法就是,要么换张表,专门做一张序号表,用来关联,再就是在代码中处理了，我这里用了第二种方法。

第二种方法,是把时间,通过年/月/日做切片:

/**
	 * 日期范围 - 切片
	 * @param startDate 起始日期
	 * @param endDate 结束日期
	 * @param type 切片类型 1-年 2-月 3-日 4-小时
	 * @return 切片日期
	 */
	public static List<String> sliceUpDateRange(String startDate, String endDate ,int type ) {
		List<String> rs = new ArrayList<>();

		try {
			int dt = Calendar.DATE;
			String pattern = "yyyy-MM-dd";
			if (type==1) {
				pattern = "yyyy";
				dt = Calendar.YEAR;
			} else if (type==2) {
				pattern = "yyyy-MM";
				dt = Calendar.MONTH;
			} else if (type==3) {
				pattern = "yyyy-MM-dd";
				dt = Calendar.DATE;
			}else if (type==4) {
				pattern = "yyyy-MM-dd HH";
				dt = Calendar.HOUR_OF_DAY;
			}
			SimpleDateFormat sd = new SimpleDateFormat(pattern);
			Calendar sc = Calendar.getInstance();
			Calendar ec = Calendar.getInstance();
			sc.setTime(sd.parse(startDate));
			ec.setTime(sd.parse(endDate));
			while (sc.compareTo(ec) < 1) {
				rs.add(sd.format(sc.getTime()));
				sc.add(dt, 1);
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return rs;
	}

用上面的方法把切割完的日期,放进sql中


union,嘿嘿,看代码(以每天为例):

select
	od.date,od.count from
		(SELECT DATE_FORMAT(SIGN_TIME, '%Y-%m-%d') as date,COUNT(*) as count
		FROM xxxx_log
		<where> 
            STATE = #{state}
			AND SIGN_TIME <![CDATA[ > ]]> #{param.createStartTime, jdbcType = VARCHAR}
			AND SIGN_TIME <![CDATA[ < ]]> #{param.createEndTime, jdbcType = VARCHAR}
		</where>
		group by DATE_FORMAT(SIGN_TIME, '%Y-%m-%d')
		union
		<foreach collection="list" item="item" index="index" separator="union">
			(select #{item},0)
		</foreach>
		)as od GROUP BY od.date ORDER BY od.date asc

这样的问题就是,sql会被union的特别长,最后一种方法就是在代码中补数据咯,只是会用循环遍历,也可以实现(不考虑性能的话,其实都可以)

第二种这种把时间切片的好处,就是union可以实现年/月/日/小时的兼容,代码比较整洁.