在行之间随机排列一列 [英] Shuffle a column between rows

问题描述

如何有效地洗排大型(1m至5m记录)表的内容?已知该列具有唯一值，但是您可以假定为此删除了所有约束.我之所以头痛，主要是因为我正在更新选择的同一列.我的目标是使用PL/SQL做到这一点，以便我可以通过编程方式采取其他措施，例如记录或更新其他表.

How can I shuffle the contents of a large (1m to 5m record) table efficiently? The column is known to have unique values, but you can assume that all constraints are removed for the purposes of this. My headaches are primarily because I am updating the same column I am selecting from. My goal is to do this with PL/SQL so that I can take additional action programmatically such as logging or updating other tables.

**Original table:**
+----+-----------+
| id | fname     |
+----+-----------+
|  1 | mike      |
|  2 | ricky     |
|  3 | jane      |
|  4 | august    |
|  6 | dave      |
|  9 | Jérôme    |
+----+-----------+

**Possible output:**
+----+-----------+
| id | fname     |
+----+-----------+
|  1 | dave      |
|  2 | jane      |
|  3 | mike      |
|  4 | ricky     |
|  6 | Jérôme    |
|  9 | august    |
+----+-----------+

我最近的尝试是创建一个使用over (order by dbms_random.value)的游标，并尝试基于rownum进行合并或更新.也许我可以通过创建一个临时表来解决修改自我约束的问题?我相当有信心Oracle可以采用一些特殊的方法来做到这一点，但是我的SQL能力仅限于基本的CRUD命令.

My latest attempts have been to create a cursor that uses over (order by dbms_random.value) and to try to do a merge or update perhaps based on rownum. Perhaps I can get around the modifying self constraint by creating a temp table of sorts? I'm fairly confident Oracle has some fancy way to do this but I am limited in my SQL abilities to the basic CRUD commands.

基于戈登的答案，完整的解决方案在这里:

The full solution is here, based on Gordon's answer:

merge into t
using (
select t.id, t2.name
from (select t.*, rownum as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum
) src
on (t.id = src.id)
when matched then update set t.name = src.name;

推荐答案

您可以使用随机行号进行自我连接:

You can do a self join, using random row numbers:

select t.id, t2.name
from (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum;

实际上，您不需要将两者都随机化:

Actually, you don't need for both to be randomized:

select t.id, t2.name
from (select t.*, rownum as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum;

这篇关于在行之间随机排列一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！