【阿里巴巴】使用递归 CTE 统计充电站故障处置链复杂度
JOIN 连接聚合函数CASE WHEN路径分析面试真题
题目描述
来源：阿里 SQL 面试真题
某新能源运营商管理大量超充站。充电站发生故障告警后，调度系统会先创建一条首发派单；如果现场工程师发现问题需要继续拆分、升级或转交，就会基于上一条派单再生成下游派单，形成一条“故障处置链”。
运维团队希望统计指定时间范围内，某运营商发起的每一条故障处置链的复杂度和处理工时。
本题使用 MySQL 8.0 语法，核心考点是 WITH RECURSIVE ... AS (...)。
数据表
charging_stations 充电站主数据表
| 字段 | 类型 | 说明 |
|---|---|---|
| station_id | BIGINT | 充电站 ID，主键 |
| operator_name | VARCHAR(100) | 运营商名称 |
| station_name | VARCHAR(200) | 充电站名称 |
| city | VARCHAR(50) | 所在城市 |
| launched_at | DATETIME | 充电站上线时间 |
fault_dispatch_tasks 故障派单表
| 字段 | 类型 | 说明 |
|---|---|---|
| task_id | BIGINT | 派单 ID，主键 |
| station_id | BIGINT | 所属充电站 ID，关联 charging_stations.station_id |
| engineer_name | VARCHAR(100) | 处理工程师姓名 |
| parent_task_id | BIGINT | 上游派单 ID；NULL 表示首发派单 |
| fault_type | VARCHAR(30) | 故障类型，例如 power、network、module、cooling |
| task_status | VARCHAR(20) | 派单状态，例如 resolved、escalated、pending |
| dispatched_at | DATETIME | 派单创建时间 |
| handling_minutes | INT | 该派单实际处理耗时，单位分钟 |
业务规则
- 一条故障处置链从 parent_task_id IS NULL 的首发派单开始。
- 下游派单满足 parent_task_id = 当前 task_id。
- 沿派单关系递归向下展开，直到没有新的下游派单。
- 只统计 operator_name = '极曜能源' 的充电站。
- 首发派单必须发生在 2025-11-05 00:00:00 到 2025-11-09 23:59:59 之间。
- 只要某条派单属于这些首发派单的递归下游，即使它自己的 dispatched_at 超出上述时间范围，也要计入对应故障处置链。
- 处置链中没有子节点的派单是叶子节点；没有下游的首发派单本身也算 1 个叶子节点。
- resolved_leaf_count 只统计叶子节点中 task_status = 'resolved' 的数量。
- distinct_fault_type_count 按整条处置链内不同 fault_type 去重统计。
问题
请查询指定运营商和首发时间范围内的每一条首发故障处置链，返回：
| 字段 | 说明 |
|---|---|
| root_task_id | 首发派单 ID |
| station_name | 充电站名称 |
| total_task_count | 故障处置链的派单总数，包含首发派单 |
| max_depth | 最大层级深度，首发派单深度为 0 |
| resolved_leaf_count | 状态为 resolved 的叶子节点数量 |
| distinct_fault_type_count | 整条处置链中不同故障类型数量 |
| total_handling_hours | 所有节点 handling_minutes 之和换算成小时，四舍五入保留 2 位小数 |
结果按以下规则排序：
- resolved_leaf_count 降序。
- max_depth 降序。
- total_task_count 降序。
- root_task_id 升序。
数据样例
| station_idPKBIGINT | operator_nameVARCHAR(100) | station_nameVARCHAR(200) | cityVARCHAR(50) | launched_atDATETIME |
|---|---|---|---|---|
| 1 | 极曜能源 | 曙光超充站 | 杭州 | 2025-01-10 08:00:00 |
| 2 | 极曜能源 | 星环超充站 | 上海 | 2025-02-15 09:00:00 |
| 3 | 极曜能源 | 北辰超充站 | 北京 | 2025-03-20 10:00:00 |
| 4 | 远峰能源 | 云谷超充站 | 广州 | 2025-04-01 08:00:00 |
输入数据显示 4 / 4 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里