获取昨日点赞或者浏览量，前10的帖子

Product Support

AI 生成的摘要

zhang_zhiyuan asks how to retrieve the top 10 posts from yesterday based on likes or views, noting that the database only stores cumulative values. ondrej suggests enabling the Discourse Data Explorer plugin. zhang_zhiyuan confirms the plugin is not enabled and asks if it’s necessary for detailed data. nat confirms that enabling the plugin will allow for queries scoped to the past day and provides SQL queries for retrieving the top 10 posts by likes and views from yesterday.

1

我想获取昨日点赞或者浏览量，前10的帖子，有什么办法可以得到吗,似乎数据库存储的都是累计值，谢谢

2

嘿 :slight_smile:

你们的网站是否启用了 Discourse Data Explorer 插件？

3

没有启用呢，是不是需要启用 才会记录更详细的数据

4

您应该启用它，然后您将能够进行针对过去一天（昨天）的特定查询。

昨日获赞最多的 10 篇帖子查询

-- 昨日获赞最多的 10 篇帖子
WITH yesterday_actions AS (
  SELECT 
    post_id,
    COUNT(*) AS like_count
  FROM post_actions
  WHERE 
    created_at::date = CURRENT_DATE - 1
    AND post_action_type_id = 2  -- 点赞操作类型
  GROUP BY post_id
)

SELECT 
  p.id AS post_id,
  t.id AS topic_id,
  t.title AS topic_title,
  p.post_number,
  u.username AS author,
  ya.like_count AS likes_yesterday
FROM yesterday_actions ya
JOIN posts p ON p.id = ya.post_id
JOIN topics t ON t.id = p.topic_id
JOIN users u ON u.id = p.user_id
ORDER BY likes_yesterday DESC
LIMIT 10;

昨日浏览量最多的 10 篇帖子查询

-- 昨日浏览量最多的 10 篇帖子
WITH yesterday_topic_views AS (
  SELECT 
    topic_id,
    COUNT(*) AS view_count
  FROM topic_views
  WHERE viewed_at::date = CURRENT_DATE - 1
  GROUP BY topic_id
)

SELECT 
  t.id AS topic_id,
  t.title,
  u.username AS creator,
  ytv.view_count AS views_yesterday
FROM yesterday_topic_views ytv
JOIN topics t ON t.id = ytv.topic_id
JOIN users u ON u.id = t.user_id
ORDER BY views_yesterday DESC
LIMIT 10;

（由 AI 机器人生成！）