5장 11강 사용자 전체의 특징과 경향 찾기
진행 페이지 : 186p ~ 202p
action_log 테이블
1.액션과 관련된 지표 계산하기
step1)세션 중복없이 count, action 전체 count, 전체 중복 없이 count
with cte_1(action,action_uu, action_count, total_uu) as (
select
from (select action,count(distinct session) as action_uu,count() as total_count from action_log group by action order by 1) a cross join (select count(distinct session) from action_log ) b)
step2)action별 사용률과 1인당 action 수 구하기
select ,round(action_uu100/total_uu,2) as usage_rate ,
round(action_count/action_uu,2) as count_per_user
from cte_1;
결과
컬럼 해석
기존 컬럼은 세션, 유저 아이디, 행동, 카테고리, 상품, 양, 시간이 있었다.
사용자들이 특정 기간동안 어떤 기능(행동)을 사용하는지 확인하고자
행동을 group by 하고 세션 수를 확인해보았다.
1.action_uu
: 액션을 기준으로, 중복 없이 세션 수 얼만큼 되는지 확인하기
세션: 클라이언트가 웹 서버에 접속해있는 상태가 하나의 단위이다.
2.action_count
: 액션을 기준으로, 세션 수 자체가 얼만큼 있는지 확인하기(중복 O)
3.total_uu
: 세션 자체 수
4.usage_rate
: 액션 별 사용된 세션 비율
( 특정 액션에서는 전체 세션 몇프로를 사용한건지)
: action_uu*100/total_uu
5.count_per_user
: action_count/action_uu
: 1인당 action 수
(1인당 얼만큼 해당 action을 사용한건지.)
2. 로그인 사용자와 비로그인 사용자를 구분해서 집계하기 (login_status)
코드
-- 로그인 여부의 기준) user_id
-- user_id 가 없을 경우 status 컬럼에 guest라고 표현하기
-- 컬럼: session / user_id / action / status
with action_log_with_status as(select session, user_id , action ,
case when user_id is not null then 'login'
else 'guest'
end as status
from action_log)
select * from action_log_with_status;
결과
해당 데이터에는 log_id가 null인 값이 없어서 status가 guest인 경우가 없음
3.로그인 상태에 따라 액션 수를 따로 집계하기
코드
-- 컬럽 : action, login_status, action_uu, action_count
with action_log_with_status as(select session, user_id , action ,
case when user_id is not null then 'login'
else 'guest'
end as login_status
from action_log)
select ifnull(action,'all') as action,
ifnull(login_status,'all') as login_status, sum(session) as sum_session
from action_log_with_status
group by action, login_status with rollup;
결과
login_status에 guest인 함수가 없어서 다음과 같이 결과값이 출력된다.
설명(rollup함수란?)
group by을 한 column 계층 구조에 따라서 값을 순차적으로 계산해준다.
예를 들어 category와 sub_category가 있는데, 서브 카테고리 하위 기준 값과
카테고리 상위 기준 값을 둘 다 한 표로 출력하고 싶다면 해당 함수를 사용하면 된다.
예제 함수
(mysql 이라 with rollup 붙임)
select category, sub_category , avg(price) as average_price
from table
group by category,sub_category with rollup;
4
step 1.
with cte1 as (select user_id,(2000-date_format(birth_date, '%Y') + 25) as age, sex from mst_users),
결과
step 2.
cte2 as (SELECT *,
CASE
WHEN (age BETWEEN 20 AND 34) AND (sex IN ('M')) THEN 'M1'
WHEN (age BETWEEN 35 AND 49) AND (sex IN ('M')) THEN 'M2'
WHEN (age BETWEEN 50 AND 100) AND (sex IN ('M')) THEN 'M3'
WHEN (age BETWEEN 20 AND 34) AND (sex IN ('F')) THEN 'F1'
WHEN (age BETWEEN 35 AND 49) AND (sex IN ('F')) THEN 'F2'
WHEN (age BETWEEN 50 AND 100) AND (sex IN ('F')) THEN 'F3'
WHEN (age BETWEEN 4 AND 12)THEN 'C'
WHEN (age BETWEEN 13 AND 19) THEN 'T'
END AS category
FROM cte1)
결과
4-1.카테고리 기준 count
select category, count(*) as count
from cte2
group by category;
4-2.연령별 구분의 특징 추출하기
select b.category, action, count(*) as purchase_count
from action_log a join cte2 b
on a.user_id = b.user_id
group by b.category, action;
결과
