PostgreSQL:如果下面为NULL，则设置+1列的最大值

Question

我是一个SQL新手，我知道下面的问题很受欢迎，但任何提出的解决方案都对我没有帮助。因此，我有一个表格

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

现在我想设置'3'，'4'等，而不是NULL与ratingId = NULL的每一行，这意味着最后一个非NULL值+ 1的最大值

我用了很多方法，但最流行的是max()

当前代码是

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

，但它不工作:(我仍然有NULL值。有什么建议吗?

Answer 1

这是你想要的吗?

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

对应的短语是:

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

这些为NULL所在的行提供唯一的ratingId，并在之前的最大值上增加值。

Answer 2

我急于回答。NULL替换为64，但应该从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

这是一个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