[PostgreSQL] 4. 사용자의 방문 빈도 집계하기

사용자가 일주일 또는 한 달 동안 서비스를 얼마나 쓰는지 알면 분석에 큰 도움이 된다.

이번엔 '서비스를 한 주 동안 며칠 사용하는 사용자가 몇 명인지' 집계하는 방법을 알아본다.

 

 

👆 위 데이터를 바탕으로 한 주 동안 매일 몇 명의 사용자가 방문했는지 파악하고 구성비누계도 계산한다.

 

 

한 주 동안 며칠 방문했는지 사용자 수를 집계

• 먼저 timestamp에서 날짜만 추출한다.
• 유저 별로, 일주일 동안 며칠 방문했는지 count 한다. (Where 조건과, count(distinct) 사용)
• 카운트 된 일 수(방문 일 수) 기준으로 유저 아이디 count를 한다.
  👉 결과 예로, 7일 중 2일 방문한 사용자가 2명이다.

WITH 
action_log_with_dt AS (
	SELECT *
		, substring(stamp, 1, 10) AS dt
	FROM action_log 
)
, action_day_count_per_user AS ( 
	SELECT user_id	
		, count(DISTINCT dt) AS action_day_count
	FROM action_log_with_dt
	WHERE dt BETWEEN '2016-11-01' AND '2016-11-07'
	GROUP BY user_id
)
SELECT action_day_count
	, count(DISTINCT user_id) AS user_count
FROM action_day_count_per_user
GROUP BY 1
ORDER BY 1
;

 

 

---

구성비와 구성비누계 계산

WITH 
action_log_with_dt AS (
	SELECT *
		, substring(stamp, 1, 10) AS dt
	FROM action_log 
)
, action_day_count_per_user AS ( 
	SELECT user_id	
		, count(DISTINCT dt) AS action_day_count
	FROM action_log_with_dt
	WHERE dt BETWEEN '2016-11-01' AND '2016-11-07'
	GROUP BY user_id
)
SELECT action_day_count
	, count(DISTINCT user_id) AS user_count
	-- 구성비 
	, 100.0 * count(DISTINCT user_id) / sum(count(DISTINCT user_id)) OVER () AS composition_ratio
	-- 구성비 누계 
	, 100.0 * sum(count(DISTINCT user_id)) 
				OVER (ORDER BY action_day_count ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
				/ sum(count(DISTINCT user_id)) OVER () AS cumulative_ratio
FROM action_day_count_per_user
GROUP BY 1
ORDER BY 1
;

결과 예시