群组访问活动
我一直在研究查看群组活动的查询。
- 独立访客数
- 总访问次数
- 移动端与桌面端访问
- 群组成员访问比例
- 每次访问平均阅读时间
- 每次访问平均阅读帖子数
- 每位成员平均阅读时间
- 每位成员平均阅读帖子数
问题
- 我怀疑
user_visits表每天每个user_id只记录一条数据，因为visited_at是 DATE 类型而不是 DATETIME 或 TIMESTAMP，即使用户在一天内从多种设备类型多次访问也是如此。当我按天运行查询时，我发现独立成员总数等于访问总数。有人能确认我的假设是否正确吗？ 如果是，那么后续问题就变成了：如果用户一天内从笔记本电脑、台式机然后手机（顺序任意）访问三次，mobile字段的值会发生什么变化？ - 奇怪的是，我还发现
AVG(uv.posts_read)的值与SUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id))不相同，因此我很好奇我的查询是在哪里出错了，或者我是否对user_visits表或字段的理解有误。有人能谈谈这两种计算之间的区别吗？
查询：
摘要
-- [参数]
-- 空字符串 :group_name = 您的群组名称
-- 日期 :start_date = 2019/09/01
-- 日期 :end_date = 2019/10/01
-- 空字符串 :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