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

WITH RECURSIVE tree AS (
    SELECT merged.task_id AS root_task_id,merged.*,0 as level
    FROM 
        (SELECT operator_name,station_name,fault_dispatch_tasks.*
        FROM fault_dispatch_tasks
        LEFT JOIN charging_stations using(station_id)
        WHERE operator_name = '极曜能源' AND dispatched_at BETWEEN '2025-11-05 00:00:00' and '2025-11-09 23:59:59') merged
    WHERE parent_task_id IS NULL
    UNION ALL 
    SELECT tree.root_task_id,merged2.*,tree.level+1
    FROM 
        (SELECT operator_name,station_name,fault_dispatch_tasks.*
        FROM fault_dispatch_tasks
        LEFT JOIN charging_stations using(station_id)
        WHERE operator_name = '极曜能源' ) merged2
    INNER JOIN tree ON merged2.parent_task_id=tree.task_id

)
SELECT
    root_task_id,
    station_name,
    COUNT(1) AS total_task_count,          -- 总下属工单数
    MAX(level) AS max_depth,               -- 最深层级
    SUM(CASE WHEN 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 -- 总处理小时（保留2位）
FROM tree 
GROUP BY root_task_id,station_name
ORDER BY resolved_leaf_count DESC,max_depth DESC,total_task_count DESC,root_task_id