针对特定用户进行活动调查

[Jim.Morrison]

下午好，

我想针对特定用户，按天统计其在指定日期范围内的点赞数、收到的点赞数、发帖数以及解决方案数。关于此类报表，有什么建议吗？我怀疑需要利用数据探索插件（Data Explorer Plugin），是否有人已经编写了适用于此类工作的查询语句？欢迎提供任何反馈！

[simon]

我在本地开发网站上有一个与您需求非常相似的查询。我对其稍作修改以满足您的要求。我尝试实现的一点是，允许该查询针对普通主题中发生的活动，或私信（PM）中发生的活动进行运行。我这样做的部分动机是希望查看自己在一段时间内的活动情况。能够按普通主题或私信进行查询，对我的情况来说是有意义的。当勾选 personal_messages 复选框时，查询对解决方案的处理方式并不理想——在这种情况下，solutions_provided 列将仅返回 NULL。也许应该从查询中移除普通主题/私信的切换选项。

这个查询并不过于复杂，但相对较长。如果您发现其结果有任何问题，请告诉我。

运行该查询前，您需要提供三个参数：username、start_date 和 end_date。日期参数需采用 ‘yyyy-mm-dd’ 格式，例如 2020-03-13。personal_messages 参数默认为 false。如果您希望查看私信中发生活动的结果，请勾选查询的 personal_messages 复选框。

--[params]
-- string :username
-- date :start_date
-- date :end_date
-- boolean :personal_messages = false

WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, :end_date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN topics t
ON t.id = p.topic_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
AND p.deleted_at IS NULL
AND p.post_number > 1
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
likes_given AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_given_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
solutions_provided AS (
SELECT tu.id,
day,
COUNT(tu.id) AS solutions_provided_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 15
GROUP BY day, tu.id
)

SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(likes_received_count, 0) AS likes_received,
COALESCE(likes_given_count, 0) AS likes_given,
(SELECT CASE WHEN NOT :personal_messages THEN COALESCE(solutions_provided_count, 0) END) AS solutions_provided
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
LEFT JOIN likes_given lg
ON lg.day = d.day
LEFT JOIN solutions_provided sp
ON sp.day = d.day
ORDER BY d.day DESC

[Jim.Morrison]

太完美了！谢谢 @simon！