Групповое посещение

Создание сообщества Данные и отчетность
1

Активность групповых посещений

Я работаю над запросами, анализирующими активность групп.

  • Уникальные посетители
  • Общее количество посещений
  • Посещения с мобильных устройств против настольных компьютеров
  • Процент участников группы, совершивших посещение
  • Среднее время чтения за посещение
  • Среднее количество прочитанных постов за посещение
  • Среднее время чтения на одного участника
  • Среднее количество прочитанных постов на одного участника

Вопросы

  1. Я подозреваю, что таблица user_visits сохраняет только одну запись на уникальный user_id в день, поскольку поле visited_at имеет тип DATE, а не DATETIME или TIMESTAMP, даже если пользователь посещает сайт несколько раз в день с разных типов устройств. Когда я запускаю свой запрос по ДНЯМ, я вижу, что общее количество уникальных участников равно общему количеству посещений. Может ли кто-то подтвердить, что мое предположение верно? Если это так, то следующим вопросом становится: что происходит со значением mobile, если пользователь посещает сайт три раза в день: сначала с ноутбука, затем с настольного компьютера, а потом с телефона, в любом порядке?
  2. Странно, но я также вижу, что 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
2

Да, это верно.

Если пользователь читает посты при посещении с нового устройства, столбец mobile обновляется в соответствии с последним устройством, с которого было совершено посещение. Например: если пользователь начинает день с чтения 2 постов в десктопном браузере, для идентификатора пользователя создаётся запись user_visits с posts_read равным 2 и mobile равным false. Если затем пользователь входит в систему с мобильного устройства и читает ещё 3 поста, запись user_visits за этот день обновляется: posts_read: 5 и mobile: true. Это можно проверить с помощью Data Explorer; просто убедитесь, что пользователь читает посты, которые он ранее не просматривал.

3

Спасибо, есть ли какие-то идеи по последнему вопросу? Я сторонник хороших данных, а не плохих, и тот факт, что значения похожи, но не идентичны, вызывает удивление, особенно с учётом вашего объяснения, что в таблице user_visits для каждого uv.user_id и uv.visited_at существует только одно значение uv.id.

Возможно, это не очень поможет, но вот пример моих данных:

Дата посещения Прочитано постов (среднее за посещение) Прочитано (среднее на участника)
13 1.18 1
14 4.15 4
15 7.18 7
16 6.15 6
4

Когда вы делите целые числа, PostgreSQL вернет целое число. Попробуйте что-то вроде SUM(posts_read)::float / COUNT(DISTINCT(user_id)) и посмотрите, сохраняется ли разница. Возможно, вам потребуется округлить результат до двух знаков после запятой.