题解 | #国庆期间近7日日均取消订单量#
国庆期间近7日日均取消订单量
https://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703
- 计算每天的完成订单数和取消订单数目
round(sum(if(fare is not null, 1, 0))/7, 2) finish_num_7d, round(sum(if(fare is null, 1, 0))/7, 2) cancel_num_7d
- 列出国庆前三天时间
with tb_day as (select distinct date(order_time) sdt from tb_get_car_order where date(order_time) between '2021-10-01' and '2021-10-3' )
- 把国庆前三天每天的前七天数据筛选出来进行连接
left join tb_get_car_order on date(order_time) between date_sub(sdt, interval 6 day) and sdt
- 聚合计算平均值
完整代码
with tb_day as (select distinct date(order_time) sdt from tb_get_car_order where date(order_time) between '2021-10-01' and '2021-10-3' ) select sdt, round(sum(if(fare is not null, 1, 0))/7, 2) finish_num_7d, round(sum(if(fare is null, 1, 0))/7, 2) cancel_num_7d from tb_day left join tb_get_car_order on date(order_time) between date_sub(sdt, interval 6 day) and sdt group by sdt;
查看8道真题和解析