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

超、凢脫俗 2022-10-23 12:40 357阅读 0赞

今天有个需求，统计累计数据趋势。开始的时候我在查询怎么通过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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjEzMzEx_size_16_color_FFFFFF_t_70]  
日期是不连续的，这样不太好，然后

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  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjEzMzEx_size_16_color_FFFFFF_t_70 1]  
然后通过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);
    			}

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjEzMzEx_size_16_color_FFFFFF_t_70]: /images/20221023/f5cb1c273cf14364b4c7aa2d0a191025.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjEzMzEx_size_16_color_FFFFFF_t_70 1]: /images/20221023/23bac919de264f2faf1b8192d64b7488.png