كيف يمكنني إضافة عمود للوقت المقروء، والمنشورات المقروءة، وزيارات المستخدم إلى هذا؟
بشكل أساسي، أود دمج الاستعلام التالي مع الاستعلام الذي نشرته هنا، بالإضافة إلى إضافة الوقت المقروء (إذا أمكن):
select user_id,
count(1) as visits,
sum(posts_read) as posts_read
from user_visits
where posts_read > 0
and visited_at > CURRENT_TIMESTAMP - INTERVAL '7 days'
group by user_id
order by visits desc, posts_read desc
ملاحظة: أفضل القدرة على تحديد معلمة الفاصل الزمني كما هو الحال في استعلامك، ولكن أود إما أن يكون المستخدم من الاستعلام الذي نشرته هو البريد الإلكتروني الكامل (وليس فقط نطاق البريد الإلكتروني).
أنا جديد في العبث بكتابة الاستعلامات وأفهم المعلمات وتمكنت من تعديل استعلامك ليكون عنوان البريد الإلكتروني الكامل. لا يمكنني مزج أعمدة الزيارات وposts_read من الاستعلام الذي شاركته، ولا إضافة وقت القراءة.
-- [params]
-- string :interval = 7 days
WITH post_data AS (
SELECT
p.user_id,
COUNT(p.id) AS total_posts,
COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS topics,
COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS posts,
COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS pms
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval
AND p.user_id > 0
AND p.deleted_at ISNULL
AND t.deleted_at ISNULL
AND p.post_type <> 3
GROUP BY p.user_id
),
user_data AS (
SELECT
uv.user_id,
COUNT(*) AS visits,
SUM(posts_read) AS posts_read,
SUM(time_read)/60 AS time_read
FROM user_visits uv
WHERE uv.visited_at >= CURRENT_DATE - INTERVAL :interval
GROUP BY uv.user_id
)
SELECT
ud.user_id,
ue.email,
ud.visits,
ud.posts_read,
ud.time_read,
pd.total_posts,
pd.topics,
pd.posts,
pd.pms
FROM user_data ud
LEFT JOIN post_data pd ON pd.user_id = ud.user_id
JOIN user_emails ue ON ue.user_id = ud.user_id AND ue.primary = TRUE
ORDER BY ud.posts_read DESC
