获取某个日期范围的平均最后 4 个计数 - get the avg last 4 counts for a date range

get the avg last 4 counts for a date range

本文关键字：最后日期范围获取 | 更新日期: 2023-09-27

我有查询计算用户在某个日期范围内每天注册的情况。

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 天的 AVG。例如：今天是星期三，所以我会得到最后 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

如果您想要今天、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)

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