Gruppenbesuchsaktivität
Ich arbeite an Abfragen, die die Aktivität von Gruppen analysieren.
- Einzigartige Besucher
- Gesamtbesuche
- Mobile vs. Desktop-Besuche
- Prozentsatz der Gruppenmitglieder, die besuchen
- Durchschnittliche Lesezeit pro Besuch
- Durchschnittlich gelesene Beiträge pro Besuch
- Durchschnittliche Lesezeit pro Mitglied
- Durchschnittlich gelesene Beiträge pro Mitglied
Fragen
- Ich vermute, dass die Tabelle
user_visitsnur einen Datensatz pro eindeutigeruser_idpro Tag erfasst, davisited_atein DATE-Typ und kein DATETIME oder TIMESTAMP ist, selbst wenn ein Nutzer mehrfach am selben Tag von verschiedenen Gerätetypen aus besucht. Wenn ich meine Abfrage nach TAG ausführe, sind die Gesamtzahl der eindeutigen Mitglieder gleich der Gesamtzahl der Besuche. Kann jemand meine Annahme bestätigen? Falls ja, lautet die Folgefrage: Was passiert mit dem Wert fürmobile, wenn ein Nutzer drei Mal am selben Tag von einem Laptop, einem Desktop und dann einem Smartphone aus besucht, in beliebiger Reihenfolge? - Seltsamerweise ist
AVG(uv.posts_read)nicht gleichSUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id)). Ich wäre daher neugierig zu erfahren, wo meine Abfrage falsch liegt oder ob ich die Tabelleuser_visitsoder ihre Felder falsch interpretiere. Kann jemand den Unterschied zwischen diesen beiden Berechnungen erläutern?
Abfrage:
Zusammenfassung
-- [Parameter]
-- null string :group_name = DeinGruppenName
-- 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