PostgreSQL:五颗星，订购对象 [英] PostgreSQL: five stars rating, ordering objects

问题描述

在我的数据库中，用户可以为每个组添加1到5星的投票. 然后，我必须通过这些投票来显示排行榜. 到目前为止，我一直在按平均票数对其进行排序，而没有权重.这不是很好，因为拥有5.0个20票的组位于拥有4.9个平均值和10000票的组之前.

In my database users can add a vote from 1 to 5 stars to every groups. Then I have to display a leaderboard by those votes. What I was doing until now is to order them by votes average without a weight. This is not so nice because a group having 5.0 with 20 votes is before of a group having 4.9 avg and 10000 votes.

这是我的投票表:

CREATE TABLE IF NOT EXISTS votes(
user_id BIGINT,
group_id BIGINT,
vote SMALLINT,
vote_date timestamp,
PRIMARY KEY (user_id, group_id)

这是我现在对它们进行排序的方式:

This is how I sort them now:

SELECT 
      group_id,
      COUNT(vote) AS amount,
      ROUND(AVG(vote), 1) AS average, 
      RANK() OVER(PARTITION BY s.lang ORDER BY ROUND(AVG(VOTE), 1)DESC, COUNT(VOTE)DESC)
FROM votes
LEFT OUTER JOIN supergroups AS s 
USING (group_id)
GROUP BY group_id, s.lang, s.banned_until, s.bot_inside
HAVING 
      (s.banned_until IS NULL OR s.banned_until < now()) 
      AND COUNT(vote) >= %s 
      AND s.bot_inside IS TRUE

如何增加一种权重来解决我之前说过的问题?

How could I add a sort of weight to solve the problem I said before?

我在这里了解了贝叶斯方法，但是我不确定这是否正确，因为我读到它即将对顶部进行排序'n'元素，而我必须做一个包括其中任何一个的排行榜.

I read about bayesan approach here but I am not sure if it's the right thing because I read it's about to sort the top 'n' elements, while I have to do a leaderboard including anyone of them.

推荐答案

您将不得不以某种方式捏造它， 也许是这样.

you're going to have to fudge it somehow, perhaps this way.

order by (0.0+sum(vote))/(count(vote)+log(count(vote)))

或者说sqrt可能比日志更好，这取决于您希望人口数量的多少.

Or sqrt might work better than log, it depends how much weight you want the population size to have.

order by (0.0+sum(vote))/(count(vote)+sqrt(count(vote)))

基本上，软糖需要以比其输入速度慢的速度增加.您甚至可以尝试一个常量.

basically the fudge needs to be a function that increases at a slower rate than it input. you could even try a constant.

这篇关于PostgreSQL:五颗星，订购对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！