题解 | 查询出每个运输方式在不同城市的平均运输时长以及总运输费用

# 按照运输方式分组，看不同运输方式在不同城市的平均运输时长、费用
with
t1 as(
    select
        destination_city,
        transport_name,
        timestampdiff(day,order_date,delivery_date) as ddays,
        total_cost
    from
        order_info
        left join cost_data using(order_id)
        left join transport_detail using(transport_id)
)
,
t2 as(
    select
        destination_city,
        transport_name,
        round(avg(ddays),2) as average_transport_duration,
        round(sum(total_cost),2) as total_transport_cost
    from
        t1
    group by
        destination_city,
        transport_name
    order by
        destination_city,
        transport_name
)

select * from t2