그룹 방문 활동
그룹별 활동을 분석하는 쿼리를 작성하고 있습니다.
- 고유 방문자 수
- 총 방문 횟수
- 모바일 대비 데스크톱 방문
- 그룹 멤버 중 방문 비율
- 방문당 평균 읽기 시간
- 방문당 평균 읽은 게시글 수
- 멤버당 평균 읽기 시간
- 멤버당 평균 읽은 게시글 수
질문
visited_at이 DATETIME이나 TIMESTAMP가 아닌 DATE 타입이기 때문에, 사용자가 하루에 여러 기기에서 여러 번 방문하더라도user_visits테이블에는 고유한 user_id당 하루에 하나의 레코드만 기록되는 것 같습니다. 날짜별(DAY)로 쿼리를 실행해 보면 총 고유 멤버 수가 총 방문 횟수와 같게 나옵니다. 제 추측이 맞는지 확인해 주실 수 있나요? 맞다면 후속 질문은 사용자가 노트북, 데스크톱, 그리고 휴대폰으로 하루에 3번 방문했을 때(순서 무관)mobile값은 어떻게 되는가가 됩니다.- 이상하게도
AVG(uv.posts_read)값이SUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id))값과 같지 않은 것을 확인했습니다. 제 쿼리가 어디서 잘못되었거나user_visits테이블이나 필드를 잘못 해석하고 있는지에 대해 의견을 듣고 싶습니다. 이 두 계산 방식의 차이에 대해 조언해 주실 수 있나요?
쿼리:
요약
-- [params]
-- null string :group_name = YourGroupName
-- date :start_date = 2019/09/01
-- date :end_date = 2019/10/01
-- null string :frame = day
with mobile as (
SELECT uv.id,
count(DISTINCT(uv.user_id)) as UniqueMobile,
date_part(:frame, uv.visited_at::date) as Day,
g.name as GroupName
from user_visits uv
join users u on uv.user_id = u.id
join group_users gu on gu.user_id = u.id
join groups g on g.id = gu.group_id
where mobile = true
and uv.visited_at >= :start_date::date
and uv.visited_at < :end_date::date
and g.name = :group_name
GROUP BY GroupName, Day, uv.id
),
desktop as (
SELECT uv.id,
count(DISTINCT(uv.user_id)) as UniqueDesktop,
date_part(:frame, uv.visited_at::date) as Day,
g.name as GroupName
from user_visits uv
join users u on uv.user_id = u.id
join group_users gu on gu.user_id = u.id
join groups g on g.id = gu.group_id
where mobile = false
and uv.visited_at >= :start_date::date
and uv.visited_at < :end_date::date
and g.name = :group_name
GROUP BY GroupName, Day, uv.id
)
SELECT
date_part(:frame, uv.visited_at::date) as VisitDate,
count(DISTINCT(uv.user_id)) as UniqueMembers,
count(uv.id) as AllVisits,
count(m.UniqueMobile) as MobileVisits,
count(d.UniqueDesktop) as DesktopVisits,
round((count(DISTINCT(uv.user_id)) * 100.0) / groups.user_count, 2) as Percent,
round(avg(uv.posts_read),2) as "Posts Read (avg Visit)",
(interval '1' minute * ROUND((cast(AVG(uv.time_read) as decimal)/60), 2)) as "Read Time (avg Visit)",
(SUM(uv.posts_read)/count(DISTINCT(uv.user_id))) as "Posts Read (avg Member)",
date_trunc('second',(interval '1' minute * (ROUND((cast(SUM(uv.time_read) as decimal)/60), 2)/(count(DISTINCT(uv.user_id)))))) as "Read Time (avg Member)"
FROM users
join group_users on group_users.user_id = users.id
join groups on group_users.group_id = groups.id
LEFT join user_visits uv on uv.user_id = users.id
LEFT JOIN mobile m ON m.id = uv.id
LEFT JOIN desktop d ON d.id = uv.id
where groups.name = :group_name
AND uv.visited_at::date >= :start_date
and uv.visited_at::date < :end_date
group by VisitDate, groups.user_count
order by VisitDate asc