SQL练习题-力扣题库-高频SQL50题-#1661 每台机器的进程平均运行时间

SQL练习题-力扣题库-高频SQL50题-#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

答：

第一次 解题思路（错误） ：

通过自连接 或者开窗函数 降下个进程的开始时间 和 结束时间 关联至一起 再统一进行运算

select 
machine_id ,
AVG(((END1 - start2) + (NEXTEND -NEXTstart))/2) processing_time 
 FROM  (
select 
t1.machine_id,
t1.process_id,
t1.TIMESTAMP AS END1 ,
t2.TIMESTAMP AS start2,
LEAD(t1.TIMESTAMP) OVER (ORDER BY t1.machine_id,t1.process_id) AS NEXTEND, 
LEAD(t2.TIMESTAMP) OVER (ORDER BY t1.machine_id,t1.process_id) AS NEXTstart
from Activity t1 
join Activity t2 
on t1.machine_id = t2.machine_id
and t1.process_id  = t2.process_id 
and t1.activity_type  = 'end'
and t2.activity_type  = 'start'
)
WHERE process_id = 0 
GROUP BY machine_id

结果 ：

在遇到 只有一个进程的 测试用例的时候 导致 计算结果不准确 无法通过测试用例

后续调整思路 考虑到 除数 要根据 机器进程数量的变化而变化 最终决定使用平均数。
用行转列的方式来做这道题 （通过所有测试用例）

SELECT 
machine_id,
round(avg(endTIME-startTIME),3)  as processing_time
from (
    select
machine_id ,
sum(CASE WHEN activity_type = 'start' THEN timestamp ELSE 0 END)  startTIME,
sum(CASE WHEN activity_type = 'end' THEN timestamp ELSE 0 END)  endTIME
FROM Activity 
group by machine_id,process_id
)  a
group by machine_id