[PostgreSQL] 10. 지속률과 정착률 한 번에 산출하는 쿼리(종합)

참고

• 지속률과 정착률은 모두 등록일 기준으로 n일 후의 행동을 집계하는 것이다.
• 따라서 등록일로부터 n일 경과하지 않은 상태라면 집계가 불가능하다.(1일 ~ 7일과 같은 단기간 지표를 활용하는 이유)
• 정착률은 7일 동안의 기간을 집계하므로, 실제로 며칠 사용했는지는 알 수 없다.

 

종합 쿼리

정착률을 계산하기 위해 만들었던 repeat_interval 테이블의 형식을 수정하면 지속률까지 계산할 수 있다.

WITH 
repeat_interval(index_name, interval_begin_date, interva_end_date) AS (
	VALUES
	   ('01 day repeat ', 1, 1)
	 , ('02 day repeat ', 2, 2)
	 , ('03 day repeat ', 3, 3)
	 , ('04 day repeat ', 4, 4)
	 , ('05 day repeat ', 5, 5)
	 , ('06 day repeat ', 6, 6)
	 , ('07 day repeat ', 7, 7)
	 , ('07 day retention', 1, 7)
	 , ('14 day retention', 8, 14)
	 , ('21 day retention', 15, 21)
	 , ('28 day retention', 22, 28)
)
SELECT *
FROM repeat_interval
ORDER BY index_name
;

 

🎯 최종 쿼리

• 이하 쿼리는 지속률, 정착률 산출시 동일한 과정을 따른다

WITH 
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
	VALUES
	   ('01 day repeat ', 1, 1)
	 , ('02 day repeat ', 2, 2)
	 , ('03 day repeat ', 3, 3)
	 , ('04 day repeat ', 4, 4)
	 , ('05 day repeat ', 5, 5)
	 , ('06 day repeat ', 6, 6)
	 , ('07 day repeat ', 7, 7)
	 , ('07 day retention', 1, 7)
	 , ('14 day retention', 8, 14)
	 , ('21 day retention', 15, 21)
	 , ('28 day retention', 22, 28)
)
, action_log_with_index_date AS (
	SELECT u.user_id
		, u.register_date
		, CAST(a.stamp AS date) AS action_date
		, MAX(CAST(a.stamp AS date)) OVER () AS lastest_date
		, r.index_name
		-- 지표의 대상기간 시작일과 종료일 계산 
		, CAST(u.register_date::date + '1 day'::INTERVAL * r.interval_begin_date AS date) 
		  AS index_begin_date
		, CAST(u.register_date::date + '1 day'::INTERVAL * r.interval_end_date AS date)
		  AS index_end_date
	FROM mst_users AS u
	LEFT JOIN action_log AS a ON u.user_id = a.user_id
	CROSS JOIN repeat_interval AS r
)
, user_action_flag AS (
	SELECT user_id
		, register_date
		, index_name
		, SIGN(
			SUM(CASE WHEN index_end_date <= lastest_date THEN
				CASE WHEN action_date BETWEEN index_begin_date AND index_end_date THEN 1 ELSE 0 END
			END)
		) AS index_date_action
	FROM action_log_with_index_date
	GROUP BY user_id, register_date, index_name, index_begin_date, index_end_date
)
SELECT index_name
	, AVG(100.0 * index_date_action) AS repeat_rate
FROM user_action_flag
GROUP BY index_name
ORDER BY index_name
;

 

종합 쿼리 결과