mysql按月统计累加数据，不用复杂SQL

今天有个需求，统计累计数据趋势。开始的时候我在查询怎么通过SQL 一下查出来每个月的累计，查出来的结果，是不连续的，

SELECT commit_time as commitTime,amount,(@var := @var + amount) as holeCount FROM (SELECT DATE_FORMAT(commit_time,'%Y年%m月') commit_time ,count(hole_code) amount FROM hole_info WHERE hole_status='pass'  GROUP BY DATE_FORMAT(commit_time,'%Y年%m月'))a,(SELECT @var:=0)T 

日期是不连续的，这样不太好，然后

SELECT
	a.click_date AS commitTime,
	ifnull( b.count, 0 ) AS `amount`

FROM
	(
	SELECT
		DATE_FORMAT( curdate( ), '%Y-%m' ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 1 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 2 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 3 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 4 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 5 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 6 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 7 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 8 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 9 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 10 MONTH ), 1, 7 ) AS click_date UNION ALL
	SELECT
		SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 11 MONTH ), 1, 7 ) AS click_date 
	) a
	LEFT JOIN (
	SELECT DATE_FORMAT(commit_time,'%Y-%m') datetime ,count(hole_code) count FROM hole_info WHERE hole_status='pass'  GROUP BY DATE_FORMAT(commit_time,'%Y-%m')
	) b ON a.click_date = b.datetime 
ORDER BY
	commitTime ASC;

查询出最近12个月的数据，不存在数据的月份补0

然后通过java代码实现累加。

大概思路如下：
1.首先统计每个月的数量，统计近12个月的数据，为空补0，java中int[] months= new int[11]

 select a.click_date as commitTime,ifnull(b.count,0) as `amount`
        from (
        SELECT DATE_FORMAT(curdate(), '%Y-%m') as click_date
        <foreach collection="months" item="item" index="index">
            union all
            SELECT SUBSTRING(date_sub(DATE_FORMAT(curdate(), '%Y-%m-%d'), interval ${index+1} month),1,7) as click_date
        </foreach>
        ) a left join (
        select date(FROM_UNIXTIME(create_time/1000,'%Y-%m')) as datetime, count(*) as count
        from ${tableName}
        group by date(FROM_UNIXTIME(create_time/1000,'%Y-%m'))
        ) b on a.click_date = b.datetime;

2.然后创建类

package com.springcloud.base.flawserver.bean.dto;
import lombok.Data;
/**
 *
 * 漏洞数量统计
 * @ClassName: HoleTypeDistribution
 * @Author: yongtao.ding  
 */
@Data
public class HoleCountDistributionDTO {
	private String commitTime;
	private Integer amount;
	private Integer holeCount;
}

3.实现

			List<HoleCountDistributionDTO> holeCountDistributionDTOS = new ArrayList<>();
			int[] months = new int[11];
			Integer holeCount = statisticsMapper.holeCountByLateMonth(11);
			holeCountDistributionDTOS = statisticsMapper.holeCountDistributionByMoth(months);
			for (HoleCountDistributionDTO holeCountDistributionDTO : holeCountDistributionDTOS) {
				Integer amount = holeCountDistributionDTO.getAmount();
				holeCount = holeCount + amount;
				holeCountDistributionDTO.setHoleCount(holeCount);
			}

4.mapper的sql查询

	/**
	 * 最近多少月之前的数量
	 * @param month
	 * @return
	 */
	@Select("(SELECT count(0), SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL #{month} MONTH ), 1, 7 )   from hole_info WHERE hole_status='pass' and  DATE_FORMAT(commit_time,'%Y-%m') < SUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL #{month} MONTH ), 1, 7 ))")
	Integer holeCountByLateMonth(@Param("month") int month);

	/**
	*  sql中替换表名和时间就可以了
	*/
	@Select("<script> " +
			"select a.click_date as commitTime ,ifnull(b.count,0) as `amount`\n" +
			"        from (\n" +
			"        SELECT DATE_FORMAT(curdate(), '%Y-%m') as click_date\n" +
			"        <foreach collection=\"months\" item=\"item\" index=\"index\">\n" +
			"            union all\n" +
			"            SELECT SUBSTRING(date_sub(DATE_FORMAT(curdate(), '%Y-%m-%d'), interval ${index+1} month),1,7) as click_date\n" +
			"        </foreach>\n" +
			"        ) a left join (\n" +
			"SELECT DATE_FORMAT(commit_time,'%Y-%m') datetime ,count(hole_code) count FROM hole_info WHERE hole_status='pass'  GROUP BY DATE_FORMAT(commit_time,'%Y-%m')"+
			"        ) b on a.click_date = b.datetime ORDER BY commitTime ASC" +
			"</script>")
	List<HoleCountDistributionDTO> holeCountDistributionByMoth(@Param("months") int[] months);

统计最近30天的原理类似：

	@Select("<script>" +
			" select a.click_date as commitTime ,ifnull(b.count,0) as `amount`\n" +
			"            from (\n" +
			"                SELECT curdate() as click_date\n" +
			"                <foreach collection=\"days\" item=\"item\" index=\"index\">\n" +
			"                    union all\n" +
			"                    SELECT date_sub(curdate(), interval ${index+1} day) as click_date\n" +
			"                </foreach>\n" +
			"            ) a left join (\n" +
			"              select date(DATE_FORMAT(commit_time,'%Y-%m-%d'))  as datetime, count(*) as count\n" +
			"              from hole_info WHERE  hole_status='pass'\n" +
			"              group by date(DATE_FORMAT(commit_time,'%Y-%m-%d'))\n" +
			"            ) b on a.click_date = b.datetime  order by commitTime Asc "+
			"</script>")
	List<HoleCountDistributionDTO> holeCountDistributionByWeek(@Param("days") int[] days);

	/**
	 * 最近多少天之前的漏洞数量
	 * @param day
	 * @return
	 */
	@Select("(SELECT count(0), DATE_SUB(CURDATE(),INTERVAL #{day} DAY)  from hole_info WHERE hole_status='pass' and commit_time< DATE_SUB(CURDATE(),INTERVAL #{day} DAY))")
	Integer holeCountByLateDay(@Param("day") int day);

			int[] days = new int[29];
			Integer holeCount= statisticsMapper.holeCountByLateDay(29);
			holeCountDistributionDTOS = statisticsMapper.holeCountDistributionByWeek(days);
			for (HoleCountDistributionDTO holeCountDistributionDTO : holeCountDistributionDTOS) {
				Integer amount = holeCountDistributionDTO.getAmount();
				holeCount = holeCount + amount;
				holeCountDistributionDTO.setHoleCount(holeCount);
			}