-- 按快递种类聚合前各快递的运输时长(关联查询，时间函数)

with

    t1 as (

        select

            exp_type,

            timestampdiff (second, out_time, in_time) / 3600 ys_time

        from

            express_tb et

            left join exp_action_tb ea on et.exp_number = ea.exp_number

    )

-- 计算每种类型快递的平均运输时长（分组查询、聚合函数、排序）

select

    exp_type,

    round(avg(ys_time), 1) time

from

    t1

group by

    exp_type

order by

    time