[Pandas/MySQL] Active User

[Pandas/MySQL] Active User

 

DAU, WAU, MAU같이 사용자 수로 평가 지표를 나누는 쿼리 작성해보기.

---

데이터 가져오기

from sqlalchemy import create_engine
import numpy as np
import pandas as pd
engine = create_engine(MYSQL_CONNECTION_STRING)

%load_ext sql
%sql {MYSQL_CONNECTION_STRING}

query = """
SELECT char_code
FROM new_character_log
WHERE date >= '2024-04-01' and date <= '2024-04-30'
"""

temp = %sql {query}
df = pd.DataFrame(temp)
df

• Open API로 얻은 로그 데이터를 한 번 정제해서 MySQL에 저장한 데이터.
• Active User에서 중요한건, Day/Week/Month마다 한 번 등장하면 세지 않아야 한다는 점
• 더 나아가서, 로그 데이터같이 방대한 데이터에서 데이터를 가져올 때 자주 사용하는 컬럼에 인덱스를 부여한다.
• 이 때, MONTH(date)같이 쿼리를 작성하면 새로운 파생컬럼에서 데이터를 Full Scan한다고 한다.
• 이렇게 사용하면 인덱스를 부여하는 의미가 없으니, between이나 부등호를 사용해서 데이터를 가져오자.

---

Pandas 쿼리

보통 SQL에서 한 번 거쳐서 가져와서 필요성이 높진 않지만 세트로 공부

SQL에서 가져오면 date type 체크 해주기.

1. DAU

dau = df.groupby(df['date'].dt.date).agg(
    dau=('char_code', 'nunique')
).reset_index()

• date컬럼에서 dt.date로 날짜만 꺼내와서 groupby를 진행한다.

2. WAU

df['week'] = df['date'].dt.to_period('W-THU')
wau = df.groupby('week').agg(
    wau=('char_code', 'nunique')
).reset_index()

wau['week_number'] = wau['week'].dt.start_time.dt.isocalendar().week
wau['week'] = wau['week']
wau

# wau['start'], wau['end'] = zip(*wau['week'].str.split('/'))

• period 객체를 사용하니, 각 주의 시작부터 끝 날짜까지 포함된다.
• 몇주차인지 확인하는 컬럼도 추가하기 위해 week_nubmer를 iso 캘린더에서 주차를 추출한다.
• 시작 끝 날짜도 알고싶으면 week를 split해서 할당하면 된다. (Series 객체라서 split한거를 언패킹하고 다시 zip으로)

3. MAU

df['month'] = df['date'].dt.to_period('M').astype(str)
mau = df.groupby('month').agg(
    mau=('char_code', 'nunique')
).reset_index()

• WAU와 비슷한 방식으로 작성하면 된다.
• 동작시간이 MAU가 다른 쿼리들에 비해서 굉장히 오래걸리는데, 메모리 오버헤드가 커져서 그런듯 하다.

---

MySQL 쿼리

1. DAU

SELECT 
    DATE(date) AS date,
    COUNT(DISTINCT char_code) AS dau
FROM 
    new_character_log
WHERE
    date BETWEEN '2024-04-01' AND '2024-04-30';
GROUP BY 
    DATE(date)
ORDER BY 
    date DESC

• 아까처럼 WHERE 조건을 사용해서 4월 날짜를 인덱스를 활용해서 빠르게 뽑아낸다.
• 이후 DATE + COUNT DISTINCT로 묶으면 빠르게 구할 수 있다.

• 대충 요런식으로 나온다.

---

2. WAU

SELECT 
    YEARWEEK(date,4) AS year_week,
    COUNT(DISTINCT char_code) AS wau
FROM
    new_character_log
WHERE
    date BETWEEN '2024-04-01' AND '2024-04-30'
GROUP BY
	YEARWEEK(date,4)
ORDER BY
	year_week;

• YEARWEEK라는 메서드를 사용한다.
• YEARWEEK에 date를 넣으면, 해당 년도에 n번째 주를 반환한다.
• 다른 한 인자는 start를 언제부터 끊을 건지이다.

월	화	수	목	금	토	일
1	6	5	4	3	2	0

• 이런식으로 나온다.
• 4월의 첫째주나 마지막주에서 짤릴 가능성이 있어서, WHERE 조건에서 주차별 조건으로 대체를 하는 것도 나쁘지 않아보인다.

---

3. MAU

SELECT
    DATE_FORMAT(date, '%Y-%m') AS `year_month`,
    COUNT(DISTINCT char_code) AS mau
FROM
    new_character_log
GROUP BY
    `year_month`
ORDER BY
    `year_month`;

• 4월 말고 주어진 DB 테이블에서 MAU를 구하는 쿼리
• DATE_FORMAT으로 년월을 추출하면 된다.

• 신규 이벤트가 있어서 4월에 약간 높고, 이후로는 쭉 감소하는 MAU 추세를 볼 수 있다.

---