我有一位客户有一个奇特的用例，希望 TOP 能够运行一个竞赛。
我从 discourse/app/models/top_topic.rb at main · discourse/discourse · GitHub 开始，得出了下面的代码，但它并没有匹配到 TOP。我是否遗漏了什么明显的地方？我的备选方案是每天通过 cron 作业拉取 JSON 文件，并编写一个将 JSON 转换为 CSV 的脚本来获取他们需要的数据。
-- [params]
-- date :start_date = '2019-05-01'
-- date :stop_date = '2019-05-30'
-- int :log_views_multiplier = 2
-- int :least_likes_per_post_multiplier = 3
-- int :num_topics = 100
WITH top_topics AS (
SELECT t.like_count AS likes_count,
p.like_count AS score,
t.id AS topic_id,
t.posts_count AS posts_count,
p.like_count AS op_likes_count,
t.title AS topic_name,
t.views AS views_count,
p.user_id AS user_id,
t.category_id AS category_id
FROM topics t
JOIN posts p ON p.topic_id = t.id AND p.post_number = 1
WHERE t.created_at > to_date(:start_date, 'YYYY-MM-DD')
AND t.created_at < to_date(:stop_date, 'YYYY-MM-DD')
),
top AS (
SELECT log(GREATEST(views_count, 1)) * :log_views_multiplier +
op_likes_count * 0.5 +
CASE WHEN top_topics.likes_count > 0 AND top_topics.posts_count > 0
THEN
LEAST(top_topics.likes_count / top_topics.posts_count, :least_likes_per_post_multiplier)
ELSE 0
END +
CASE WHEN topics.posts_count < 10 THEN
0 - ((10 - topics.posts_count) / 20) * op_likes_count
ELSE
10
END +
log(GREATEST(top_topics.posts_count, 1)) AS score,
topic_id
FROM top_topics
LEFT JOIN topics ON topics.id = top_topics.topic_id AND
topics.deleted_at IS NULL
)
SELECT topic_id, topic_name, t.user_id, u.username, likes_count, category_id
FROM top_topics t
LEFT JOIN users u ON t.user_id = u.id
ORDER BY likes_count DESC
LIMIT :num_topics