1

I need to calculate the average score of a list of userId.

I have a table user_place_score:

(user_id, place_id, score)

Sometimes I do not have the score (no row for (user_id,place_id) in the table) so I need to fetch a "default" score from the table default_place_score:

(place_id, score)

Is is possible in PostgreSQL to do this in one query? For a given list of users and one place_id, select all their scores in the user_place_score and if no row is found then take the score found if the default_place_score table?

Basically I want to avoid doing this kind of stuff:

for (Integer userId : userIds) {
  score = (query to fetch the score for this userId)
  if (score == null)
    defaultScore = (query to fetch the default score for this PlaceId)

  total = total + defaultScore
}

//return the average
return total/numberOfUser
  • If a user has no score, is there a row with NULL in score column? Or not? (you say "not row found") If there is no row, how do you know a user's place_id? – ypercubeᵀᴹ Jan 13 '15 at 21:51
  • I'm sorry I wasn't clear enough in my question. When I say that I have no score this means that the row is not present "at all" in the table – Alexis Jan 13 '15 at 22:03
  • And you are interested in a specific place_id? – ypercubeᵀᴹ Jan 13 '15 at 22:05
  • Yes the input are a list of useids and one placeid – Alexis Jan 13 '15 at 22:06
1

It seemed you need a simple LEFT JOIN but with the added comments, it gets a bit more complicated:

SELECT 
    u.user_id,
    p.place_id,
    COALECSE(ups.score, d.score) AS score
FROM 
    user AS u
  CROSS JOIN                                          -- the places we are
    (VALUES (1),(2),(3),...,(10)) AS p (place_id)     -- interested in
  LEFT JOIN
    user_place_score AS ups
      ON  ups.user_id = u.user_id
      AND ups.place_id = p.place_id
  LEFT JOIN 
    default_place_score AS d
      ON d.place_id = p.place_id
WHERE                                         -- the users we are
    u.user_id IN (6, 28, 496, ...) ;          -- interested in       
  • Thanks alot ypercube! Yes indeed it is more complex! I have just tried and it does perfectly what i was looking for! I know I didn't specify it in the question, but is possible to select the AVG when using COALECSE? – Alexis Jan 14 '15 at 0:52
  • You can use AVG(COALESCE(...)) of course, If you have for example GROUP BY p.place_id (and remove the user_id from the Select list) – ypercubeᵀᴹ Jan 14 '15 at 0:54
1

Please try the following query:

SELECT
   user_place_score.user_id,
   user_place_score.place_id,
   COALESCE(user_place_score.score, default_place_score.score) AS score
FROM
   user_place_score
   LEFT OUTER JOIN default_place_score ON default_place_score.place_id = user_place_score.place_id

COALESCE function returns first no-NULL value from the given list, so if scorefrom user_place_score is not NULL then the function peaks user_place_score.score, else it will take the value from default_place_score.score.

1

You could use COALESCE() .. it returns the first non-null value in the list passed to it..

COALESCE((QUERY TO GET SCORE FOR USERID),(QUERY TO GET DEFAULT SCORE))

EDIT

oh - you added "list" requirement ... see @ypercube's answer, then.

EDIT2

If the place_ids exist in a separate table, and they only DON'T EXIST in the table you're querying to get scores (which, imo, would be considered a 'good design decision'), then you should include that table in your query .. COALESCE() as used in @ypercube's answer should work fine in that case.

SELECT 
  u.user_id,
  u.place_id,
  COALECSE(u.score, d.score) AS score
FROM
  place_list AS pl
LEFT JOIN
  user_place_score AS u ON pl.place_id = u.place_id
LEFT JOIN 
  default_place_score AS d
    ON d.place_id = u.place_id ;

Above taken and edited from @ypercube's answer.

  • Yep, tried that already but how can I make it loop through a list of userId ? – Alexis Jan 13 '15 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.