Notations bayésiennes dans postgresql

Question

J'ai la table suivante dans ma base de données

  Name     |    total_stars   |      total_reviews
 Item A             27                       7
 Item B             36                       9
 Item C             27                       7
 Item D             30                       6
 Item E             0                        0
 Item F             0                        0
 Item F             15                       3

Je regardais cet article J'ai essayé de mettre en place des classements bayésiens dans une base de données postgresql.

La formule donnée pour le classement est la suivante

br = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / 
(avg_num_votes + this_num_votes)

où :

• avg_num_votes : Le nombre moyen de votes de tous les éléments qui ont num_votes>0
• avg_rating : La note moyenne de chaque élément (encore une fois, parmi ceux qui ont num_votes>0)
• this_num_votes : nombre de votes pour ce point
• this_rating : la note attribuée à cet article

Voici la requête que j'ai trouvée, mais elle ne fonctionne pas :

with avg_num_votes as (
      select AVG(total_reviews) 
           from business 
           where total_reviews != 0), 
       avg_rating as (
          select AVG(total_stars/total_reviews) 
          from business 
          where total_reviews != 0)
select * from business 
  order by ((avg_num_votes * avg_rating) + (total_stars)) / (avg_num_votes + total_reviews);

Je reçois : ERROR: column "avg_num_votes" does not exist

Answer 1

Fidèle à SQL

with av as (
    select avg(total_reviews) avg_num_votes
    from business
    where total_reviews > 0
), ar as (
    select name, avg(total_stars * 1.0 / total_reviews) avg_rating
    from business
    where total_reviews > 0
    group by name
)
select b.*, avg_rating, avg_num_votes,
    (avg_num_votes * avg_rating + total_stars)
    /
    (avg_num_votes + total_reviews) br
from
    business b
    left join
    ar on ar.name = b.name
    inner join
    av on true
order by br, b.name
    ;
  name  | total_stars | total_reviews |     avg_rating     |   avg_num_votes    |                 br                 
--------+-------------+---------------+--------------------+--------------------+------------------------------------
 Item A |          27 |             7 | 3.8571428571428571 | 6.4000000000000000 | 3.85714285714285712238805970149254
 Item C |          27 |             7 | 3.8571428571428571 | 6.4000000000000000 | 3.85714285714285712238805970149254
 Item B |          36 |             9 | 4.0000000000000000 | 6.4000000000000000 | 4.00000000000000000000000000000000
 Item D |          30 |             6 | 5.0000000000000000 | 6.4000000000000000 | 5.00000000000000000000000000000000
 Item F |           0 |             0 | 5.0000000000000000 | 6.4000000000000000 | 5.00000000000000000000000000000000
 Item F |          15 |             3 | 5.0000000000000000 | 6.4000000000000000 | 5.00000000000000000000000000000000
 Item E |           0 |             0 |                    | 6.4000000000000000 |