转载于:https://my.oschina.net/u/3400816/blog/1919468
上次遇到一个需求是统计流失与回流用户。流失用户分为7日流失、14日流失、30日流失,n日流失的定义为到统计日期为止,连续n日没有游戏记录的用户即为统计日期当天的n日流失;回流用户也分为7日回流、14日回流、30日回流,n日回流的定义为在统计日期有游戏记录,在统计日期之前的最近一次游戏记录距离统计日期大于等于n日即为统计日期当天的n日回流。当时听产品经理阐述需求的时候就有一点一脸懵逼,不过理解之后就还好了,可以参照下图。
n日流失定义:
n日回流定义:
目前有一个日表记录着每个用户每天的局数,表结构如下:
表中的数据如下:
假设现在我们想要统计在2018.7.10的n日流失用户有哪些,我们可以这样来查询:
SELECT
user_id
FROM
(
SELECT
*
FROM
user_day A
WHERE
NOT EXISTS (
SELECT
1
FROM
user_day
WHERE
user_id = A .user_id
AND log_date > A .log_date
AND log_date <= '2018-07-10'
)
AND A .log_date <= '2018-07-10'
) AS b
WHERE
log_date = '2018-07-03';
其中子查询的作用是筛选出每个用户在小于等于2018-07-10日的最近的一条游戏记录,然后在其中筛选出在2018-07-03日的游戏记录对应的用户ID,这些用户ID对应的用户即为2018-07-10日的7日流失用户。
假设我们想要统计在2018-07-10的7日回流用户的话,可以这样来查询:
SELECT
user_id
FROM
(
SELECT
user_id,
log_date
FROM
user_day u
WHERE
NOT EXISTS (
SELECT
*
FROM
user_day
WHERE
user_id = u.user_id
AND log_date > u.log_date
AND log_date < '2018-07-10'
)
AND u.log_date < '2018-07-10'
) AS A
WHERE
log_date <= '2018-07-03'
AND user_id IN (
SELECT
user_id
FROM
user_day
WHERE
log_date = '2018-07-10'
);
其中子查询的作用是筛选出每个用户在小于2018-07-10日的最近的一条游戏记录,然后在其中筛选出小于2018-07-03日的游戏记录对应的user_id,然后在查询这些user_id中在2018-07-10日有游戏记录的user_id,即为2018-07-10日的7日回流用户ID。