mysql 中间值_如何使用group by(MySQL)查找中间值

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

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;