10 -每台机器的进程平均运行时间（高频 SQL 50 题基础版)

10 - 每台机器的进程平均运行时间

-- sum(if(activity_type = 'end',timestamp ,-timestamp ))
-- 如果activity_type为“end”,值为timestamp，为“start” 为-timestamp，所有数相加=end-start
-- count(distinct process_id),获取同一机器有几个进行id
-- round(,3),保留三位小数


select 
    machine_id , round(sum(if(activity_type = 'end',timestamp ,-timestamp ))/count(distinct process_id),3) as processing_time 
from 
    Activity 
group by 
    machine_id;


-- AVG(IF(activity_type = 'start', -timestamp, timestamp))
-- 如果activity_type为“end”,值为timestamp，为“start” 为-timestamp，所有数相加=end-start
-- 将所有数求平均，avg(1,2,3,4)/4，多除了2倍
-- round(,3),保留三位小数

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