mysql - 计算每月不同的活跃用户数

Question

我正在尝试根据注册和取消日期计算每月活跃用户。一些用户的取消日期为 NULL(因为仍然有效)。该查询有很多用户，action_year 和 action_month 均为空。

SELECT
  T.action_year,
  T.action_month,
  COUNT(USerID) active_users
FROM
  (
  SELECT DISTINCT UserID, YEAR(SignupDate) action_year, MONTH(SignupDate) action_month FROM Stat 
  UNION
  SELECT DISTINCT UserID, YEAR(CancelDate) action_year, MONTH(CancelDate) action_date FROM  Stat 
  ) T
GROUP BY
  T.action_year,
  T.action_month
ORDER BY
  T.action_year ASC,
  T.action_month ASC

Answer 1

大概活跃用户是那些月份在注册日期和取消日期之间的用户。这很难定义。它在该月的任何一天都有效吗？最后一天活跃吗？第一天活跃吗？

我会假设第一个。该月中的任何一天都活跃。

这个想法是，给定月份的活跃人数是之前注册且尚未停止的所有用户。根据这一观察结果，计算过程如下:

1. 获取所有年份和月份的列表。以下查询假设每月都会进行注册以简化这部分。
2. 使用相关子查询来获取事件数量。这将与日期的“yyyymm”形式进行比较。
3. 请务必记住 CancelDate 可以为 NULL。

生成的查询是:

select ym.the_year, ym.the_month,
       (select count(*)
        from stat s
        where date_format(SignupDate, '%Y-%m') <= ym.yyyymm and
              (CancelDate is null or date_format(CancelDate, '%Y-%m') >= ym.yyyymm)
       ) as NumActives
from (select distinct year(SignupDate) as the_year, month(SignupDate) as the_month,
             date_format(SignupDate, '%Y-%m') as yyyymm
      from stat
     ) ym