基于PostgreSQL数据库的流失、回流统计

2019独角兽企业重金招聘Python工程师标准>>>

上次遇到一个需求是统计流失与回流用户。流失用户分为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。

转载于:https://my.oschina.net/u/3400816/blog/1919468