mysql汇总一周每日新增数及累计数

背景

疫情期间需要统计每日新增确诊人数以及累计确诊人数，只查询连续一周数据。

测试表结构与数据

新建一张确诊人员信息表，记录确诊人员姓名以及确诊日期。

初步思路

要统计每日新增人数，首先想到根据日期分组。

SELECT
	count( 1 ) daily_new,
	time 
FROM
	confirmed_population 
GROUP BY
	time;

按日期分组后的结果并不是连续的，由于我们需要连续一周的数据，需要关联下固定的日期。 比如我们需要查询下从2020-03-13开始过去一周日期。

SELECT
	DATE( DATE_SUB( 2020-03-13, INTERVAL @s := @s + 1 DAY ) ) AS `date` 
FROM
	mysql.help_topic,
	( SELECT @s := - 1 ) temp 
WHERE
	@s < 7 
ORDER BY
	`date`;

这里的开始日期和天数都可以作为参数修改。

一周每日新增数

有了上述的基础准备我们已经可以查询连续一周的每日新增数。

SELECT
	base.date,
	ifnull( daily_new, 0 ) daily_new 
FROM
	(
	SELECT
		DATE( DATE_SUB( 2020-03-13, INTERVAL @s := @s + 1 DAY ) ) AS `date` 
	FROM
		mysql.help_topic,
		( SELECT @s := - 1 ) temp 
	WHERE
		@s < 7 
	ORDER BY
		`date` 
	) base
	LEFT JOIN ( SELECT count( 1 ) daily_new, time FROM confirmed_population GROUP BY time ) res ON base.date = res.time 
ORDER BY
	base.date;

通过每日新增数计算累计数

现在就到了最关键的一步，将每日新增数汇总成累计值。 这里需要使用到mysql的自定义变量，与上面查询日期时一样通过’@变量名’使用。

SELECT
	date,
	daily_new,
	@total := @total + daily_new AS total 
FROM
	(
	...
	) AS temp,
	( SELECT @total := 0 ) tab1;

为了看起来更清晰使用省略号代替查询一周每日新增数部分。

优化

到上一步基本就满足了查询要求，但是还有一个问题： 累计数是从0开始累计的，所以我们还需要计算下基础累计值。

SELECT
	@total := count( 1 ) 
FROM
	confirmed_population 
WHERE
	time < date_add( 2020-03-13, INTERVAL - 7 DAY )

最后合并完成的完整sql如下：

SELECT
	date,
	daily_new,
	@total := @total + daily_new AS total 
FROM
	(
	SELECT
		base.date,
		ifnull( daily_new, 0 ) daily_new 
	FROM
		(
		SELECT
			DATE( DATE_SUB( 2020-03-13, INTERVAL @s := @s + 1 DAY ) ) AS `date` 
		FROM
			mysql.help_topic,
			( SELECT @s := - 1 ) temp 
		WHERE
			@s < 7 
		ORDER BY
			`date` 
		) base
		LEFT JOIN ( SELECT count( 1 ) daily_new, time FROM confirmed_population GROUP BY time ) res ON base.date = res.time 
	ORDER BY
		base.date 
	) AS temp,
	( SELECT @total := count( 1 ) FROM confirmed_population WHERE time < date_add( 2020-03-13, INTERVAL - 7 DAY ) ) tab1;

后记

sql写完后写到代码中查询时会报错(使用的mybatis)

MybatisPlusException: Failed to process, please exclude the tableName or statementId.

百度了下报错原因是因为项目使用了多租户，解析sql时会追加例如tenant_id等条件。 在接口上增加注解即可解决。

@SqlParser(filter = true)