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

标签：machine end timestamp sum 好题 力扣 start 心路历程 id

又来到了【好题分享】专栏~

这次博主要分享的，是既力扣1179之后的姊妹题。

只能用几个字来描述：旧瓶换新酒，如出一辙！

【题目介绍】

该题为力扣1661，名为每台机器的进程平均运行时间。

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

【题型分类】

属于 case when 专题。

官网标为简单题，考虑到计算复杂程度，个人认为难度应为中等题~

【思路分享】

方法一：case when

其实本质和它的姊妹题是一样的，都会出现单纯 case when 之后同类数据分散在不同记录的尴尬问题。

不过没关系，group by 及 sum() 帮我们解决它~

# 新建列：
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

方法二：lag

事实上方法二才是博主我的第一反应，毕竟 lag 这些东西我玩得66的（bushi）~

这次不偷懒，上完整代码~

# 新增列：
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/

标签：machine,end,timestamp,sum,好题,力扣,start,心路历程,id
From： https://www.cnblogs.com/peitongshi/p/16988529.html