J’ai un client avec un cas d’utilisation bizarre qui souhaite que TOP puisse organiser un concours.
J’ai commencé par discourse/app/models/top_topic.rb at main · discourse/discourse · GitHub et j’ai élaboré ce qui suit, mais cela ne correspond pas à TOP. Y a-t-il quelque chose d’évident que j’aurais manqué ? Mon idée de repli est simplement de récupérer les fichiers JSON chaque jour avec un travail cron et d’écrire un script JSON-CSV pour les données qu’ils souhaitent.
-- [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 likes_count,
p.like_count score,
t.id topic_id,
t.posts_count posts_count,
p.like_count op_likes_count,
t.title topic_name,
t.views views_count,
p.user_id user_id,
t.category_id 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