[8] 데이터 가공을 위한 SQL - 사용자를 파악하기 위한 데이터 추출 : 사용자 별 액션 수 집계, 연령 별 구분[연령대] 별 특성 집계,

목차

3. 데이터 가공을 위한 SQL
 3-1. 하나의 값 조작하기 (5강)
 3-2. 여러 개의 값에 대한 조작 (6강)
  3-3. 하나의 테이블에 대한 조작 (7강)
3-4. 여러 개의 테이블 조작하기 (8강)
4. 매출을 파악하기 위한 데이터 추출 (9강)
  4-1. 시계열 기반으로 데이터 집계하기 (10강)
4-2. 다면적인 축을 사용해 데이터 집약하기
5. 사용자를 파악하기 위한 데이터 추출
  5-1. 사용자 전체의 특징과 경향 찾기
  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기
  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기
6. 웹사이트에서 행동을 파악하는 데이터 추출하기
  6-1. 사이트 전체의 특징/경향 찾기
  6-2. 사이트 내의 사용자 행동 파악하기
  6-3. 입력 양식 최적화하기
7. 데이터 활용의 정밀도를 높이는 분석 기술
  7-1. 데이터를 조합해서 새로운 데이터 만들기
  7-2. 이상값 검출하기
  7-3. 데이터 중복 검출하기
  7-4. 여러 개의 데이터셋 비교하기
8. 데이터를 무기로 삼기 위한 분석 기술
  8-1. 검색 기능 평가하기
  8-2. 데이터 마이닝
  8-3. 추천
  8-4. 점수 계산하기
9. 지식을 행동으로 옮기기
  9-1. 데이터 활용의 현장

---

 

※ Mysql 기준
 
5장. 사용자를 파악하기 위한 데이터 추출

11강. 사용자 전체의 특징과 경향 찾기

*서비스를 제공하는 측에서
1) 사용자의 속성 데이터를 집계해서 어떤 사용자가 사용중인지 파악하고,
2) 사용자의 행동 데이터를 집계해서 어떻게 사용하고 있는지 파악할 수 있다.

사용자 마스터 테이블 mst_users

액션 로그 테이블 action_log

액션 로그 테이블의 action컬럼

• view: 페이지 열람
• favorite: 관심상품 등록
• add_cart: 카트 추가
• purchase: 구매
• review: 상품 리뷰
• user_id가 비어있는 레코드는 로그인하지 않은 사용자의 행동을 나타냄
• 일반적으로는 각각의 action에 해당하는 데이터가 각각의 테이블에 있지만, 이런 식으로 액션 로그 테이블을 따로 만들면 별도의 join과 union없이도 데이터를 다룰 수 있다.

---

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

COUNT(DISTINCT~), ROLLUP, UU, 사용률, 개인별 통계

 

액션과 관련된 지표 집계하기

*UU(Unique Users): 중복 없이 집계된 사용자 수, 즉 액션UU란 액션을 취한 사용자 수를 중복 없이 집계한 것

액션	액션UU	액션 수	사용률	1명 당 액션 수
VIEW	4,365	52,083	100%	11.9
ADD_CART	381	978	8.7%	2.6
PURCHASE	234	819	5.4%	3.5

 

액션 수와 비율 계산하기

1. action_uu = 세션을 중복 없이 count
2. action_count = action 전체 count
3. total_uu = action 별이 아닌 전체로 중복 없이 세션 count
4. usage_rate = action 별 사용률, 액션uu를 전체uu로 나눈 값
5. count_per_user = 1인당 action수, 액션 수를 액션uu로 나눈 값

WITH stats AS(
	SELECT COUNT(DISTINCT session) AS total_uu
    FROM action_log)
SELECT l.action,
	COUNT(DISTINCT l.session) AS action_uu, -- 액션UU
    COUNT(*) AS action_count, -- 액션의 수
    s.total_uu, -- 전체 UU
    100.0 * COUNT(DISTINCT l.session) / s.total_uu AS usage_rate, -- 사용률 = <액션uu> / <전체uu>
    1.0 * COUNT(1) / COUNT(DISTINCT l.session) AS count_per_user -- 1인당 액션 수 = <액션 수> / <액션 UU>
FROM action_log AS l
CROSS JOIN stats AS s
GROUP BY l.action, s.total_uu;

---

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

user_id가 없는 session에는 guest 표시하기

WITH action_log_with_status AS(
	SELECT
    session,
    user_id,
    action,
    CASE WHEN COALESCE(user_id, '') <> '' THEN 'login' ELSE 'guest' END AS status
    FROM action_log)
SELECT * FROM action_log_with_status;

 

---

status에 따라 action 수를 집계하고, status에 관계 없이 모든 action수도 함께 집계하기

ROLLUP()

로그인 상태에 따른 액션 수(action_uu, action_count) 집계하기 (전체 포함)

WITH action_log_with_status AS(
	SELECT
		session,
        user_id,
        action,
        CASE WHEN COALESCE(user_id, '') <> '' THEN 'login' ELSE 'guest' END AS status
	FROM action_log)
SELECT 
	COALESCE(action, 'all') AS status,
    COALESCE(status, 'all') AS action,
    COUNT(*) AS action_count,
    COUNT(DISTINCT session) AS action_uu
FROM action_log_with_status
GROUP BY action, status WITH ROLLUP;

원본에 guest에 해당하는 데이터가 없음

* 비로그인 사용자가 로그인하면 각각의 액션에 +1이 된다. 반면 all은 session을 기반으로 집계되기 때문에 <login + guest = all>이 성립하지 않음에 주의

---

회원과 비회원을 구분해서 집계하기

• 한번이라도 로그인했던 사용자는 회원으로 구분하기
• 로그를 타임스탬프 순으로 나열하고, 한 번이라도 로그인한 사용자일 경우, 이후의 모든 로그를 member로 설정

1번이라도 로그인했던(user_id가 있는) session은 member처리하기

user_id를 stamp순으로 나열했을 때, max값의 user_id가 비어있지 않은 경우, member로 처리하기

SELECT session,
    user_id,
    action,
CASE WHEN COALESCE(MAX(session) OVER (PARTITION BY user_id
    ORDER BY stamp),'') <> '' THEN 'member' ELSE 'guest' END AS status,
    stamp
    FROM action_log;

원본에 user_id 빈 게 없어서 다 member로 나온다

※ 여기에서는 로그인하지 않은 때의 사용자 ID를 빈 문자열로 저장했기 때문에, user_id를 중복없이 count했을 경우 빈 문자열이 user_id의 카운트에 포함된다. 따라서 중복 없이 사용자 수를 정확히 집계하기 위해서는 사용자 ID를 NULL로 저장하는 것이 좋음

---

11-2. 연령 별 구분 집계하기

CASE식, CAST, 연령 별 구분
 

• 나이는 시간이 지나면서 변하기 때문에, 생일을 기반으로 리포트를 만드는 시점이 집계한다.
• 나이 계산 - 생일과 특정 날짜를 정수로 표현하고, 이 차이를 10,000으로 나누는 방법 활용

(age함수는 Postgre에만 존재..)

birth_date로 현재(특정 날짜) 기준 연령 구하기

2017.1.1을 기준으로 age계산하기

Steps
1. 20170101을 specific_int_date로 select
2. CAST로 birth_date를 int_date로 표현
3. specific_date에서 birth_date를 빼고 10,000으로 나눠주기
→ cast to int가 안됨 . . TO_DAYS() 함수 활용하고 365로 나누기
TO_DAYS()
: date 날짜에 0년 이후 날의 365일에 합한 수를 반환

WITH int_date AS(
	SELECT 
    20170101 AS specific_int_date,
    user_id,
    sex,
    birth_date
    FROM mst_users)
SELECT 
	user_id,
    sex,
    birth_date,
	FLOOR((TO_DAYS(specific_int_date) - TO_DAYS(birth_date)) /365) AS age
FROM int_date;

/*오늘 날짜 기준*/
SELECT 
	user_id,
    sex,
    birth_date,
	FLOOR((TO_DAYS(NOW()) - TO_DAYS(birth_date)) /365) AS age
FROM mst_users;

 
※(추가) 10대, 20대..의 연령대를 구하는 경우 FLOOR대신 TRUNCATE(숫자, 버릴 자릿수[필수]) 으로 1의 자리를 버려주면 쉽게 구할 수 있다. 

/*오늘 날짜 기준 TRUNCATE활용*/
SELECT 
	user_id,
    sex,
    birth_date,
    FLOOR((TO_DAYS(NOW()) - TO_DAYS(birth_date)) /365) AS age,
	TRUNCATE((TO_DAYS(NOW()) - TO_DAYS(birth_date)) /365,-1) AS age_cat
FROM mst_users;

---

연령으로 연령 별 구분 집계하기

연령 별 구분	성별
C	4-12세 남성과 여성
T	13-19세 남성과 여성
M1	20-34세 남성
M2	35-49세 남성
M3	50세 이상 남성
F1	20-34세 여성
F2	35-49세 여성
F3	50세 이상 여성

(▲)위의 표를 바탕으로 연령별 구분 집계하기

성별과 연령으로 연령 별 구분 계산하기

Steps
1. 나이가 20세 이상이면, 성별 접두사로 M또는 F 출력 (연령 별 구분에서 20세 미만은 성별 구분을 하지 않음)
2. CASE WHEN 활용
3. CASE식 두 개를 CONCAT

WITH int_date AS(
	SELECT 
    20170101 AS specific_int_date,
    user_id,
    sex,
    birth_date
    FROM mst_users),
case_with_age AS(
SELECT 
	user_id,
    sex,
    birth_date,
	FLOOR((TO_DAYS(specific_int_date) - TO_DAYS(birth_date)) /365) AS age
FROM int_date),
case_with_age_cat AS(
SELECT 
	user_id,
    sex,
    age,
    CONCAT(
    CASE WHEN age>=20 THEN sex ELSE '' END,
    CASE WHEN age BETWEEN 4 AND 12 THEN 'C'
		WHEN age BETWEEN 13 AND 19 THEN 'T'
        WHEN age BETWEEN 20 AND 34 THEN '1'
        WHEN age BETWEEN 35 AND 49 THEN '2'
        WHEN age >=50 THEN '3' END) AS category
FROM case_with_age)
SELECT * FROM case_with_age_cat;

※ 나이가 3세 이하인 경우, 연령 구분 코드 category가 NULL이 되어 CONCAT의 결과도 NULL이 된다.

---

연령 구분 별 유저 카운트 (유저 연령대 파악)

연령 별 구분의 사람 수 계산하기

WITH int_date AS(
	SELECT 
    20170101 AS specific_int_date,
    user_id,
    sex,
    birth_date
    FROM mst_users),
case_with_age AS(
SELECT 
	user_id,
    sex,
    birth_date,
	FLOOR((TO_DAYS(specific_int_date) - TO_DAYS(birth_date)) /365) AS age
FROM int_date),
case_with_age_cat AS(
SELECT 
	user_id,
    sex,
    age,
    CONCAT(
    CASE WHEN age>=20 THEN sex ELSE '' END,
    CASE WHEN age BETWEEN 4 AND 12 THEN 'C'
		WHEN age BETWEEN 13 AND 19 THEN 'T'
        WHEN age BETWEEN 20 AND 34 THEN '1'
        WHEN age BETWEEN 35 AND 49 THEN '2'
        WHEN age >=50 THEN '3' END) AS category
FROM case_with_age)
/*연령대 별 유저 카운트하기*/
SELECT 
	category,
    COUNT(*) AS user_count
FROM case_with_age_cat
GROUP BY category;

---

11-3. 연령 별 구분의 특징 추출하기

JOIN, GROUP BY, 연령 별 구분
 

제품 별 연령대 별 구매 건수 파악하기

제품 카테고리 별로 유저 연령대 그룹 별 구매 건수를 count하기

* action 중 purchase로그만 선택 (where조건절)

WITH int_date AS(
	SELECT 
    20170101 AS specific_int_date,
    user_id,
    sex,
    birth_date
    FROM mst_users),
case_with_age AS(
SELECT 
	user_id,
    sex,
    birth_date,
	FLOOR((TO_DAYS(specific_int_date) - TO_DAYS(birth_date)) /365) AS age
FROM int_date),
case_with_age_cat AS(
SELECT 
	user_id,
    sex,
    age,
    CONCAT(
    CASE WHEN age>=20 THEN sex ELSE '' END,
    CASE WHEN age BETWEEN 4 AND 12 THEN 'C'
		WHEN age BETWEEN 13 AND 19 THEN 'T'
        WHEN age BETWEEN 20 AND 34 THEN '1'
        WHEN age BETWEEN 35 AND 49 THEN '2'
        WHEN age >=50 THEN '3' END) AS category
FROM case_with_age)
/*제품 카테고리 별 유저 연령대 그룹 별 구매 건수 count*/
SELECT 
	p.category AS product_cat,
    q.category AS age_cat,
    COUNT(*) AS purchase_cnt
FROM action_log AS p
JOIN case_with_age_cat AS q
ON p.user_id = q.user_id
WHERE p.action = 'purchase'
GROUP BY p.category, q.category
ORDER BY p.category, q.category;

※ product_cat별 구매 건수와 age_cat별 구매 건수 각각으로는 사용자의 특징을 파악하기 힘들기 때문에, 두 테이블을 조인해서 확인하기

ABC 분석과 구성비 누계를 리포트에 추가하여 특성 파악

---

11-4. 사용자의 방문 빈도 집계하기

SUM 윈도 함수, 방문 빈도

 
*사이트를 매일 방문하는 사용자와, 일주일에 한 번 방문하는 사용자의 행동패턴에는 차이가 있을 것
 

특정 기간 동안 들어온 일자 수 별로 유저 카운트하기

한 주에 며칠 사용했는지 확인하는 쿼리

Steps
1. stamp에서 날짜 추출 (substring 활용)
2. user_id의 dt 중복없이 카운트
3. dt를 일주일로 지정 (action_day)
4. 지정한 기간 동안의 user 중복없이 카운트
5. action_day 카운트 수 별 user_count 출력

WITH dt_1 AS(
SELECT *,
	MID(stamp, 1, 10) AS dt
FROM action_log),
action_day_cnt_2 AS(
SELECT
	user_id,
    COUNT(DISTINCT dt) AS action_day_count
FROM dt_1
WHERE dt BETWEEN '2016-11-03' AND '2016-11-04' -- 데이터가 2일치밖에 없음..
GROUP BY user_id)
SELECT * FROM action_day_cnt_2;

여기까지는 user_id 별 지정된 기간 내 기록된 dt 개수 출력 (step 2,3)

WITH dt_1 AS(
SELECT *,
	MID(stamp, 1, 10) AS dt
FROM action_log),
action_day_cnt_2 AS(
SELECT
	user_id,
    COUNT(DISTINCT dt) AS action_day_count
FROM dt_1
WHERE dt BETWEEN '2016-11-03' AND '2016-11-04' -- 데이터가 2일치밖에 없음..
GROUP BY user_id)
/* action_day_count(지정된 기간 내 구매 횟수) 별로 몇 명이 해당하는지 출력*/
SELECT action_day_count,
	COUNT(DISTINCT user_id) AS user_count
FROM action_day_cnt_2
GROUP BY action_day_count
ORDER BY action_day_count;

step4,5

---

구성비와 구성비 누계 산출하기

WITH dt_1 AS (
    SELECT *,
        MID(stamp, 1, 10) AS dt
    FROM action_log
),
action_day_cnt_2 AS (
    SELECT
        user_id,
        COUNT(DISTINCT dt) AS action_day_count
    FROM dt_1
    WHERE dt BETWEEN '2016-11-03' AND '2016-11-04'
    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 ratio, -- 구성비
    100.0 * SUM(COUNT(DISTINCT user_id)) OVER (
        ORDER BY action_day_count) /  SUM(COUNT(DISTINCT user_id)) OVER() AS ratio_cum -- 구성비 누계
FROM action_day_cnt_2
GROUP BY action_day_count
ORDER BY action_day_count;

---