current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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
share|improve this question
    
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 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 –  Johny19 Jan 13 at 22:03
    
And you are interested in a specific place_id? –  ypercube Jan 13 at 22:05
    
Yes the input are a list of useids and one placeid –  Johny19 Jan 13 at 22:06

4 Answers 4

up vote 1 down vote accepted

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
share|improve this answer
    
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? –  Johny19 Jan 14 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 at 0:54
    
Exelent, thanks a lot! –  Johny19 Jan 14 at 0:57
up vote 1 down vote

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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer
    
Yep, tried that already but how can I make it loop through a list of userId ? –  Johny19 Jan 13 at 21:50
up vote 0 down vote 
SELECT p.place_id, u.user_id
     , COALECSE(ups.score, ds.score) AS score
FROM  (SELECT 123)                          p (place_id)  -- 1 place_id
CROSS  JOIN unnest('{31,2,99,1235}'::int[]) u (user_id)   -- n user_id
LEFT   JOIN user_place_score    ups USING (user_id, place_id)
LEFT   JOIN default_place_score ds  ON ds.place_id = p.place_id
                                   AND ups.place_id IS NULL

This way, the last join only looks up rows where the first join failed.

To return total number of users and the average score for the given place:

SELECT p.place_id
     , count(DISTINCT u.user_id) AS total_users  -- DISTINCT maybe not necessary
     , avg(COALECSE(ups.score, ds.score)) AS avg_score
FROM  (SELECT 123)                          p (place_id)  -- 1 place_id
CROSS  JOIN unnest('{31,2,99,1235}'::int[]) u (user_id)   -- n user_id
LEFT   JOIN user_place_score ups USING (user_id, place_id)
LEFT   JOIN default_place_score ds ON ds.place_id = p.place_id
                                  AND ups.place_id IS NULL
GROUP  BY 1;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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