with t1 as(
    select
        s1.order_id,
        total_amount temp_amount,
        sum(price*cnt) - total_amount  temp_cost
    from
        tb_order_detail s1
    join
        (
            select
                *,
                rank() over(partition by uid order by event_time) rk
            from
                tb_order_overall
            where date_format(event_time,'%Y%m') = 202110
            and     status = 1 
        ) s2
    on s1.order_id = s2.order_id 
    and rk = 1
    group by 1,2
)
select 
    round(avg(temp_amount),1) avg_amount,
    round(avg(temp_cost),1) avg_cost
from
    t1

这代码几乎完美