思路解析：第一性原理解 SQL：连接（JOIN）

目录

题目描述

🎯 应用第一性原理来思考这个 SQL 题目 

✅ 第一步：还原每个事件的本质单位

✅ 第二步：如果一个表只有事件，如何构造事件对？

✅ 第三步：加过滤条件，只保留“同一机器、同一进程”的行组合

✅ 第四步：再过滤，只保留从 start 到 end 的方向 

✅ 第五步：思考目标层级 —— 我们要“按机器”聚合

✅ 最后一步：结构简化为计算表达式

🎓 小结：SQL 中的第一性原理套路

---

题目描述

表: Activity

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+

该表展示了一家工厂网站的用户活动。
(machine_id, process_id, activity_type) 是当前表的主键（具有唯一值的列的组合）。
machine_id 是一台机器的ID号。
process_id 是运行在各机器上的进程ID号。
activity_type 是枚举类型 ('start', 'end')。
timestamp 是浮点类型,代表当前时间(以秒为单位)。
'start' 代表该进程在这台机器上的开始运行时间戳 , 'end' 代表该进程在这台机器上的终止运行时间戳。
同一台机器，同一个进程都有一对开始时间戳和结束时间戳，而且开始时间戳永远在结束时间戳前面。

现在有一个工厂网站由几台机器运行，每台机器上运行着 相同数量的进程 。编写解决方案，计算每台机器各自完成一个进程任务的平均耗时。

完成一个进程任务的时间指进程的'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。

结果表必须包含machine_id（机器ID） 和对应的 average time（平均耗时） 别名 processing_time，且四舍五入保留3位小数。

以 任意顺序 返回表。（来源：Leecode）

🎯 应用第一性原理来思考这个 SQL 题目 

✅ 第一步：还原每个事件的本质单位

我们知道，每个 (machine_id, process_id) 都有两个活动：

• 一次 'start'（开始时间）

• 一次 'end'（结束时间）

而我们要计算的，是这对事件之间的时间差：

end.timestamp - start.timestamp

 这意味着：我们要把这两个记录放在同一行中，也就是事件对还原。

✅ 第二步：如果一个表只有事件，如何构造事件对？

可以把表自己 JOIN 一下，试试看拼出一对记录！

这就是经典的 自连接（Self Join）策略 —— 它不是死记硬背的 SQL 技巧，而是从数据结构出发的推理： 

FROM Activity t1, Activity t2

✅ 第三步：加过滤条件，只保留“同一机器、同一进程”的行组合

where t1.machine_id = t2.machine_id and t1.process_id = t2.process_id

 这一层筛掉了不同机器、不同进程的组合，只保留你真正要比较的配对 —— 同一个进程在同一台机器上（也就是必须是 start + end 的那一对）。

✅ 第四步：再过滤，只保留从 start 到 end 的方向 

and t1.activity_type = 'start' 
and t2.activity_type = 'end'

为什么这一步关键？因为之前虽然我们筛出了同一个进程的两个事件，但没说明谁是开始谁是结束。
你必须明确：

• t1 是 start

• t2 是 end

这样你才能计算：

t2.timestamp - t1.timestamp

否则你可能会把 end - end、start - start、甚至 start - end 都拿来算，结果错乱。

最终筛选：

WHERE t1.machine_id = t2.machine_id AND t1.process_id = t2.process_id AND t1.activity_type = 'start' AND t2.activity_type = 'end'

✅ 第五步：思考目标层级 —— 我们要“按机器”聚合

第一性思维问：“我们最后是要按什么单位输出？”

答案是每台机器的平均耗时 → 所以我们用：

GROUP BY t1.machine_id

并用 AVG(t2.timestamp - t1.timestamp) 得到结果。

✅ 最后一步：结构简化为计算表达式

SELECT t1.machine_id, ROUND(AVG(t2.timestamp - t1.timestamp), 3) AS processing_time
FROM Activity t1, Activity t2
WHERE t1.machine_id = t2.machine_id AND t1.process_id = t2.process_id AND t1.activity_type = 'start' AND t2.activity_type = 'end'
GROUP BY t1.machine_id;

把问题从“做计算”→“拼接事件”→“组合计算”→“聚合分组”完全拆解，这就是第一性分析法！

🎓 小结：SQL 中的第一性原理套路

问题类型	第一性问题	拆解方法
一张表中记录事件多个步骤	我怎么才能还原一对记录？	用 Self Join，按条件连接拼成一行
一张记录表要“还原状态”再分析	我能不能让记录拆成字段？	用 CASE WHEN 聚合 或 Self Join 建模
不知道从哪一步开始写复杂运算	能不能先 JOIN 看看？	用原始 JOIN 探索，再逐步加 WHERE + GROUP

这是第一性学习的关键训练方法。别急着背模板，从“结构 → 关系 → 目的”一步步推出答案，才是真正掌握 SQL 的方式。