postgre如何在Postgres SQL中传播/分发一个数字

Question

>我有一个表格如下：

id    rank    user
1      1       1
2      1       1
3      1       2
4      2       1
5      2       1

我想将给定用户的rank值分散在 1(或 0(和最大值之间，以便在值之间留出空间。例如，我想为用户1传播rank，最大值为 100(在这种情况下(，结果应该是：

id    rank    user
1      20      1
2      40      1
3      1       2
4      60      1
5      80      1

我想计算一个increment = maxValue / count(items) + 1，然后使用每个新increment进行许多UPDATE查询，但这似乎非常低效。有没有办法做得更好，也许有一个大的UPDATE查询，比如：

UPDATE MyTable
SET rank = increment * ??? (?current position? in SELECT * FROM MyTable WHERE user = 1 ORDER BY rank)
WHERE user = 1

Answer 1

您可以使用CTE为用户的每个条目生成行号 (rn( (按rank顺序，id订购重复项(，以及该用户的行数计数 (cnt(，然后将该用户的rank设置为：

rn * 100 / (cnt + 1)

作为查询：

WITH CTE AS (
SELECT id, user, 
ROW_NUMBER() OVER (PARTITION BY user ORDER BY rank, id) AS rn,
COUNT(*) OVER (PARTITION BY user) AS cnt
FROM MyTable
WHERE "user" = 1
)
UPDATE MyTable
SET rank = CTE.rn * 100 / (CTE.cnt + 1)
FROM CTE
WHERE MyTable.id = CTE.id

结果表：

id  rank    user
1   20      1
2   40      1
3   1       2
4   60      1
5   80      1

关于SQLFiddle的演示