LeetCode 1661. 每台机器的进程平均运行时间[SQL 50]--连接

1661. 每台机器的进程平均运行时间

表: 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位小数。

以 任意顺序 返回表。

具体参考例子如下。

示例 1:

输入：
Activity table:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 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.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |
+------------+------------+---------------+-----------+
输出：
+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
+------------+-----------------+
解释：
一共有3台机器,每台机器运行着两个进程.
机器 0 的平均耗时: ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
机器 1 的平均耗时: ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
机器 2 的平均耗时: ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456

解法一：自连接（INNER JOIN）

SELECT a1.machine_id AS 'machine_id',
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS 'processing_time'
FROM Activity AS a1
    INNER JOIN Activity AS a2
    ON a1.machine_id = a2.machine_id
        AND a1.process_id = a2.process_id  
        AND a1.activity_type = 'start' AND a2.activity_type = 'end'
GROUP BY a1.machine_id;

思路详解：

1. 自连接：将Activity表与自身连接，相当于创建两个副本

2. 连接条件：

   • 相同机器（machine_id）
   • 相同进程（process_id）
   • a1是开始记录（activity_type = 'start'）
   • a2是结束记录（activity_type = 'end'）
3. 计算时间差：a2.timestamp - a1.timestamp 得到每个进程的运行时间
4. 求平均值：AVG() 计算每台机器上所有进程的平均运行时间
5. 四舍五入：ROUND(..., 3) 保留3位小数

优点：

• 逻辑清晰，直观易懂
• 直接使用开始和结束时间戳相减

解法二：巧用正负值（SUM + IF）

SELECT machine_id AS 'machine_id',
    ROUND(
        SUM(IF(activity_type = 'start', -timestamp, timestamp))
        / COUNT(*) 
        * 2
        ,3) AS 'processing_time'
FROM Activity
GROUP BY machine_id;

思路详解：

1. 正负转换：使用IF函数，将开始时间转为负数，结束时间保持正数

   • IF(activity_type = 'start', -timestamp, timestamp)

2. 求和计算：SUM() 将所有时间相加

   • 效果：(end1 + end2 + ...) - (start1 + start2 + ...)

3. 除以进程数：需要得到每个进程的时间差之和

   • COUNT(*) 是总记录数（开始+结束），除以2才是进程数
   • 所以需要 * 2 来调整

示例计算（机器0）：

记录：start(0.712), end(1.520), start(3.140), end(4.120)
正负后：-0.712, 1.520, -3.140, 4.120
SUM = (-0.712 + 1.520) + (-3.140 + 4.120) = 0.808 + 0.98 = 1.788
COUNT(*) = 4
(1.788 / 4) * 2 = 0.447 * 2 = 0.894

解法三：使用COUNT(DISTINCT)

SELECT machine_id AS 'machine_id',
    ROUND(
        SUM(IF(activity_type = 'start', -timestamp, timestamp))
        / COUNT(DISTINCT process_id) 
        ,3) AS 'processing_time'
FROM Activity
GROUP BY machine_id;

思路详解：

1. 同样使用正负转换：IF(activity_type = 'start', -timestamp, timestamp)
2. 求和：SUM() 得到所有进程的总运行时间

3. 直接除以进程数：COUNT(DISTINCT process_id) 统计每台机器上的进程数量

   • 不需要乘以2，因为进程数就是真正的进程数量

解法四：使用CASE WHEN

SELECT machine_id AS 'machine_id',
    ROUND(
        SUM(
            CASE
                WHEN activity_type = 'start' THEN -timestamp
                ELSE timestamp
            END
        )
        / COUNT(DISTINCT process_id) 
    , 3) AS 'processing_time'
FROM Activity
GROUP BY machine_id;

思路详解：

• 与解法三完全相同，只是将IF函数替换为CASE WHEN
• CASE WHEN是标准SQL语法，可移植性更好
• 逻辑：开始时间取负，结束时间取正，求和后除以进程数

推荐使用解法三或解法四，因为它们只需要扫描一次表，可以避免自连接的开销，性能较好