نشاط الزيارة الجماعية
لقد كنت أعمل على استعلامات تفحص نشاط المجموعات.
- زوار فريدون
- إجمالي الزيارات
- الزيارات عبر الجوال مقابل الزيارات عبر سطح المكتب
- نسبة أعضاء المجموعة الذين قاموا بالزيارة
- متوسط وقت القراءة لكل زيارة
- متوسط المنشورات المقروءة لكل زيارة
- متوسط وقت القراءة لكل عضو
- متوسط المنشورات المقروءة لكل عضو
أسئلة
- أشك في أن جدول
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