1. 首页
  2. 文章中心
  3. 问题

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

181 阅读 0 评论
我是靠谱客的博主 沉静寒风，这篇文章主要介绍mysql按月统计累加数据，不用复杂SQL，现在分享给大家，希望可以做个参考。

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

复制代码
1
2
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

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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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]

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
 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.然后创建类

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.实现

复制代码
1
2
3
4
5
6
7
8
9
10
			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查询

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
	/**
	 * 最近多少月之前的数量
	 * @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_daten" +
			"        <foreach collection="months" item="item" index="index">n" +
			"            union alln" +
			"            SELECT SUBSTRING(date_sub(DATE_FORMAT(curdate(), '%Y-%m-%d'), interval ${index+1} month),1,7) as click_daten" +
			"        </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天的原理类似：

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
	@Select("<script>" +
			" select a.click_date as commitTime ,ifnull(b.count,0) as `amount`n" +
			"            from (n" +
			"                SELECT curdate() as click_daten" +
			"                <foreach collection="days" item="item" index="index">n" +
			"                    union alln" +
			"                    SELECT date_sub(curdate(), interval ${index+1} day) as click_daten" +
			"                </foreach>n" +
			"            ) a left join (n" +
			"              select date(DATE_FORMAT(commit_time,'%Y-%m-%d'))  as datetime, count(*) as countn" +
			"              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);
复制代码
1
2
3
4
5
6
7
8
9
			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);
			}

最后

以上就是沉静寒风最近收集整理的关于mysql按月统计累加数据，不用复杂SQL的全部内容，更多相关mysql按月统计累加数据内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(120)

相关文章

sql server 查询本年的每个月的数据
sql server 查询本年的每个月的数据
sql --- 按分类，统计每类的数量
sql --- 按分类，统计每类的数量
sql按月份统计每个月的总数
sql按月份统计每个月的总数
数据库专题——SQL语句统计每天、每月、每年的数据
数据库专题——SQL语句统计每天、每月、每年的数据
mysql按月统计累加数据，不用复杂SQL
mysql按月统计累加数据，不用复杂SQL
SQL每日一题(20201107)希望将每个月之前的月份进行累加
SQL每日一题(20201107)希望将每个月之前的月份进行累加
[SQL] 按月累加逐级统计数据
[SQL] 按月累加逐级统计数据
date_format() 函数
date_format() 函数

评论列表共有 0 条评论

立即
投稿
返回
顶部