大家好..
我真的很需要大家的帮助，通过数据探索器来追踪我们的用户活动。
输入：用户名和日期
我希望通过查询得到的结果如下所示：
| 用户名 | 日期 | 创建的主题数 | 创建的帖子数 | 收到的点赞数 | 总字数 |
|---|---|---|---|---|---|
| 用户 A | 2019-01-10 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 | |||||
| 用户 B | 2019-01-10 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 | |||||
| 用户 B | 2019-01-10 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 | |||||
| 用户 C | 2019-01-10 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 | |||||
| … | |||||
| 用户 Z | 2019-01-10 | ||||
| 2019-01-02 | |||||
| 2019-01-03 | |||||
| … | |||||
| 2019-01-31 |
非常感谢您的帮助…
我认为您列出的输入与您期望的输出并不匹配。如果您希望获取单个用户的数据，下面的查询可能是一个不错的起点。它使用了公用表表达式（CTE）来使查询更易读和编写。您可以遵循相同的模式，向结果中添加更多数据。
有一点我不太确定：您是需要某一天的数据总和，还是每天发生的行为次数？例如，您是想了解在 2019-10-31 这一天，该用户在其所有在站期间总共发布了 20 篇帖子，还是想了解在 2019-10-31 这一天该用户恰好发布了 3 篇帖子？当前查询的结构返回的是后一种结果。
--[params]
-- string :username
-- date :start_date
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, NOW()::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 = 'regular'
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts,
SUM(array_length(regexp_split_to_array(raw, '\s'), 1)) AS word_count
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND p.deleted_at IS NULL
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 days
ON ua.created_at::date = day
WHERE ua.action_type = 2
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(word_count, 0) AS word_count,
COALESCE(likes_received_count, 0) AS likes_received
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
ORDER BY d.day
我想要每天发生的行为数量，完全按照您提供给我们的查询方式。
非常感谢您的帮助，这真的对我们很有帮助。
为了提升用户活跃度，我们计划举办一场挑战活动，评选出论坛中最活跃的用户并给予奖励。
因此，我们希望获取一些额外的数据，让挑战活动更加有趣。
我们目前拥有的数据（通过 @simon 提供的查询获取）包括：主题数、帖子数、字数统计和收到的点赞数。
一些额外的数据包括：
请帮助我们获取这些额外数据。
关于各项指标的权重分配，我们计划使用 Excel 进行处理。对于如何为每项指标分配权重以选出获胜者，您有什么建议吗？
我们希望这场挑战既严肃又有趣。最佳用户应是活跃度最高、最乐于助人且最受欢迎的综合体现。
再次衷心感谢您的帮助。
注意：我们能否按群组获取数据，只需输入群组 ID？我们的计划是将所有参与者加入一个群组。目前，我们需要逐个输入参与者的用户 ID。
这里也有一位来自曼彻斯特的伙伴：slight_smile
我们公司正计划内部举办一场类似的竞赛，以提升员工与社区的互动。
你们成功举办了这场竞赛吗？最终是如何提取数据的？又是如何为不同的互动指标分配权重的？
非常希望能和您交流！
谢谢
Julia