Tengo un cliente con un caso de uso extraño que desea que TOP pueda ejecutar un concurso.
Comencé con discourse/app/models/top_topic.rb at main · discourse/discourse · GitHub y elaboré lo siguiente, pero no está coincidiendo con TOP. ¿Hay algo obvio que me esté faltando? Mi idea de respaldo es simplemente extraer los archivos JSON cada día con un trabajo cron y escribir un script de JSON a CSV para los datos que necesitan.
-- [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