mysql - 如何计算平均值?
问题描述
我有两张桌子。进入第一个表(活动)有:user_id、sessions和login_time。在第二个(付款)中只有一列 - user_id。
我需要计算“付款”用户在 01/01/2018-01/05/2018 期间进行了多少次会话。
activity
user_id sessions login_time
1 6 01.01.2018
2 2 01.01.2018
1 1 02.01.2018
4 3 02.01.2018
1 2 03.05.2018
3 04.02.2018
桌子
payments
user_id
1
3
4
我的代码(计算错误):
select login_time, avg(activity.sessions) AS
average_sessions
from activity inner join
payments
on payments.user_id = activity.user_id
where activity.login_time between '2018-01-01' and '2018-05-01'
group by activity.login_time;
感谢帮助!
输出:
output
login_time average_sessions
01.01.2018 6
02.01.2018 2
03.01.2018 0
04.01.2018 0
...
解决方案
查看您的样本
似乎您需要会话总和除以总天数
select user_id, sum(sessions)/t.num_days
from activity
cross join (
select count(distinct login_time) num_days
from activity
) t
group by user_id
如果您只需要付款中的 user_id
select user_id, sum(sessions)/t.num_days
from activity
cross join (
select count(distinct login_time) num_days
from activity
) t
inner join payments p on p.user_id = activity.user_id
group by user_id
并且看着你形成的结果似乎你需要
select t1. login_time, ifnull(t2.avg_sess) average_sessions
from (
select distinct login_time
from activity
) t1
left join (
select a.login_time, avg(a.session) avg_sess
from activity a
inner join payments p on a.user_id = p.user_id
group by a.login_time
) t2 on t1.login_time = t2.login_time