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

WITH RECURSIVE t AS (
    SELECT task_id AS root_task_id,
           f.station_id AS root_station_id,
           task_id AS current_task_id,
           handling_minutes,
           0 AS max_depth,
           fault_type,
           task_status
    FROM charging_stations c 
    LEFT JOIN fault_dispatch_tasks f ON c.station_id=f.station_id
    WHERE parent_task_id IS NULL 
          AND operator_name = '极曜能源' 
          AND dispatched_at BETWEEN '2025-11-05 00:00:00' AND '2025-11-09 23:59:59'
    
    UNION ALL
    
    SELECT t.root_task_id AS root_task_id,
           t.root_station_id AS root_station_id,
           f1.task_id AS current_task_id,
           f1.handling_minutes,
           max_depth+1 AS max_depth,
           f1.fault_type,
           f1.task_status
    FROM charging_stations c1 
    LEFT JOIN fault_dispatch_tasks f1 ON c1.station_id=f1.station_id
    JOIN t  ON f1.parent_task_id=t.current_task_id
    WHERE c1.operator_name = '极曜能源'
),
t2 AS(
   SELECT root_task_id,root_station_id,
          COUNT(current_task_id) AS total_task_count,
          MAX(max_depth) AS max_depth,
          SUM(CASE WHEN NOT EXISTS (
             SELECT 1
             FROM fault_dispatch_tasks f2
             WHERE f2.parent_task_id=current_task_id
          ) AND 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,2) AS total_handling_hours
   FROM t
   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 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