グループ訪問アクティビティ
私はグループのアクティビティを調査するクエリの作成に取り組んでいます。
- 一意の訪問者数
- 総訪問数
- モバイル対デスクトップ訪問数
- グループメンバーの訪問割合
- 訪問あたりの平均読了時間
- 訪問あたりの平均投稿読了数
- メンバーあたりの平均読了時間
- メンバーあたりの平均投稿読了数
質問
user_visitsテーブルは、visited_atが DATETIME または TIMESTAMP ではなく DATE であるため、1 日に複数回、複数のデバイスタイプから訪問した場合でも、1 日の 1 人の一意の user_id につき 1 件のレコードのみを記録しているのではないかと疑っています。DAY 単位でクエリを実行すると、一意のメンバーの総数が訪問数の総数と一致しています。私の仮定が正しいか確認していただけませんか？ もしそうであれば、次の質問は、ユーザーが 1 日にラップトップ、デスクトップ、そして順不同で携帯電話から 3 回訪問した場合、mobileの値はどうなるのかということです。- 奇妙なことに、
AVG(uv.posts_read)の値がSUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id))と一致しないことも確認しました。私のクエリがどこで誤っているのか、あるいはuser_visitsテーブルやフィールドの解釈を間違えているのか、興味があります。それらの 2 つの計算の違いについてご意見をお聞かせください。
クエリ:
サマリー
-- [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