首先，需要枚举每种类型的行。使用变量，此代码如下所示：

select sc.*,
       (@rn := if(@t = id_type, @rn + 1,
                  if(@t := id_type, 1, 1)
                 )
       ) as seqnum
from (select timestampdiff(second, s.date_sent, c.date_click) as time_diff,
             s.id_type,
      from emails_sent s inner join
           emails_clicks c
           on s.id = c.id_email
      order by time_diff
     ) sc cross join
     (select @t := -1, @rn := 0) as params;

然后，您需要输入每种类型的总数并计算中位数：

select sc.id_type, avg(time_diff)
from (select sc.*,
             (@rn := if(@t = id_type, @rn + 1,
                        if(@t := id_type, 1, 1)
                       )
             ) as seqnum
      from (select timestampdiff(second, s.date_sent, c.date_click) as time_diff,
                   s.id_type,
            from emails_sent s inner join
                 emails_clicks c
                 on s.id = c.id_email
            order by time_diff
           ) sc cross join
           (select @t := -1, @rn := 0) as params
     ) sc join
     (select id_type, count(*) as cnt
      from emails_sent s inner join
           emails_clicks c
           on s.id = c.id_email
      group by id_type
     ) n
where 2 * seqnum in (n.cnt, n.cnt, n.cnt + 1, n.cnt + 2)
group by sc.id_type;