[데이터 분석을 위한 SQL 레시피] 11강 - 사용자 전체의 특징과 경향 찾기

사용자를 위한 서비스를 제공할 때 사용자와 관련된 정보로 알고 싶은 것은?

- 사용자의 속성(나이, 성별, 주소) : 마스터 테이블

- 사용자의 행동(구매 상품, 빈도) : 액션 로그 테이블

 

어떤 속성의 사용자가 사용하는지 or 사용자가 어떻게 사용하는 지 알 수 있어야 함

 

마스터 테이블

 

액션 로그 테이블

---

1. 사용자의 액션 수 집계하기

UU : Unique User, 중복 없이 집계된 사용자 수

사용률(usage_Rate) : 특정 액션 UU를 전체 액션 UU로 나눈 것

 

with stats as(
	select count(distinct session) as total_uu
	from action_log
)
select
	l.action
	,count(distinct l.session) as action_uu
	,count(*) as action_count
	,s.total_uu
	,100.0 * count(distinct l.session) / s.total_uu as usage_rate
	,1.0 * count(1) / count(distinct l.session) as count_per_user
from
	action_log as l
	cross join
	stats as s
group by
	l.action
	,s.total_uu

 

total_UU 의미 (uu는 사용자 수인데, 세션을 사용자 수라고 보는 게 맞나?)

- 세션의 수

- action_uu : 특정 액션이 기록된 세션의 수

 

cross join <-> where 테이블,테이블

 

stats 는 그냥 상수 값 하나를 나타냄

join 해준 이유 : 연산시 이 상수를 활용해서 새로운 칼럼을 도출하는 데에 사용하기 위해서

 

 

로그인 사용자와 비로그인 사용자를 구분해서 집계하기

고객 아이디가 없는데 로그 기록이 있다면 비로그인 사용자

with action_log_with_status as(
select
	session
	,user_id
	,action
	,case when coalesce(user_id,'') <> '' then 'login' else 'guest' end as login_status
from
	action_log
)
select * from action_log_with_status

 

<> : 같지 않다

user_id가 null 이면 ''

user_id가 null이 아니면 login, null 이면 guest

 

with action_log_with_status as(
select
	session
	,user_id
	,action
	,case when coalesce(user_id,'') <> '' then 'login' else 'guest' end as login_status
from
	action_log
)
select
	coalesce(action,'all') as action
	,login_status
	,coalesce(login_status, 'all') as login_status
	,count(distinct session) as action_uu
	,count(1) as action_count
from
	action_log_with_status
group by
	rollup(action, login_status)

 

rollup : 그룹별 집계 뿐만 아니라 총 집계도 해줌

* 두가지 컬럼을 기준으로 했기 때문에 action의 총 집계와 action별 login_status의 총 집계가 출력됨

-> 집계시 null 값이 생기므로 all 이라고 정해두는 것!

 

 

로그인 사용자 vs 비로그인 사용자

-> user_id 존재 여부로 판단

-> 회원 vs 비회원으로 생각할 수 있음

 

자세히 생각해보면

회원 vs 비회원

-> 회원이 비로그인 사용자일 수도 있음.

-> 이전에 한 번이라도 로그인했다면 회원으로 판단