以与现有ID字段相同的模式创建随机值

以下查询:

SELECT id, var1, var2, var3 FROM my_table

返回此结果:

+----------+------+------+------+
| id       | var1 | var2 | var3 |
+----------+------+------+------+
| 43245324 | A    | 1    | 2    |
+----------+------+------+------+
| 43245324 | B    | 2    | 3    |
+----------+------+------+------+
| 11112233 | A    | 2    | 2    |
+----------+------+------+------+
| 11112233 | A    | 2    | 2    |
+----------+------+------+------+
| 11112233 | C    | 3    | 3    |
+----------+------+------+------+
| 23312222 | H    | 5    | 1    |
+----------+------+------+------+
| 23312222 | Q    | 3    | 3    |
+----------+------+------+------+

我需要一个新的ID字段,该字段可以是随机的,但需要与现有ID保持一致。例如,43254324的每个实例都需要具有相同的对应newID。我将如何调整查询以返回类似以下内容的结果:

+----------+-------+------+------+------+
| id       | newid | var1 | var2 | var3 |
+----------+-------+------+------+------+
| 43245324 | 1     | A    | 1    | 2    |
+----------+-------+------+------+------+
| 43245324 | 1     | B    | 2    | 3    |
+----------+-------+------+------+------+
| 11112233 | 2     | A    | 2    | 2    |
+----------+-------+------+------+------+
| 11112233 | 2     | A    | 2    | 2    |
+----------+-------+------+------+------+
| 11112233 | 2     | C    | 3    | 3    |
+----------+-------+------+------+------+
| 23312222 | 3     | H    | 5    | 1    |
+----------+-------+------+------+------+
| 23312222 | 3     | Q    | 3    | 3    |
+----------+-------+------+------+------+

谢谢, 马特

评论
  • 刺青
    刺青 回复

    Simply use dense_rank():

    select t.*, dense_rank() over (order by id) as newid
    from mytable t;
    

    This will assign a value starting at 1 with no gaps based on the ordering of id.

    By the way, if you wanted a "random" value, you could use rand() with a seed:

    select t.*, dense_rank() over (order by rand(id), id) as newid
    from mytable t;