链接:1661. 每台机器的进程平均运行时间 - 力扣（LeetCode）

前提条件:

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

以 任意顺序 返回表。

具体参考例子如下。

CREATE TABLE Activity (
    machine_id INT,
    process_id INT,
    activity_type ENUM('start', 'end'),
    timestamp FLOAT
);
INSERT INTO Activity
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.100),
    (2, 0, 'end', 4.512),
    (2, 1, 'start', 2.500),
    (2, 1, 'end', 5.000);

优化:

1. 使用窗口函数替代连接

SELECT
        a1.machine_id,
        ROUND(
      (select AVG(a2.timestamp) FROM Activity a2 WHERE a2.activity_type = 'end' AND a2.machine_id = a1.machine_id) -
      (select AVG(a2.timestamp) FROM Activity a2 WHERE a2.activity_type = 'start' AND a2.machine_id = a1.machine_id)
        ,3)  processing_time
FROM
        activity a1
GROUP BY
        a1.machine_id;

实现:

SELECT
        a1.machine_id machine_id, ROUND(AVG(a2.timestamp - a1.timestamp),3) processing_time
FROM
        activity a1 JOIN activity 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;