如何使用group by(MySQL)查找中间值 [英] How to find median value with group by (MySQL)
本文介绍了如何使用group by(MySQL)查找中间值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
需要找到每种类型的电子邮件的发送日期和单击日期之间的时间差的中间值(以秒为单位).我找到了仅适用于所有数据的解决方案:
Need to find median value of time difference between sent date and click date (in seconds) for each type of emails. I found solution just for all data:
SET @rowindex := -1;
SELECT g.type, g.time_diff
FROM
(SELECT @rowindex:=@rowindex + 1 AS rowindex,
TIMESTAMPDIFF(SECOND, emails_sent.date_sent, emails_clicks.date_click) AS time_diff,
emails_sent.id_type AS type
FROM emails_sent inner join emails_clicks on emails_sent.id = emails_clicks.id_email
ORDER BY time_diff) AS g
WHERE g.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2));
是否可以通过id_type语句添加分组? 谢谢!
Is it possible to add group by id_type statement? Thanks!
推荐答案
首先,您需要枚举每种类型的行.使用变量,此代码如下所示:
First, you need to enumerate the rows for each type. Using variables, this code looks like:
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;
然后,您需要输入每种类型的总数并计算中位数:
Then, you need to bring in the total number for each type and do the calculation for the median:
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;
这篇关于如何使用group by(MySQL)查找中间值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文