mysql - 如何根据已经活跃的用户(而不是根据用户注册日期)计算月度用户留存率？

Question

我有一个跟踪用户事件的表(即用户在什么时间开始 session ？)。此表包含从 2018 年 12 月至今的数据。我需要根据用户事件计算每月留存率(不是基于注册日期)( i-e 在 2018 年 12 月，有 500 个用户活跃。然后，他们中有多少人在一月、二月、三月....直到现在？应该为 2019 年 1 月、2019 年 2 月...到现在的用户执行相同的事件)。

我尝试了硬编码方式，即让 2018 年 12 月的用户进入表，然后在其他表中获得 2019 年 1 月的用户 并加入两个表基于 user_ids，但为此我必须写很多连接。需要一种动态的方法来检查每月的用户保留率以及 2018 年 12 月之后的所有月份(因为数据从这个月开始可用)。

select A.year_month_id,count(distinct A.user_id) as November_Users,count(distinct B.user_id) as December_Retained_Users 
FROM (
        select date_trunc('month', ua.created_at) as monthly,
        ua.user AS user_id
        FROM     user_activity ua
        WHERE    ua.event_type='StartSession'
        and     cast(ua.created_at as date) between cast('20181201' as date) and cast('20181231' as date)
        GROUP BY 1,2
    ) AS A
left Join 
    (
        select date_trunc('month', ua.created_at) as monthly,
        ua.user AS user_id
        FROM     user_activity ua
        WHERE    ua.event_type='StartSession'
        and     cast(ua.created_at as date) between cast('20190101' as date) and cast('20190131' as date)
        GROUP BY 1,2
    ) AS B 
on A.user_id=B.user_id
group by 1

user_activity 表#

id | user | event_type   | created_at
1  | A1   | StartSession | April 29, 2019, 3:59 AM
2  | A2   | StartSession | December 29, 2018, 1:07 AM
3  | A3   | StartSession | December 9, 2018, 4:59 PM
49 | A31  | StartSession | May 25, 2019, 11:59 AM
100| A46  | StartSession | April 29, 2019, 3:56 AM

预期输出#

Month |Monthly_Active_Users| Jan_Retained|Feb_Retained|Mar_Retained|.......
Dec   | 500                |  300        |  200       | 330
Jan   | 700                |  N/A        |  450       | 410
Feb   | 1000               |  N/A        |  N/A       | 820
Mar   | 920                |  N/A        |  N/A       | N/A
.
.
.
.
Aug   | 100                | N/A         |    N/A     | N/A

Answer 1

我认为这可以完成工作:

with t as (
    select distinct user_, to_char(created_at, 'yymm') dt
      from user_activity where event_type = 'StartSession'),
  u as (
    select a.user_, a.dt mth, b.dt dt, count(distinct a.user_) over (partition by a.dt) cnt
      from t a join t b on (a.user_ = b.user_ and b.dt >= a.dt))
select * from u pivot (count(user_) for dt in (1901, 1902, 1903, 1904)) order by mth

^{dbfiddle demo}

我假设 created_at 列是 date 数据类型。如果没有，请使用 cast，无论哪种对您有用。我们需要在查询中将此值转换为 yymm。另外 user 是保留字，我在查询中使用了 user_。

用所有月份 (1901...1908) 填充 pivot 的 in 子句中的列表，并在未来添加下个月。 Pivot 不允许在此处使用动态语法，您必须指定它们。

工作原理:

首先 - 来自您的表的不同值(用户、月份)。然后是最重要的部分 - 自连接，它为每个用户创建开始 月份和 future 月份的元组。我还在这里添加了分析计数，这是您报告中第二列所必需的。 Final pivot 只是聚合这些准备好的数据。