Activité de visite de groupe
Je travaille sur des requêtes qui analysent l’activité des groupes.
- Visiteurs uniques
- Nombre total de visites
- Visites mobiles par rapport aux visites sur ordinateur de bureau
- Pourcentage d’adhérents du groupe ayant effectué une visite
- Temps de lecture moyen par visite
- Nombre moyen de publications lues par visite
- Temps de lecture moyen par adhérent
- Nombre moyen de publications lues par adhérent
Questions
- Je soupçonne que la table
user_visitsne capture qu’un seul enregistrement paruser_idunique par jour, étant donné quevisited_atest de type DATE et non DATETIME ou TIMESTAMP, même si l’utilisateur effectue plusieurs visites dans la journée à partir de différents types d’appareils. Lorsque j’exécute ma requête par JOUR, je constate que le nombre total d’adhérents uniques est égal au nombre total de visites. Quelqu’un peut-il confirmer si mon hypothèse est correcte ? Si c’est le cas, la question suivante devient : que devient la valeur pourmobilesi l’utilisateur effectue trois visites dans la journée, d’abord depuis un ordinateur portable, puis un ordinateur de bureau, puis un téléphone, dans n’importe quel ordre ? - Étrangement, je constate également que
AVG(uv.posts_read)n’a pas la même valeur queSUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id)). Je serais donc curieux de savoir où ma requête fait fausse route ou si j’interprète incorrectement la tableuser_visitsou ses champs. Quelqu’un peut-il intervenir pour expliquer la différence entre ces deux calculs ?
Requête :
Résumé
-- [paramètres]
-- chaîne null :group_name = VotreNomDeGroupe
-- date :start_date = 2019/09/01
-- date :end_date = 2019/10/01
-- chaîne null :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 "Publications lues (moyenne par visite)",
(interval '1' minute * ROUND((cast(AVG(uv.time_read) as decimal)/60), 2)) as "Temps de lecture (moyenne par visite)",
(SUM(uv.posts_read)/count(DISTINCT(uv.user_id))) as "Publications lues (moyenne par adhérent)",
date_trunc('second',(interval '1' minute * (ROUND((cast(SUM(uv.time_read) as decimal)/60), 2)/(count(DISTINCT(uv.user_id)))))) as "Temps de lecture (moyenne par adhérent)"
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