calculate and return the ratings using sql

Question

I have a table like this

What I am trying to achieve is weighted rating. For e.g., in this table we have five star as 3, four star as 1, three star as 1, two star as 1 and one star as 0. So the weighted rating is

((5x3) + (4x1) + (3x1) + (2x1) + (1x0)) / (3+1+1+1) = 4

for service uuid ff0177fe. How can I achieve this ?

J.D. · Accepted Answer · 2020-12-05T13:36:21.107

You just need an aggregative query using GROUP BY like so:

SELECT 
    service_uuid, 
    ((5 * SUM(five_rating)) + (4 * SUM(four_rating)) + (3 * SUM(three_rating)) + (2 * SUM(two_rating)) + SUM(one_rating))
    /
    (SUM(five_rating) + SUM(four_rating) +  SUM(three_rating)  + SUM(two_rating) + SUM(one_rating))
FROM RatingsTable
GROUP BY service_uuid

Also just keep in mind that your columns will return integer results and therefore the / symbol will do integer division which means it'll round down to the nearest 0 (e.g. 5 / 2 = 2). If you want a weighted rating that includes fractional decimal points then you just need to cast either the divisor or dividend to float or decimal. You can find more information about this in this StackOverflow post's accepted answer.

Erwin Brandstetter · Answer 2 · 2021-07-11T23:23:44.707

The sample suggests each row can only have one rating. And only 0 or 1 is in use.

Do the sum / count once in a subquery. That's clearer:

SELECT service_uuid
     , round((r5*5 + r4*4 + r3*3 + r2*2 + r1) * 1.0
           / (r5   + r4   + r3   + r2   + r1), 2) AS avg_rating
FROM  (
   SELECT service_uuid
        , count(five_rating  = 1 OR NULL) AS r5
        , count(four_rating  = 1 OR NULL) AS r4
        , count(three_rating = 1 OR NULL) AS r3
        , count(two_rating   = 1 OR NULL) AS r2
        , count(one_rating   = 1 OR NULL) AS r1
   FROM   tbl
   GROUP  BY 1
   ) sub;

See:

For absolute performance, is SUM faster or COUNT?

In Postgres 9.4 or later, use the faster aggregate FILTER clause:

Return counts for multiple ranges in a single SELECT statement

SELECT service_uuid
     , round((r5*5 + r4*4 + r3*3 + r2*2 + r1) * 1.0
           / (r5   + r4   + r3   + r2   + r1), 2) AS avg_rating
FROM  (
   SELECT service_uuid
        , count(*) FILTER (WHERE five_rating  = 1) AS r5
        , count(*) FILTER (WHERE four_rating  = 1) AS r4
        , count(*) FILTER (WHERE three_rating = 1) AS r3
        , count(*) FILTER (WHERE two_rating   = 1) AS r2
        , count(*) FILTER (WHERE one_rating   = 1) AS r1
   FROM   tbl
   GROUP  BY 1
   ) sub;

Multiplying with 1.0 converts the bigint counts to numeric, so we avoid integer division and can feed the result to round() to get two fractional digits (you choose).

But your table design seems inefficient.
Either use 1 integer column instead of 5, and allow values from 1 - 5.
Or use 5 boolean columns.
Smaller table, simpler query.

score -1 · Answer 3 · edited May 05 '23 at 06:00

SELECT ((r5*5 + r4*4 + r3*3 + r2*2 + r1*1) / (r5   + r4   + r3   + r2   + r1)) AS avg_rating 
         FROM 
         (
             SELECT 
             count(ra."ratingValue") FILTER (WHERE ra."recipeId" = ${recipeId} and ra."ratingValue" > 4) AS r5,
             count(ra."ratingValue") FILTER (WHERE ra."recipeId" = ${recipeId} and ra."ratingValue" > 3 and ra."ratingValue" <=4) AS r4,
             count(ra."ratingValue") FILTER (WHERE ra."recipeId" = ${recipeId} and ra."ratingValue" > 2 and ra."ratingValue" <=3) AS r3, 
             count(ra."ratingValue") FILTER (WHERE ra."recipeId" = ${recipeId} and ra."ratingValue" > 1 and ra."ratingValue" <=2) AS r2,
             count(ra."ratingValue") FILTER (WHERE ra."recipeId" = ${recipeId} and ra."ratingValue" > 0 and ra."ratingValue" <=1) AS r1
             FROM master_catalog.ratingmaster ra
         ) as t

calculate and return the ratings using sql

3 Answers3