力扣-高频 SQL 50 题（基础版）-1661. 每台机器的进程平均运行时间

一、完整建表 SQLMySQL 语法CREATE TABLE Activity ( machine_id INT, process_id INT, activity_type ENUM(start,end), timestamp FLOAT, PRIMARY KEY (machine_id, process_id, activity_type) ); 该表展示了一家工厂网站的用户活动。 (machine_id, process_id, activity_type) 是当前表的主键具有唯一值的列的组合。 machine_id 是一台机器的ID号。 process_id 是运行在各机器上的进程ID号。 activity_type 是枚举类型 (start, end)。 timestamp 是浮点类型,代表当前时间(以秒为单位)。 start 代表该进程在这台机器上的开始运行时间戳 , end 代表该进程在这台机器上的终止运行时间戳。 同一台机器同一个进程都有一对开始时间戳和结束时间戳而且开始时间戳永远在结束时间戳前面。INSERT INTO Activity(machine_id, process_id, activity_type, timestamp) VALUES (0,0,start,0.712), (0,0,end,1.520), (0,1,start,3.140), (0,1,end,4.120), (1,0,start,0.550), (1,0,end,1.550), (1,1,start,0.430), (1,1,end,1.420), (2,0,start,4.110), (2,0,end,4.512), (2,1,start,2.500), (2,1,end,5.000);二、需求现在有一个工厂网站有几台机器运行每台机器上运行着相同数量的进程。编写解决方案计算每台机器各自完成一个进程任务的平均耗时。完成一个进程任务的时间指进程的 end 时间戳 减去 start时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。结果表必须包含machine_id机器ID和对应的average time平均耗时别名processing_time且四舍五入保留3位小数。以任意顺序返回表。 以任意顺序返回表格中的记录。三、 思路与解题我一开始的思路就是是先对id对机器进行分组然后把该 id 机器的全部 end 相加并减去其全部 start 再除以count(machine_id)这听起来很简单但是这是SQL不是什么Java / C你只会一些SQL基本查询语句没有像学习Python那样系统学习整个语法格式所以你基本不是很清晰怎么写在SQL里面方法1强行使用SQL语法破局# Write your MySQL query statement below SELECT machine_id, ROUND((SUM(IF(activity_typeend,timestamp,0)) - SUM(IF(activity_typestart,timestamp,0))) / COUNT(DISTINCT process_id),3) processing_time FROM Activity GROUP BY machine_id;方法 2自连接法最直观推荐新手记步骤 1自连接拆分 start、end把同一张表起两个别名s 只存启动记录e 只存结束记录通过where s/t.activity_typestart/end 来实现关联条件同一机器、同一进程# Write your MySQL query statement belowselect s.machine_id,e.timestamp-s.timestamp run_time from Activity s join Activity e on s.machine_ide.machine_id and s.process_ide.process_id where s.activity_typestart and e.activity_typeend执行后会得到每个进程单独的运行时长步骤 2基于上面的结果求每台机器平均值套一层子查询# Write your MySQL query statement below select machine_id,round(avg(run_time),3) processing_time from( -- 子查询先算出每个进程的运行时长 select s.machine_id,e.timestamp-s.timestamp run_time from Activity s join Activity e on s.machine_ide.machine_id and s.process_ide.process_id where s.activity_typestart and e.activity_typeend ) as temp group by machine_id注意每一个派生表子查询括号包裹的临时表必须指定别名 AS temp否则报错 “Every derived table must have its own alias”方法 3CASE WHEN 分组聚合不用自连接单表完成原理同一进程内用CASE分别提取 start、end 时间相减得到单进程时长再外层求平均# Write your MySQL query statement below SELECT machine_id, ROUND(AVG(end_ts - start_ts), 3) AS processing_time FROM ( SELECT machine_id, process_id, -- MAX 函数作用忽略 NULL取出唯一有效数字 MAX(CASE WHEN activity_type start THEN timestamp END) AS start_ts, MAX(CASE WHEN activity_type end THEN timestamp END) AS end_ts -- 逻辑 -- 如果这一行的 activity_type 是 end → 返回这行的 timestamp启动时间 -- 如果不是 end也就是 start→ 没写 ELSE直接返回 NULL FROM Activity GROUP BY machine_id, process_id ) AS temp GROUP BY machine_id;内部子查询分析1.原始两行数据2.执行 CASE每行生成两个临时值第一行 (start)start_ts0.712end_tsNULL第二行 (end)start_tsNULLend_ts1.5203.GROUP BY 把两行合并为一行两列变成数组start_ts 数组[0.712, NULL]end_ts 数组[NULL, 1.520]4.MAX 过滤 NULL提取有效值start_ts MAX ([0.712, NULL]) 0.712end_ts MAX ([NULL, 1.520]) 1.5205.最终输出单行结果四、学习建议刷题 工作两套标准1.刷 LeetCode 这道原题上面写法都能 AC但面试写代码优先推荐双层 CASE 分组面试官会认为你考虑了数据异常思维更完整单层 SUM (IF) 只能当作取巧捷径2.真实开发写 SQL禁止使用单层 SUM 差值求平均的写法必须分层先聚合到单进程维度再聚合机器维度避免隐藏计算 bug五、总结条件聚合CASEMAXGROUP BY通用行转列技巧可把同一实体分散多行的不同指标整合至单行适用于所有分状态统计、差值计算场景逻辑鲁棒性远超单层聚合取巧写法