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

with recursive cte as (
    select
        f.task_id as root_task_id,
        c.station_name	 as station_name,
        f.task_id,
        0 as max_depth,
        fault_type as type,
        handling_minutes
    from
        charging_stations c
    join
        fault_dispatch_tasks f on c.station_id = f.station_id
    where
        f.parent_task_id is NULL and c.operator_name = '极曜能源' and f.dispatched_at >= '2025-11-05' and f.dispatched_at < '2025-11-10'
    
    union all

    select
        c.root_task_id,
        c.station_name,
        f.task_id,
        max_depth +1 as max_depth,
        f.fault_type as type,
        f.handling_minutes
    from
        cte c
    join
        fault_dispatch_tasks f on f.parent_task_id = c.task_id
)
select
    c.root_task_id,
    c.station_name,
    count(c.task_id) as total_task_count,
    max(c.max_depth) as max_depth,
    sum(case when c.task_id not in (select parent_task_id from fault_dispatch_tasks where parent_task_id is not null) and f.task_status = 'resolved' then 1 else 0 end) resolved_leaf_count,
    count(distinct type) as distinct_fault_type_count,
    round(sum(c.handling_minutes)/60,2) as total_handling_hours
from
    cte c
join
    fault_dispatch_tasks f on f.task_id = c.task_id
group by
    root_task_id,station_name
order by
    resolved_leaf_count desc , max_depth desc,total_task_count desc ,root_task_id