Bayesian ratings in postgresql

Question

I have the following table in my db

  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

I was looking at this article and was trying to implement bayesian rankings in postgresql database.

The formula given for the rank is

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

where:

• avg_num_votes: The average number of votes of all items that have num_votes>0
• avg_rating: The average rating of each item (again, of those that have num_votes>0)
• this_num_votes: number of votes for this item
• this_rating: the rating of this item

This is the query I came up with, but it is not working:

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);

I am getting: ERROR: column "avg_num_votes" does not exist

Answer 1

Operator WITH in Postgres is only used to create additional working queries to be used in main query.

This query is using sub-selects in FROM clause instead and works as you expect:

SELECT business.* FROM business,
     (SELECT avg(total_reviews) AS v
      FROM business 
      WHERE total_reviews != 0
     ) AS avg_num_votes,
     (SELECT avg(total_stars/total_reviews) AS v 
      FROM business 
      WHERE total_reviews != 0
     ) AS avg_rating
ORDER BY ((avg_num_votes.v * avg_rating.v) + (total_stars)) / (avg_num_votes.v + total_reviews)

---

EDIT: Actually, using WITH is also possible, but does not seem to be shorter compared to first form. Also, it is less portable - first solution will work on MySQL, but this will not:

WITH
    avg_num_votes AS (
        SELECT avg(total_reviews) AS v
        FROM business 
        WHERE total_reviews != 0
    ),
    avg_rating AS (
        SELECT avg(total_stars/total_reviews) AS v
        FROM business
        WHERE total_reviews != 0
    )
SELECT business.*
FROM business, avg_num_votes, avg_rating
ORDER BY ((avg_num_votes.v * avg_rating.v) + (total_stars)) / (avg_num_votes.v + total_reviews)

Answer 2

SQL Fiddle

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 |