php - 获取日期范围内最后 4 次计数的平均值

Question

我有查询统计某个日期范围内每一天注册的用户数。

select count(*) as c, date(from_unixtime(user.firstaccess)) as date
    from user
    where firstaccess between ? and ?
    group by date(from_unixtime(user.firstaccess))

这很好用。现在，我正在生成报告，我想知道一周最后 4 天的平均值。例如:今天是星期三，所以我会得到最后 4 个星期三并得到 AVG。同样，我有这个查询，正在运行，但我只能考虑每天单独查询的逻辑。而不是日期范围。这就是我拥有的

select
(
(select count(*) from user where firstaccess between 1456617600-(3600*24*7) and 1456703999-(3600*24*7)) + 
(select count(*) from user where firstaccess between 1456617600-(3600*24*14) and 1456703999-(3600*24*14)) +
(select count(*) from user where firstaccess between 1456617600-(3600*24*14) and 1456703999-(3600*24*14)) +
(select count(*) from user where firstaccess between 1456617600-(3600*24*21) and 1456703999-(3600*24*21))
)
/4
as c
from user
limit 0,1

我正在使用 Mysql 和 PHP

Answer 1

如果你想要今天、7天、14天前和21天前首次注册的用户的平均数，你可以使用:

select count(*)/4 as avg_new_users
from
user
where 
date(from_unixtime(user.firstaccess))=CURDATE() or date(from_unixtime(user.firstaccess))=DATE_SUB(CURDATE(),INTERVAL 7 DAY) or date(from_unixtime(user.firstaccess))=DATE_SUB(CURDATE(),INTERVAL 14 DAY) or date(from_unixtime(user.firstaccess))=DATE_SUB(CURDATE(),INTERVAL 21 DAY)