题解 | 超充站故障派单链路统计

with recursive temp as (
    select
        fdt.task_id as root_task_id,    -- 根派单id
        fdt.station_id as root_station_id,   -- 根充电站id
        fdt.task_id as current_task_id,       -- 当前派单id
        fdt.station_id as current_station_id,  -- 当前充电站id
        station_name,                          -- 充电站名称
        task_status,                          -- 派单状态 ， 后续筛选要用
        0 as depth,                          -- 深度
        handling_minutes,                 -- 每次处理时间
        fault_type                          -- 错误类型  ， 后续计算类型数量
        from fault_dispatch_tasks fdt
        left join charging_stations cs 
        on cs.station_id = fdt.station_id
        where dispatched_at >= '2025-11-05 00:00:00' and dispatched_at < '2025-11-10 00:00:00'
        and parent_task_id is null            -- 筛选条件
        and cs.operator_name = '极曜能源'

union all

    select 
        tp.root_task_id,
        tp.root_station_id,
        fdt.task_id AS current_task_id,
        fdt.station_id AS current_station_id,
        tp.station_name, 
        fdt.task_status,                     
        tp.depth + 1 AS depth,               
        fdt.handling_minutes,
        fdt.fault_type
        from temp tp
        inner join fault_dispatch_tasks fdt
        on fdt.parent_task_id = tp.current_task_id   -- 当前的派单id是父类的派单id，递归的核心
),

temp1 as (
    select
        root_task_id,
        station_name,
        count(current_task_id) as total_task_count,
        max(depth) as max_depth,
        sum(
            case
            when not exists(
                select
                1 
                from fault_dispatch_tasks fdt
                where fdt.parent_task_id = tp.current_task_id           -- 叶子节点的判断
            ) AND tp.task_status = 'resolved' then 1 else 0 end 
        ) as resolved_leaf_count,
        count(distinct fault_type) as distinct_fault_type_count,
        round(sum(handling_minutes)/60.0 , 2 ) as total_handling_hours
        from temp tp
        group by root_task_id, station_name
)
SELECT
    root_task_id,
    station_name,
    total_task_count,
    max_depth,
    resolved_leaf_count,
    distinct_fault_type_count,
    total_handling_hours
FROM temp1
ORDER BY
    resolved_leaf_count DESC,
    max_depth DESC,
    total_task_count DESC,
    root_task_id ASC;