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

with recursive temp as
(
    select 
    task_id as root_task_id,
    f.station_id as root_station_id,
    task_id as current_task_id,
    0 as depth,
    handling_minutes,
    fault_type,
    task_status
    from 
    fault_dispatch_tasks f join charging_stations c 
    on c.station_id = f.station_id
    where parent_task_id is NULL
    and dispatched_at >= '2025-11-05 00:00:00'
    and dispatched_at <= '2025-11-09 23:59:59'
    and operator_name = '极曜能源'
    union all
    select tp.root_task_id,
    tp.root_station_id,
    f1.task_id as current_task_id,
    depth + 1 as depth,
    f1.handling_minutes,
    f1.fault_type,
    f1.task_status
    from fault_dispatch_tasks f1 join charging_stations c1 
    on f1.station_id = c1.station_id
    join temp tp on f1.parent_task_id = tp.current_task_id where c1.operator_name = '极曜能源'
),
temp2 as
(
    select 
    root_task_id,
    root_station_id,
    count(current_task_id) as total_task_count,
    max(depth) as max_depth,
    count(distinct fault_type) as distinct_fault_type_count,
    round(sum(handling_minutes)/60,2) as total_handling_hours,
    sum(
        case when not exists(
            select 1 from fault_dispatch_tasks f2 where f2.parent_task_id = tp.current_task_id
        ) and task_status = 'resolved' then 1 else 0 end
    ) as resolved_leaf_count
    from temp tp
    group by root_task_id,root_station_id
)
select root_task_id,
station_name,
total_task_count,
max_depth,
resolved_leaf_count,
distinct_fault_type_count,
total_handling_hours 
from temp2 t2 join charging_stations c on t2.root_station_id = c.station_id
order by
resolved_leaf_count desc,
max_depth desc,
total_task_count desc,
root_task_id asc