第一次速度击败了100%的人,只用总和处于人数就可以,没用到题解的在对id进行商品的聚合,所以会快很多 用了 () in (select)来匹配每个用户的第一次订单,
select round(amount/num,1) ,
round((cost-amount)/num,1)
from
(select sum(price*cnt) as cost,
count(distinct uid) as num,
sum(distinct total_amount) as amount
from tb_order_detail de
left join tb_product_info info using(product_id)
left join tb_order_overall ov using(order_id)
where (uid ,event_time) in (select uid,min(event_time)
from tb_order_overall group by uid)
and left(event_time,7)="2021-10" and status=1) tmp