MySQL训练：同一表内，按条件进行计算，Leetcode1661题leetcode 1661题 https://lee

leetcode 1661题 leetcode-cn.com/problems/av…

解题思路

1、自连接 + group by + 计算公式 + sum等函数 + round

#1、根据条件进行连接 join on
select
    *
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'
#2、按照统计要求进行分组 + (后表关键字段 ± 前表关键字段) + 利用聚合函数 + round设置条件
select
    a1.machine_id,
    round(avg(a2.timestamp-a1.timestamp),3) as 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

2、if/case when + group by + sum等函数 + round 这个做法的关键：利用if的非此即彼与正负对应

#1、按照统计要求进行分组
select
    ####
from Activity
group by machine_id
#2、if函数 将区分减数与被减数， 减数为负，被减数为正
if(activity_type='start',-timestamp,timestamp)
#利用sum函数求和 + count(distinct()) 求平均
sum(if(activity_type='start',-timestamp,timestamp))/count(distinct(process_id))
#round限定
round(sum(if(activity_type='start',-timestamp,timestamp))/count(distinct(process_id)),3)

#完整版
select
    machine_id,
    round(sum(if(activity_type='start',-timestamp,timestamp))/count(distinct(process_id)),3) as processing_time
from Activity
group by machine_id
order by 1

MySQL训练：同一表内，按条件进行计算，Leetcode1661题

解题思路

相关题目：