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 

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

SELECTa.click_date AS commitTime,ifnull( b.count, 0 ) AS `amount`FROM(SELECTDATE_FORMAT( curdate( ), '%Y-%m' ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 1 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 2 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 3 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 4 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 5 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 6 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 7 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 8 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 9 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 10 MONTH ), 1, 7 ) AS click_date UNION ALLSELECTSUBSTRING( date_sub( DATE_FORMAT( curdate( ), '%Y-%m-%d' ), INTERVAL 11 MONTH ), 1, 7 ) AS click_date ) aLEFT 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 BYcommitTime 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_dateunion allSELECT SUBSTRING(date_sub(DATE_FORMAT(curdate(), '%Y-%m-%d'), interval ${index+1} month),1,7) as click_date) a left join (select date(FROM_UNIXTIME(create_time/1000,'%Y-%m')) as datetime, count(*) as countfrom ${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 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("")List holeCountDistributionByMoth(@Param("months") int[] months);

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

	@Select("")List 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);}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！