题解 | 下单复盘

select
    customer_id,
    customer_name,
    count(distinct order_id) feb_2024_order_count,
    round(coalesce(sum(qty*price),0),2) feb_2024_total_amount,
    round(coalesce(sum(qty*price)/nullif(count(distinct order_id),0),0),2) feb_2024_avg_order_amount,
    min(order_date) feb_2024_first_order_date,
    max(order_date) feb_2024_last_order_date
from
    customers s1
left join
    (select * from orders where date_format(order_date,'%Y%m') = 202402) s2
using(customer_id)
left join
    order_items s3
using(order_id)

group by 1,2
order by 4 desc,1 asc

业务里一般用coalesce代替ifnull