PostgreSQL – Execute Default Query When No Rows Returned

default valuepostgresql

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

Best Answer

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

Related Solutions

How to Get Latest Five Scores Per User Per Subject in PostgreSQL

Do you want the ones with less than 5 scores in mix. If you want only the 5 scores one, you can add a having to your query:

    SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
    FROM  (
       SELECT *
             ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
      FROM mg.scores
      WHERE score IS NOT NULL) as score
       ) AS x
    WHERE  x.rn <= 5
    GROUP  BY user_id,dept
    HAVING count(*) > 4;

PostgreSQL – Left Join Not Working with Sub-query

You could use a CROSS JOIN from score_sort to all the students and then a LEFT join to score:

SELECT st.student_id, 
       AVG(COALESCE(sc.value, default_value)) AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort AS sort 
  CROSS JOIN student AS st
  LEFT JOIN score AS sc
    ON  sort.subject_id = sc.subject_id 
    AND   st.student_id = sc.student_id
GROUP BY st.student_id ;

A different approach would be to alter your query to count the available scores each student has (in the 5 top subjects):

SELECT score.student_id, 
       (SUM(score.value) + (5 - COUNT(score.subject_id)) * default_value) / 5 
           AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
JOIN score ON score_sort.subject_id = score.subject_id 
GROUP BY score.student_id ;

But even with this, you would still not get any result for the students that have no score in any of the 5 top subjects.

Best Answer

Related Solutions

How to Get Latest Five Scores Per User Per Subject in PostgreSQL

PostgreSQL – Left Join Not Working with Sub-query

Related Question