mysql - 每天返回过去 30 天内的活跃用户

Question

我有一个表，activity，如下所示:

date | user_id |

数千名用户以及所有用户的多个日期和事件。我想要提取一个查询，该查询将在结果中的每一天提供过去 30 天内的活跃用户总数。我现在的查询如下所示:

从事件中选择日期、计数(不同的 user_id)，其中 date > date_sub(日期，间隔 30 天)按日期分组

这给出了我当天的唯一用户总数；我无法让它为我提供每个日期的最后 30 个日期。感谢帮助。

Answer 1

为此，您需要一个日期列表并将其加入到事件中。

因此这应该可以做到。用于获取日期列表和 user_id 计数的子查询(或者您可以使用 COUNT(*)，因为我认为 user_id 不能为空):-

SELECT date, COUNT(user_id)
FROM
(
    SELECT DISTINCT date, DATE_ADD(b.date, INTERVAL -30 DAY) AS date_minus_30
    FROM activity
) date_ranges
INNER JOIN activity
ON activity.date BETWEEN date_ranges.date_minus_30 AND date_ranges.date
GROUP BY date

但是，如果某个特定日期的 user_id 可以有多个记录，但您只想计算某个日期的唯一 user_id 的数量，则需要计算 DISTINCT user_id(尽管请注意，如果用户 id 出现在该日期内的 2 个不同日期) 30 天的日期范围，它们只会被计算一次):-

SELECT activity.date, COUNT(DISTINCT user_id)
FROM
(
    SELECT DISTINCT date, DATE_ADD(b.date, INTERVAL -30 DAY) AS date_minus_30
    FROM activity
) date_ranges
INNER JOIN activity
ON activity.date BETWEEN date_ranges.date_minus_30 AND date_ranges.date
GROUP BY date

更简单的做法是根据日期范围将事件表与其自身连接起来，并使用 COUNT(DISTINCT ...) 来消除重复项:-

SELECT a.date, COUNT(DISTINCT a.user_id) 
FROM activity a
INNER JOIN activity b
ON a.date BETWEEN DATE_ADD(b.date, INTERVAL -30 DAY) AND b.date
GROUP by a.date