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

-- 逻辑拆解：按照运输方式和城市分组，求值-平均时长、总费用，难点：订单表和费用表的对象记录不唯一，要注意笛卡尔积。
-- 使用cte表达式，分别求每一笔订单费用、派送时长，再连接求每个运输方式每个城市
WITH total_cost AS(
    SELECT 
        order_id,
        SUM(total_cost) total_cost
    FROM cost_data
    GROUP BY order_id
),
transport_duration AS(
    SELECT 
        order_id,
        transport_id,
        destination_city,
        TIMESTAMPDIFF(day,order_date,delivery_date) duration_time
    FROM order_info
),
t3 AS(
    SELECT 
        t2.transport_id,
        t2.destination_city,
        AVG(duration_time) average_transport_duration,
        SUM(t1.total_cost) total_transport_cost
    FROM total_cost t1
    LEFT JOIN transport_duration t2
    ON t1.order_id = t2.order_id
    GROUP BY t2.transport_id,t2.destination_city
)
SELECT 
    a.destination_city,b.transport_name,ROUND(a.average_transport_duration,2) average_transport_duration,ROUND(a.total_transport_cost,2) total_transport_cost
FROM t3 a
LEFT JOIN transport_detail b
ON a.transport_id = b.transport_id
ORDER BY a.destination_city,b.transport_name