CODEWITHSUNDEEP
sqlpostgresql
Vyacheslav Fedorin
Vyacheslav Fedorin

Reputation: 235

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

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?

Upvotes: 2

Views: 1722

Answers (2)

Vyacheslav Fedorin
Vyacheslav Fedorin

Reputation: 235

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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.

Upvotes: 2

Related Questions