IT/SQL
[데이터 분석을 위한 SQL 레시피] 5장 12강 시계열에 따른 사용자 전체의 상태 변화 찾기_part1(p.233~p.275)
- -
728x90
반응형
💡 사용자의 서비스 사용 시계열로 수치화하고 변화를 시각화하는 방법을 소개
샘플데이터
- 사용자 마스터(mst_users)
withdraw_date : 탈퇴일
CREATE TABLE mst_users(
user_id STRING,
sex CHAR(1),
birth_date DATE,
register_Date DATE,
register_device STRING,
withdraw_date DATE
)
- 액션 로그(action_log)
CREATE TABLE action_log(
session STRING,
user_id STRING,
action STRING,
stamp DATETIME
)
1. 등록 수의 추이와 경향 보기
💡 SQL : COUNT(DISTINCT ~)
분석 : 등록수, 작대비
등록자가 ⬇️ ⇒ 서비스를 활성화하기 어려움
등록자가 ⬆️ ⇒ 사용자가 서비스에서 이탈하지 아닐지 분석해서 서비스 활성화와 연결
날짜별 등록 수의 추이
사용자 수를 집계할 때
- 식별할 수 있는 ID(사용자 ID)
- 중복을 제거해야 되지만, 중복이 될 수가 없다.
12-1. 날짜별 등록 수의 추이를 집계하는 쿼리
SELECT
register_date
, count(DISTINCT user_id) AS register_count
FROM
mst_users
GROUP BY
register_date
ORDER BY
register_date
;
월별 등록 추이
월별 집계
- 연과 월 데이터 → year_month 칼럼
- year_month로 집약해서 등록 수 집계
전월비
- LAG 윈도우 함수
12-2. 매달 등록 수와 전월비를 계산하는 쿼리
WITH
mst_users_with_year_month AS (
SELECT *
,substring(register_date, 1, 7) AS year_month
FROM mst_users
)
SELECT
year_month
, COUNT(DISTINCT user_id) AS register_count
, LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY year_month)
AS last_month_count
, 1.0
* COUNT(DISTINCT user_id)
/ LAG(count(DISTINCT user_id)) over(ORDER BY year_month)
AS last_month_ratio
FROM mst_users_with_year_month
GROUP BY year_month
;- LAG 함수: 이전 행의 값을 리턴
등록 디바이스별 등록 추이
🔖 등록한 디바이스에 따라 사용자 행동이 다를 수 있다.
device 칼럼을 이용해 등록한 디바이스를 알 수 있다.
- 등록과 동시에 추가되는 정보가 있다면,
- 해당 칼럼으로 변경해서 해당 칼럼 내역을 집계할 수 있다.(ex. 성별 or 지역)
12-3. 디바이스들의 등록 수를 집계하는 쿼리
WITH
mst_users_with_year_month AS (
-- [코드 12-2] 참고하기
SELECT *
,substring(register_date, 1, 7) AS year_month
FROM mst_users
)
SELECT
year_month
, COUNT(DISTINCT user_id) AS register_count
, COUNT(DISTINCT CASE WHEN register_device = 'pc' THEN user_id END) AS register_pc
, COUNT(DISTINCT CASE WHEN register_device = 'sp' THEN user_id END) AS register_sp
, COUNT(DISTINCT CASE WHEN register_device = 'app' THEN user_id END) AS register_app
FROM mst_users_with_year_month
GROUP BY year_month
;
2. 지속률과 정착률 산출하기
💡 SQL : COUNT(DISTINCT ~)
분석 : 지속률과 정착률
등록된 사용자가 아무리 많아도 서비스를 지속해서 사용하지 않는다면 의미 없는 시스템
- 지속이용자가 없음 ⇒ 이용자의 의욕감소
지속률과 정착률
지속률과 정착률 사용 구분
지속률과 관계있는 리포트
12-4. ‘로그 최근 일자’와 ‘사용자별 등록일의 다음날’ 계산하는 쿼리
WITH
action_log_with_mst_users AS(
SELECT
u.USER_id
, u.register_date
-- 액션 날짜와 로그 전체의 최신 날짜 자료형으로 젼환하기
, CAST(a.stamp AS date) AS action_date
, max(CAST(a.stamp AS date)) over() AS latest_date
-- 등록일 다음날의 날짜 계산하기
-- PostgreSQL의 경우
, cast(u.register_date::date + '1 day' :: INTERVAL AS date)
AS next_day_1
FROM
mst_users AS u
LEFT OUTER JOIN
action_log AS a
ON u.user_id = a.user_id
)
SELECT *
FROM action_log_with_mst_users
ORDER BY register_date
;- CAST('[변환하고자 하는 데이터]' AS [데이터형식]) : 데이터형식을 다른 데이터형식으로 변환하는 역할
- ::에 대한 ChatGPT 설명
- 이 코드에서 "::"은 SQL에서 유형 캐스트 연산자로 사용됩니다. 한 데이터 유형을 다른 데이터 유형으로 명시적으로 변환하는 데 사용됩니다. 주어진 코드에서 "::"은
u.register_date::date표현식을 "date" 데이터 유형으로 변환하는 데 사용됩니다.u.register_date에 "1일" 간격을 추가한 결과가 날짜 데이터 유형으로 리턴되도록 합니다.
- 이 코드에서 "::"은 SQL에서 유형 캐스트 연산자로 사용됩니다. 한 데이터 유형을 다른 데이터 유형으로 명시적으로 변환하는 데 사용됩니다. 주어진 코드에서 "::"은
12-5. 사용자의 액션 플래그를 계산하는 쿼리
- 지정한 날의 다음날에 액션을 했는지 0과 1의 플래그로 표현
- ‘지정한 날의 다음날’이 로그의 가장 최근 날짜를 넘는 경우 플래그를 NULL로 나타냄
WITH
action_log_with_mst_users AS(
-- [코드 12-4] 참고하기
)
, user_action_flag AS (
SELECT
user_id
, register_date
-- 4. 등록일 다음날에 액션을 했는지 안 했는지를 플래그로 나타내기
, SIGN(
-- 3. 사용자별로 등록일 다음날에 한 액션의 합계 구하기
SUM(
-- 1. 등록일 다음날이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN next_day_1 <= latest_date THEN
--2. 등록일 다음날의 날짜에 액션을 했다면 1, 안 했다면 0 지정하기
CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END
END
)
) AS next_1_day_action
FROM action_log_with_mst_users
GROUP BY user_id, register_date
)
SELECT *
FROM user_action_flag
ORDER BY register_date, user_id
;
12-6. 다음날 지속률을 계산하는 쿼리
WITH
action_log_with_mst_users AS(
-- [코드 12-4] 참고하기
WITH
action_log_with_mst_users AS(
SELECT
u.USER_id
, u.register_date
-- 액션 날짜와 로그 전체의 최신 날짜 자료형으로 젼환하기
, CAST(a.stamp AS date) AS action_date
, max(CAST(a.stamp AS date)) over() AS latest_date
-- 등록일 다음날의 날짜 계산하기
-- PostgreSQL의 경우
, cast(u.register_date::date + '1 day' :: INTERVAL AS date)
AS next_day_1
FROM
mst_users AS u
LEFT OUTER JOIN
action_log AS a
ON u.user_id = a.user_id
)
SELECT *
FROM action_log_with_mst_users
ORDER BY register_date
)
, user_action_flag AS(
-- [코드 12-5] 참고하기
SELECT
user_id
, register_date
-- 4. 등록일 다음날에 액션을 했는지 안 했는지를 플래그로 나타내기
, SIGN(
-- 3. 사용자별로 등록일 다음날에 한 액션의 합계 구하기
SUM(
-- 1. 등록일 다음날이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN next_day_1 <= latest_date THEN
--2. 등록일 다음날의 날짜에 액션을 했다면 1, 안 했다면 0 지정하기
CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END
END
)
) AS next_1_day_action
FROM action_log_with_mst_users
GROUP BY user_id, register_date
)
SELECT *
FROM user_action_flag
ORDER BY register_date, user_id
SELECT
register_date
, avg(100.0 * next_1_day_action) AS repeat_rate_1_day
FROM
user_action_flag
GROUP BY
register_date
ORDER BY
register_date
12-7. 지속률 지표를 관리하는 마스터 테이블을 작성하는 쿼리
WITH
repeat_interval(index_name, interval_date) AS (
--■ PostgreSQL의 경우 VALUES 구문으로 일시 테이블 생성 가능
-- 8강 5절 참고하기
VALUES
('01 day repeat',1)
, ('02 day repeat',2)
, ('03 day repeat',3)
, ('04 day repeat',4)
, ('05 day repeat',5)
, ('06 day repeat',6)
, ('07 day repeat',7)
)
SELECT *
FROM repeat_interval
ORDER BY index_name
12-8. 지속률을 세로 기반으로 집계하는 쿼리
WITH
repeat_interval(index_name, interval_date) AS (
--■ PostgreSQL의 경우 VALUES 구문으로 일시 테이블 생성 가능
-- 8강 5절 참고하기
VALUES
('01 day repeat',1)
, ('02 day repeat',2)
, ('03 day repeat',3)
, ('04 day repeat',4)
, ('05 day repeat',5)
, ('06 day repeat',6)
, ('07 day repeat',7)
)
, 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 latest_date
, r.index_name
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, CAST(CAST(u.register_date AS date) + INTERVAL '1 day' * r.interval_date AS date)
AS index_date
FROM
mst_users AS u
LEFT OUTER 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
-- 4. 등록일로부터 n일 후에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자별로 등록일로부터 n일 후에 한 액션의 합계 구하기
sum(
-- 1. 등록일로부터 n일 후가 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_date <= latest_date THEN
-- 2. 등록일로부터 n일 후의 날짜에 액션을 했다면 1, 아니라면 0 지정하기
CASE WHEN index_date = action_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_date
)
SELECT
register_date
, index_name
, avg(100.0 * index_date_action) AS repeat_rate
FROM
user_action_flag
GROUP BY
register_date, index_name
ORDER BY
register_date, index_name
;
💡 SIGN 함수는 부호 함수로 숫자 값의 부호를 결정하는 데 사용됩니다. 값이 음수이면 -1, 값이 0이면 0, 양수이면 1을 반환합니다.
12-9. 정착률 지표를 관리하는 마스터 테이블을 작성하는 쿼리
WITH
repeat_interval(index_name, interval_begin_date, INTERVAL_end_date) AS (
--■ PostgreSQL의 경우 VALUES 구문으로 일시 테이블 생성 가능
-- 8강 5절 참고하기
VALUES
('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
;
12-10. 정착률을 계산하는 쿼리
WITH
repeat_interval AS(
WITH
repeat_interval(index_name, interval_begin_date, INTERVAL_end_date) AS (
--■ PostgreSQL의 경우 VALUES 구문으로 일시 테이블 생성 가능
-- 8강 5절 참고하기
VALUES
('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
)
, 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 latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER 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
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
register_date
, index_name
, avg(100.0 * index_date_action) AS index_rate
FROM
user_action_flag
GROUP BY
register_date, index_name
ORDER BY
register_date, index_name
12-11. 지속률 지표를 관리하는 마스터 테이블을 정착률 형식으로 수정한 쿼리
정착률 / 지속률 지표를 한 테이블에서 관리하기 위한 쿼리
WITH
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
--■ PostgreSQL의 경우 VALUES 구문으로 일시 테이블 생성 가능
-- 8강 5절 참고하기
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
;12-12. n일 지속률을 집계하는 쿼리
주요 문법
- CAST(A as B): A테이블을 B 데이터타입으로 변환. A :: B로도 활용
- INTERVAL: datetime 타입의 데이터를 string 기반으로 정제해 새로운 테이블 생성(추가 조사 필요)
WITH -- repeate interval은 11번, 나머지는 10번 코드에서 참조
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
--■ PostgreSQL의 경우 VALUES 구문으로 일시 테이블 생성 가능
-- 8강 5절 참고하기
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 -- cast(A as B)를 활용, 컬럼 데이터타입 변환
, MAX(CAST(a.stamp AS date)) over() AS latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER 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
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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 repeate_rate
FROM user_action_flag
GROUP BY
index_name
ORDER BY
index_name
;3. 지속과 정착에 영향을 주는 액션 집계하기
12-13. 모든 사용자의 액션의 조합을 도출하는 쿼리
WITH repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
-- postgreSQL 의 경유 VALUES구문으로 일시 테이블 생성 가능
-- 4강 5절 참고
values('01 day repeat', 1, 1)
)
, 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 latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER 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
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
) -- 이상 12-12번 코드와 동일
, mst_actions AS (
SELECT 'view' AS ACTION
UNION ALL SELECT 'comment' AS ACTION
UNION ALL SELECT 'follow' AS ACTION
)
, mst_user_actions AS (
SELECT
u.user_id,
u.register_date,
a.ACTION
FROM mst_users AS u
CROSS JOIN
mst_actions AS a
)
SELECT *
FROM mst_user_actions
ORDER BY user_id, action
;12-14. 사용자의 액션 로그를 0, 1의 플래그로 표현하는 쿼리
WITH repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
-- postgreSQL 의 경유 VALUES구문으로 일시 테이블 생성 가능
-- 4강 5절 참고
-- 이하 코드 12_13과 동일
values('01 day repeat', 1, 1)
)
, 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 latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER 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
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
)
, mst_actions AS (
SELECT 'view' AS ACTION
UNION ALL SELECT 'comment' AS ACTION
UNION ALL SELECT 'follow' AS ACTION
)
, mst_user_actions AS (
SELECT
u.user_id,
u.register_date,
a.ACTION
FROM mst_users AS u
CROSS JOIN
mst_actions AS a
)
-- 이상 코드 12-13과 동일
, register_action_flag AS (
SELECT DISTINCT
m.user_id
, m.register_date
, m.ACTION
, CASE
WHEN a.ACTION IS NOT NULL THEN 1
ELSE 0
END AS do_action
, index_name
, index_date_action
FROM
mst_user_actions AS m
LEFT JOIN
action_log AS a
ON m.user_id = a.user_id
AND cast(m.register_date AS date) = CAST(a.stamp AS date)
AND m.ACTION = a.ACTION
LEFT JOIN user_action_flag AS f
ON m.user_id = f.user_id
WHERE
f.index_date_action IS NOT NULL
)
SELECT
*
FROM
register_action_flag
ORDER BY
user_id, index_name, action
;12-15. 액션에 따른 지속률과 정착률을 집계하는 쿼리
WITH repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
-- postgreSQL 의 경유 VALUES구문으로 일시 테이블 생성 가능
-- 4강 5절 참고
-- 이하 코드 12_13과 동일
values('01 day repeat', 1, 1)
)
, 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 latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER 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
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
)
, mst_actions AS (
SELECT 'view' AS ACTION
UNION ALL SELECT 'comment' AS ACTION
UNION ALL SELECT 'follow' AS ACTION
)
, mst_user_actions AS (
SELECT
u.user_id,
u.register_date,
a.ACTION
FROM mst_users AS u
CROSS JOIN
mst_actions AS a
)
-- 이상 코드 12-13과 동일
, register_action_flag AS (
SELECT DISTINCT
m.user_id
, m.register_date
, m.ACTION
, CASE
WHEN a.ACTION IS NOT NULL THEN 1
ELSE 0
END AS do_action
, index_name
, index_date_action
FROM
mst_user_actions AS m
LEFT JOIN
action_log AS a
ON m.user_id = a.user_id
AND cast(m.register_date AS date) = CAST(a.stamp AS date)
AND m.ACTION = a.ACTION
LEFT JOIN user_action_flag AS f
ON m.user_id = f.user_id
WHERE
f.index_date_action IS NOT NULL
)
--이상 12-14와 동일
SELECT
ACTION
, count(1) users
, avg(100.0 * do_action) AS usage_rate
, index_name
, avg(CASE do_action WHEN 1 THEN 100.0 * index_date_action end) AS idx_rate
, avg(CASE DO_action WHEN 0 THEN 100.0 * index_date_action end) AS no_action_idx_rate
FROM
register_action_flag
GROUP BY
index_name, ACTION
ORDER BY
index_name, ACTION
;4. 액션 수에 따른 정착률 집계하기
- 페이스북과 트위터 등 대표적인 SNS 사례 중 등록 후 1주일 이내에 10명을 팔로우하면, 해당 사용자는 서비스를 계속해서 사용한다라는 말이 있다.
- 이번 절에서는 등록일과 이후 7일 동안(7일 정착률 기간)에 실행한 액션 수에 따라 14일 정착률이 어떻게 변화하는 지 확인.
code. 12-16(액션의 계급 마스터와 사용자 액션 플래그의 조합을 산출하는 쿼리)
WITH
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
-- PostgreSQL의 경우 VALUES로 일시 테이블 생성 가능
-- 8강 5절
VALUES ('14 day retention', 8, 14)
)
, action_log_with_index_date AS (
-- [코드 12-10] 참고하기
SELECT
u.user_id
, u.register_date
-- 액션의 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환하기
, CAST(a.stamp AS date) AS action_date
, MAX(CAST(a.stamp AS date)) over() AS latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER JOIN
action_log AS a
ON u.user_id = a.user_id
CROSS JOIN
repeat_interval AS r
)
, user_action_flag AS (
-- [코드 12-10] 참고하기
SELECT
user_id
, register_date
, index_name
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
)
, mst_action_bucket(action, min_count, max_count) AS (
-- 액션 단계 마스터
VALUES
('comment', 0, 0)
, ('comment', 1, 5)
, ('comment', 6, 10)
, ('comment', 11, 9999) -- 최댓값으로 간단하게 9999 입력
, ('follow', 0, 0)
, ('follow', 1, 5)
, ('follow', 6, 10)
, ('follow', 11, 9999) --최댓값으로 간단하게 9999 입력
)
, mst_user_action_bucket AS (
-- 사용자 마스터와 액션 단계 마스터 조합
SELECT
u.user_id
, u.register_date
, a.action
, a.min_count
, a.max_count
FROM
mst_users AS u
CROSS JOIN
mst_action_bucket AS a
)
SELECT *
FROM
mst_user_action_bucket
ORDER BY
user_id, action, min_count
6
code. 12-17(등록 후 7일 동안의 액션 수를 집계하는 쿼리)
WITH
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
-- PostgreSQL의 경우 VALUES로 일시 테이블 생성 가능
-- 8강 5절
VALUES ('14 day retention', 8, 14)
)
, action_log_with_index_date AS (
-- [코드 12-10] 참고하기
SELECT
u.user_id
, u.register_date
-- 액션의 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환하기
, CAST(a.stamp AS date) AS action_date
, MAX(CAST(a.stamp AS date)) over() AS latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER JOIN
action_log AS a
ON u.user_id = a.user_id
CROSS JOIN
repeat_interval AS r
)
, user_action_flag AS (
-- [코드 12-10] 참고하기
SELECT
user_id
, register_date
, index_name
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
)
, mst_action_bucket(action, min_count, max_count) AS (
-- 액션 단계 마스터
VALUES
('comment', 0, 0)
, ('comment', 1, 5)
, ('comment', 6, 10)
, ('comment', 11, 9999) -- 최댓값으로 간단하게 9999 입력
, ('follow', 0, 0)
, ('follow', 1, 5)
, ('follow', 6, 10)
, ('follow', 11, 9999) --최댓값으로 간단하게 9999 입력
)
, mst_user_action_bucket AS (
-- 사용자 마스터와 액션 단계 마스터 조합
SELECT
u.user_id
, u.register_date
, a.action
, a.min_count
, a.max_count
FROM
mst_users AS u
CROSS JOIN
mst_action_bucket AS a
)
, register_action_flag As (
-- 등록일에서 7일 후까지 액션수를 세고,
-- 액션 단계와 14일 정착 달성 플래그 계산
SELECT
m.user_id
, m.action
, m.min_count
, m.max_count
, COUNT(a.action) AS action_count
, CASE
WHEN COUNT(a.action) BETWEEN m.min_count AND m.max_count THEN 1
ELSE 0
END As achieve
, index_name
, index_date_action
FROM
mst_user_action_bucket AS m
LEFT JOIN
action_log AS a
ON m.user_id = a.user_id
-- 등록일 당일부터 7일 후까지의 액션 로그 결합
-- PostgreSQL, Redshift의 경우
AND CAST(a.stamp AS date)
BETWEEN CAST(m.register_date AS date)
AND CAST(m.register_date AS date) + interval '7 days'
LEFT JOIN
user_action_flag AS f
ON m.user_id = f.user_id
WHERE
f.index_date_action IS NOT NULL
GROUP BY
m.user_id
, m.action
, m.min_count
, m.max_count
, f.index_name
, f.index_date_action
)
SELECT *
FROM
register_action_flag
ORDER BY
user_id, action, min_count
;
code. 12-18(등록 후 7일 동안의 액션 횟수별로 14일 정착률을 집계하는 쿼리)
WITH
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
-- PostgreSQL의 경우 VALUES로 일시 테이블 생성 가능
-- 8강 5절
VALUES ('14 day retention', 8, 14)
)
, action_log_with_index_date AS (
-- [코드 12-10] 참고하기
SELECT
u.user_id
, u.register_date
-- 액션의 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환하기
, CAST(a.stamp AS date) AS action_date
, MAX(CAST(a.stamp AS date)) over() AS latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER JOIN
action_log AS a
ON u.user_id = a.user_id
CROSS JOIN
repeat_interval AS r
)
, user_action_flag AS (
-- [코드 12-10] 참고하기
SELECT
user_id
, register_date
, index_name
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
)
, mst_action_bucket(action, min_count, max_count) AS (
-- 액션 단계 마스터
VALUES
('comment', 0, 0)
, ('comment', 1, 5)
, ('comment', 6, 10)
, ('comment', 11, 9999) -- 최댓값으로 간단하게 9999 입력
, ('follow', 0, 0)
, ('follow', 1, 5)
, ('follow', 6, 10)
, ('follow', 11, 9999) --최댓값으로 간단하게 9999 입력
)
, mst_user_action_bucket AS (
-- 사용자 마스터와 액션 단계 마스터 조합
SELECT
u.user_id
, u.register_date
, a.action
, a.min_count
, a.max_count
FROM
mst_users AS u
CROSS JOIN
mst_action_bucket AS a
)
, register_action_flag As (
-- 등록일에서 7일 후까지 액션수를 세고,
-- 액션 단계와 14일 정착 달성 플래그 계산
SELECT
m.user_id
, m.action
, m.min_count
, m.max_count
, COUNT(a.action) AS action_count
, CASE
WHEN COUNT(a.action) BETWEEN m.min_count AND m.max_count THEN 1
ELSE 0
END As achieve
, index_name
, index_date_action
FROM
mst_user_action_bucket AS m
LEFT JOIN
action_log AS a
ON m.user_id = a.user_id
-- 등록일 당일부터 7일 후까지의 액션 로그 결합
-- PostgreSQL, Redshift의 경우
AND CAST(a.stamp AS date)
BETWEEN CAST(m.register_date AS date)
AND CAST(m.register_date AS date) + interval '7 days'
LEFT JOIN
user_action_flag AS f
ON m.user_id = f.user_id
WHERE
f.index_date_action IS NOT NULL
GROUP BY
m.user_id
, m.action
, m.min_count
, m.max_count
, f.index_name
, f.index_date_action
)
SELECT
action
-- PostgreSQL, Redshift, 문자열 연결
, min_count || '~' || max_count AS count_range
, SUM(CASE achieve WHEN 1 THEN 1 ELSE 0 END) AS achieve
, index_name
, AVG(CASE achieve WHEN 1 THEN 100.0 * index_date_action END) AS achieve_index_date
FROM
register_action_flag
GROUP BY
index_name, action, min_count, max_count
ORDER BY
index_name, action, min_count
;
- 사용자의 액션을 기반으로 사용자를 집계.
- 액션별로 사용자를 집계하면, 사용자가 어떤 기능을 더 많이 사용하도록 유도해야 하는지 알 수 있다.
5. 사용 일수에 따른 정착률 집계하기
code. 12-19(등록일 다음날부터 7일 동안의 사용 일수와 28일 정착 플래그를 생성하는 쿼리)
WITH
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
-- PostgreSQL의 경우 VALUES로 일시 테이블 생성 가능
-- 8강 5절
VALUES ('28 day retention', 22, 28)
)
, action_log_with_index_date AS (
-- [코드 12-10] 참고하기
SELECT
u.user_id
, u.register_date
-- 액션의 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환하기
, CAST(a.stamp AS date) AS action_date
, MAX(CAST(a.stamp AS date)) over() AS latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER JOIN
action_log AS a
ON u.user_id = a.user_id
CROSS JOIN
repeat_interval AS r
)
, user_action_flag AS (
-- [코드 12-10] 참고하기
SELECT
user_id
, register_date
, index_name
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
)
, register_action_flag AS (
SELECT
m.user_id
, COUNT(DISTINCT CAST(a.stamp AS date)) AS dt_count
, index_name
, index_date_action
FROM
mst_users AS m
LEFT JOIN
action_log AS a
ON m.user_id = a.user_id
-- 등록 다음날부터 7일 이내의 액션 로그 결합하기
-- PostgreSQL, Redshift의 경우 다음과 같이 사용
AND CAST(a.stamp AS date)
BETWEEN CAST(m.register_date AS date) + interval '1 day'
AND CAST(m.register_date AS date) + interval '8 days'
LEFT JOIN
user_action_flag AS f
ON m.user_id = f.user_id
WHERE
f.index_date_action IS NOT NULL
GROUP BY
m.user_id
, f.index_name
, f.index_date_action
)
SELECT *
FROM
register_action_flag
;
code. 12-20(사용 일수에 따른 정착율을 집계하는 쿼리)
WITH
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
-- PostgreSQL의 경우 VALUES로 일시 테이블 생성 가능
-- 8강 5절
VALUES ('28 day retention', 22, 28)
)
, action_log_with_index_date AS (
-- [코드 12-10] 참고하기
SELECT
u.user_id
, u.register_date
-- 액션의 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환하기
, CAST(a.stamp AS date) AS action_date
, MAX(CAST(a.stamp AS date)) over() AS latest_date
, r.index_name
--지표의 대상 기간 시작일과 종료일 계산하기
--■ PostgreSQL의 경우는 다음과 같이 사용하기
, 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 OUTER JOIN
action_log AS a
ON u.user_id = a.user_id
CROSS JOIN
repeat_interval AS r
)
, user_action_flag AS (
-- [코드 12-10] 참고하기
SELECT
user_id
, register_date
, index_name
-- 4. 지표의 대상 기간에 액션을 했는지 플래그로 나타내기
, sign(
-- 3. 사용자 별로 대상 기간에 한 액션의 합계 구하기
sum(
-- 1. 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인하기
CASE WHEN index_end_date <= latest_date THEN
-- 2. 지표의 대상 기간에 액션을 했다면 1, 안 했다면 0 지정하기
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
)
, register_action_flag AS (
SELECT
m.user_id
, COUNT(DISTINCT CAST(a.stamp AS date)) AS dt_count
, index_name
, index_date_action
FROM
mst_users AS m
LEFT JOIN
action_log AS a
ON m.user_id = a.user_id
-- 등록 다음날부터 7일 이내의 액션 로그 결합하기
-- PostgreSQL, Redshift의 경우 다음과 같이 사용
AND CAST(a.stamp AS date)
BETWEEN CAST(m.register_date AS date) + interval '1 day'
AND CAST(m.register_date AS date) + interval '8 days'
LEFT JOIN
user_action_flag AS f
ON m.user_id = f.user_id
WHERE
f.index_date_action IS NOT NULL
GROUP BY
m.user_id
, f.index_name
, f.index_date_action
)
SELECT
dt_count AS dates
, COUNT(user_id) AS users
, 100.0 * COUNT(user_id) / SUM(COUNT(user_id)) OVER() AS user_ratio
, 100.0
* SUM(COUNT(user_id))
OVER(ORDER BY index_name, dt_count
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
/ SUM(COUNT(user_id)) OVER() AS cum_ratio
, SUM(index_date_action) AS achieve_users
, AVG(100.0 * index_date_action) AS achieve_ratio
FROM
register_action_flag
GROUP BY
index_name, dt_count
ORDER BY
index_name, dt_count;
728x90
'IT > SQL' 카테고리의 다른 글
| [데이터 분석을 위한 SQL 레시피] 6장 13강,14강 (1) | 2023.06.19 |
|---|---|
| [데이터 분석을 위한 SQL 레시피] 5장 12강 시계열에 따른 사용자 전체의 상태 변화 찾기_part2(p.275~p.302) (0) | 2023.06.17 |
| [데이터 분석을 위한 SQL 레시피] 5장 11강. 사용자 전체의 특징과 경향 찾기 (0) | 2023.06.17 |
| [데이터 분석을 위한 SQL 레시피] 4장 9강, 10강 (0) | 2023.06.17 |
| [해커랭크] Ollivander's Inventory (0) | 2023.04.08 |
Contents
소중한 공감 감사합니다