sql - PostgreSQL 洗牌列值

Question

在一个超过 100k 行的表中，我怎样才能有效地随机排列特定列的值？

表定义:

CREATE TABLE person
(
  id integer NOT NULL,
  first_name character varying,
  last_name character varying,
 CONSTRAINT person_pkey PRIMARY KEY (id)
)

为了匿名化数据，我必须就地打乱“first_name”列的值(不允许创建新表)。

我的尝试:

with
first_names as (
select row_number() over (order by random()),
       first_name as new_first_name
from person
),
ids as (
select row_number() over (order by random()), 
       id as ref_id
from person
)
update person
set first_name = new_first_name
from first_names, ids
where id = ref_id;

完成需要几个小时。

有什么有效的方法吗？

Answer 1

这个在我的笔记本电脑上洗牌 500.000 行需要 5 秒:

with names as (
  select id, first_name, last_name,
         lead(first_name) over w as first_1,
         lag(first_name) over w as first_2
  from person
  window w as (order by random())
)
update person
  set first_name = coalesce(first_1, first_2)
from names 
where person.id = names.id;

想法是在随机排序数据后选择“下一个”名称。这和随机取一个名字一样好。

有可能不是所有的名字都被洗牌了，但是如果你运行它 2 到 3 次，这应该就足够了。

这是 SQLFiddle 上的测试设置:http://sqlfiddle.com/#!15/15713/1

右侧的查询检查“随机化”后是否有任何名字保持不变