mysql - 获取按日期分组的最近 n 天的活跃用户数量

Question

假设我有一个 Hive 表 logins包含以下列:

user_id | login_timestamp

我现在有兴趣获取一些事件 KPI。例如，每日活跃用户:

SELECT 
to_date(login_timestamp) as date,
COUNT(DISTINCT user_id) daily_active_user
FROM
logins
GROUP BY to_date(login_timestamp)
ORDER BY date asc

将其从每日活跃更改为每周/每月活跃并不是什么大问题，因为我只需交换 to_date()函数获取月份，然后按该值进行分组。

我现在想要获取的是按日期分组的过去 n 天(例如 3 天)活跃用户的不同数量。此外，我正在寻找一种适用于可变时间窗口而不仅仅是一天的解决方案(仅在第 x 天获取过去 3 天的活跃用户数量会很容易)。

结果应该是这样的:

date, 3d_active_user
2017-12-01, 111
2017-12-02, 234
2017-12-03, 254
2017-12-04, 100
2017-12-05, 103
2017-12-06, 103
2017-12-07, 230

在第一个选择中使用子查询(例如 select x, (select max(x) from x) as y from z )为移动时间窗口构建解决方法是不可能的，因为我正在使用的 Hive 版本不支持它。

我试试运气，比如 COUNT(DISTINCT IF(DATEDIFF(today,login_date)<=3,user_id,null))但到目前为止我尝试的一切都不起作用。

您知道如何解决这个问题吗？任何帮助表示赞赏!

Answer 1

您可以使用“BETWEEN”功能。如果您想查找活跃用户，请从特定日期至今登录。

选择 to_date(login_timestamp) 作为日期，COUNT(DISTINCT user_id) daily_active_user
来自登录
startDate_timeStamp 和 now() 之间的登录时间戳
按 to_date 分组(登录时间戳)
按日期升序排序

如果您想要活跃用户(即特定日期范围内的登录用户)，则:注意:-

选择 to_date(login_timestamp) 作为日期，COUNT(DISTINCT user_id) daily_active_user
来自登录
登录时间戳位于 to_date(startDate_timeStamp) 和 to_date(endDate_timeStamp) 之间
按 to_date 分组(登录时间戳)
按日期升序排序