计算近几周数据然后汇总，sql问题

需求如图

img

get_water 为饮水量，没有的周数用0填充，如图，上一周没有任何数据

img

mysql 8.0以上版本,可以使用with递归补充中间缺失的数据。
但有点恶心的是mysql这个recursive只能放在第一个with后面,所以后面的统计每周的汇总sql只能一模一样写两次了

create table t_user_consume_log(
get_water DECIMAL(20,2),remark VARCHAR(200),flag TINYINT(1),
create_time datetime
);
insert into t_user_consume_log values (2600,'',1,now());
insert into t_user_consume_log values (2600,'',1,date_add(now(),interval -1 day));
insert into t_user_consume_log values (2600,'',1,date_add(now(),interval -7 day));
insert into t_user_consume_log values (2600,'',1,date_add(now(),interval -20 day));

with
recursive cte as (
select 0 wd,ifnull((select s from (
select 
WEEK( now() )- WEEK( create_time ) wd,
sum(get_water) s
from t_user_consume_log 
group by WEEK( now() )- WEEK( create_time )) d where wd=0),0) s 
union all
select cte.wd+1,ifnull(d.s,0) from cte left join (
select 
WEEK( now() )- WEEK( create_time ) wd,
sum(get_water) s
from t_user_consume_log 
group by WEEK( now() )- WEEK( create_time )) d on cte.wd+1=d.wd
where cte.wd<=11
)
select concat('近',wd+1,'周') 时间,
'17500ml' 目标饮水量,
concat(s,'ml') 实际饮水量,
concat(round(s/17500*100,2),'%') 完成进度,
case when s>=17500 then '达标' else '不达标' end 是否达标
from cte

img

如果是mysql8.0以下,可以构造一个虚拟的列(可以用系统表),然后left join 上面汇总好的数据

select concat('近',help_topic_id+1,'周') 时间,
'17500ml' 目标饮水量,
concat(ifnull(s,0),'ml') 实际饮水量,
concat(round(ifnull(s,0)/17500*100,2),'%') 完成进度,
case when s>=17500 then '达标' else '不达标' end 是否达标
from mysql.help_topic a left join (select 
WEEK( now() )- WEEK( create_time ) wd,
sum(get_water) s
from t_user_consume_log 
group by WEEK( now() )- WEEK( create_time )) b 
on b.wd=a.help_topic_id
where help_topic_id<=13

select TIMESTAMPDIFF(week,create_time,now()) weeks,sum(get_water) from 表名 group by weeks

先求每一周的用水量。将其作为子查询去查你要的效果。

提一个我的理解
首先所谓近一周的概念，真的按日历周期去算很复杂，就以近七天为近一周来理解
其次，需要标识的周期无数据展示为零，也就是需要维护一个日期的维表
在然后就可以开干了
先把维表的数据做一个子集临时表做left join用，然后维表在左做left join 取出需要计算的近多少周的日期数据右表为Null则取零，之后就可以对这个时间段内的数据做全局分段，一段七条数据以表示一周，具体的做法是row_number 全局发号外层在计算取余，伪代码
select (rn - 1) / 7 + 1 as wk from (select row_number() over(order by datetime) as rn) tmp
这样wk分段为一组，之后要算什么直接以wk为group key 计算就行了

img


哪位老哥帮补充一下，我是菜鸡，人麻了


SELECT
    CONCAT( '近', TIMESTAMPDIFF( WEEK, t.`create_time`, now( ) ), '周' ) AS `_date`,
    '17500ml' AS '目标饮水量',
    SUM( t.`get_water` ) AS '实际饮水量',
    CONCAT( ROUND( SUM( t.`get_water` ) / 17500 * 100, 2 ), '%' ) AS '完成进度',
CASE
        
        WHEN SUM( t.`get_water` ) >= 17500 THEN
        '达标' ELSE '不达标' 
    END '是否达标' 
FROM
    t_user_consume_log t 
WHERE
    t.`flag` = 1 
GROUP BY
    `_date` 
ORDER BY
    `_date`
您好，我是有问必答小助手，您的问题已经有小伙伴帮您解答，感谢您对有问必答的支持与关注！
PS：问答VIP年卡 【限时加赠：IT技术图书免费领】，了解详情>>> https://vip.csdn.net/askvip?utm_source=1146287632