背景
疫情期间需要统计每日新增确诊人数以及累计确诊人数,只查询连续一周数据。
测试表结构与数据
新建一张确诊人员信息表,记录确诊人员姓名以及确诊日期。
初步思路
要统计每日新增人数,首先想到根据日期分组。
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)