• 欢迎光临~

# 好题分享、心路历程（力扣1661）

【题目介绍】

emmm 虽然名字有点土，但丝毫不影响我们肝它~

【题型分类】

【思路分享】

```# 新建列：
with tmp as
(select machine_id,process_id,
case when activity_type='start' then timestamp end as start_timestamp,
case when activity_type='end' then timestamp end as end_timestamp
from Activity)

# 聚合：
,tmp1 as
(select machine_id,process_id,
sum(start_timestamp) as sum_start_timestamp,
sum(end_timestamp) as sum_end_timestamp,
(sum(end_timestamp)-sum(start_timestamp)) as processing_timestamp
from tmp
group by machine_id,process_id)

# 取平均：
select machine_id,
round(avg(processing_timestamp),3) as processing_time
from tmp1
group by machine_id```

1. 分组排序后，拉取上一个时间戳，即开始时间到结束状态记录行；

2. 对同行记录做加减，获得每个机器、每个进程的处理时间；

3. 对每个机器，平均处理时间即可~

```# 新增列：
with tmp as
(select *,
lag(timestamp,1,0) over
(partition by machine_id,process_id
order by activity_type) as start_timestamp
from Activity)

# 过滤：
,tmp1 as
(select machine_id,process_id,
timestamp as end_timestamp,
start_timestamp,
timestamp-start_timestamp as processing_timestamp
from tmp
where activity_type='end')

# 取平均：
select machine_id,
round(avg(processing_timestamp),3) as processing_time
from tmp1
group by machine_id```

-END

https://leetcode.cn/problems/average-time-of-process-per-machine/