PostgreSQL: Set the max value in the column +1 if the following is NULL

Question

I'm a newbie in SQL and I know that the following question is popular but any proposed solutions do not help for me. So, I have a table

ratingId | userId
1        | 1
2        | 2
NULL     | 3
NULL     | 4

Now I want to set '3', '4' etc instead of NULL for each row with ratingId = NULL, which means MAX value of last NOT NULL value + 1

I have used many ways but the most popular that was found is max()

My current code is

SELECT
    COALESCE(rating.id, max(rating.id)) AS id,

but it does not work :( I still have NULL values. Any suggestions, please?

Please provide better sample data. Why doesn't user_id just work? What if there are duplicates? — Gordon Linoff
– Gordon Linoff, Commented Mar 10, 2021 at 17:06
table 'user' has relation with table 'rating' by user.id => rating.userId Brand-new users do not have a rating by default, but I need to display 0 values for such users in my view called 'ratingExpanded'. — Vyacheslav Fedorin
– Vyacheslav Fedorin, Commented Mar 11, 2021 at 9:12

Gordon Linoff · Accepted Answer · 2021-03-10 17:07:58Z

2

Does this do what you want?

select coalesce(ratingId,
                coalesce(max(ratingId) over (), 0) +
                         count(*) filter (where ratingId is null) over (order by userid)
               ) as imputed_ratingId

An equivalent phrasing is:

select coalesce(ratingId,
                coalesce(max(ratingId) over (), 0) +
                         row_number() over (partition by ratingId order by userid)
               ) as imputed_ratingId

These provide a unique ratingId for the rows where it is NULL, with incremental values over the previous maximum.

answered Mar 10, 2021 at 17:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vyacheslav Fedorin Over a year ago

Yes! this is it. Thanks a lot!

Gordon Linoff Over a year ago

@VyacheslavFedorin . . . Is there a reason you unaccepted this answer? You comment suggests tht it does exactly what you want.

Vyacheslav Fedorin · Accepted Answer · 2021-03-11 08:43:59Z

I was in a hurry to answer. NULL is replaced with 64, but should start from 61.

ratingId | userId
1        | 1
2        | 2
.........|.......
60       | 60
64       | 61 // these row should have ratingId: 61 instead of NULL
64       | 62 // these row should have ratingId: 62 instead of NULL

Here is a Raw SQL

SELECT
    coalesce(r.id,
        coalesce(max(r.id) over (), 0) +
                 count(*) filter (where r.id is null) over (order by r.Id)
       ) as id,
    r2.seqnum AS position,
    coalesce(r3.avg, 0) AS avg,
    r3."avgPosition",
    u.id AS "userId"
FROM ("user" u
      CROSS JOIN "userRole" ur
      LEFT JOIN rating r
      JOIN (SELECT
            r2_1.id,
            r2_1."userId",
            r2_1."userRoleId",
            r2_1."performerRatingGroupId",
            r2_1.value,
            row_number() OVER (PARTITION BY r2_1."userRoleId", r2_1."performerRatingGroupId" ORDER BY r2_1.value DESC) AS seqnum
        FROM rating r2_1
      ) r2 ON ((r2.id = r.id))
      JOIN (SELECT
        r3_1.id,
        r3_2.avg,
        dense_rank() OVER (ORDER BY r3_2.avg) AS "avgPosition"
      FROM
        (rating r3_1
          JOIN (SELECT
              rating.id,
              round(avg(rating.value) OVER (PARTITION BY rating."userId" ORDER BY rating."userId")) AS avg
            FROM rating
          ) r3_2 ON ((r3_1.id = r3_2.id))
        ) 
    ) r3 ON ((r3.id = r.id))
    ON u.id = r."userId" AND ur.id = r."userRoleId"
   )
GROUP BY
  r.id,
  r2.seqnum,
  r3.avg,
  r3."avgPosition",
  u.id

Collectives™ on Stack Overflow

PostgreSQL: Set the max value in the column +1 if the following is NULL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related