如何在保持相同数据集的同时打乱列值

Question

我想从Postgres数据库的表中打乱一些列。我有200万排。我需要通过另一个更新所有非空值。

我需要保持相同的数据集。两次使用相同的值是不可能的。不可能用下一个值交换数据，因为如果我对另一列执行相同的过程，我将保持相同的链接。这是为了匿名化我的数据库。只需要打乱数据并保留数据集。

示例(更改名字和姓氏(：

einsten居里格雷厄姆·贝尔爱迪生

id	姓氏
1	阿尔伯特
2	isaac	牛顿
3
4	亚历山大
5	托马斯

这以完全随机的方式搅乱列firstname中的值：

UPDATE test t0
SET    firstname = t2.firstname
FROM  (SELECT row_number() OVER (ORDER BY random()) AS rn, id        FROM test WHERE firstname IS NOT NULL) t1
JOIN  (SELECT row_number() OVER (ORDER BY random()) AS rn, firstname FROM test WHERE firstname IS NOT NULL) t2 USING (rn)
WHERE  t0.id = t1.id
AND    t0.firstname IS NOT NULL;

"完全随机的"；包括某些列可能保留其原始值的可能性。(行越多，机会就越小。(这实际上最适合匿名化数据。那么这些值就是真正的随机值。如果我们强制切换，读者将获得与给定ID关联的不同值的最小信息。

它还观察到您的惊人规则，即只搅乱而不是null值。

对需要洗牌的每一列重复此操作。

在不排除NULL值的情况下，此单个查询对于任何列数都更便宜：

UPDATE test t0
SET    firstname = t2.firstname
, lastname  = t3.lastname
FROM  (SELECT row_number() OVER (ORDER BY random()) AS rn, id        FROM test) t1
JOIN  (SELECT row_number() OVER (ORDER BY random()) AS rn, firstname FROM test) t2 USING (rn)
JOIN  (SELECT row_number() OVER (ORDER BY random()) AS rn, lastname  FROM test) t3 USING (rn)
WHERE  t0.id = t1.id;

如果id是一个无间隙序列，我们可以从方程中去除t1，并将t0.id与t2.rn连接起来。(排除NULL值时不起作用。(

小提琴

考虑到更新后的需求，您最好使用像Erwin Brandstetter的解决方案这样的策略，它可以很容易地应用于任意数量的列，但我将在完成第二列时更新原始答案。

原始答案(要求混洗一列(：

考虑到你对洗牌顺序的要求有多普遍，我不确定这在实际中会有多大帮助，但我认为它回答了你的问题：

update test
SET firstname = t2.firstname
FROM
(
SELECT id, COALESCE(LAG(firstname, 1) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING), LAST_VALUE(firstname) OVER (ORDER BY id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) firstname
FROM test t2
WHERE firstname IS NOT NULL
) t2
WHERE test.id = t2.id

这里的想法是内部查询将值偏移一(忽略null(。使用COALESCE是因为第一个没有继续的条目，所以它依靠某些LAST_VALUE逻辑来获得最后一个值(即，它的行为就像移位循环一样(。

周围的UPDATE语句将测试连接到子查询以实际更新数据。

你可以看到它在这个Fiddle中工作。

已更新(还需要重新排列第二列(：

考虑到对第二个字段进行混洗的更新要求，您可以在那里应用不同的逻辑：

update test
SET firstname = t2.firstname,
lastname = t2.lastname
FROM
(
SELECT id, 
COALESCE(LAG(firstname, 1) OVER (ORDER BY id), LAST_VALUE(firstname) OVER (ORDER BY id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) firstname,
COALESCE(LEAD(lastname, 1) OVER (ORDER BY id), FIRST_VALUE(lastname) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING)) lastname
FROM test t2
WHERE firstname IS NOT NULL AND lastname IS NOT NULL
) t2
WHERE test.id = t2.id

这只是在相反的方向上搅乱lastname，因此firstname从前一个非空行(环绕(中被抓取，lastname从下一个非零行中被抓取(环绕(。两列都将更改。

这是一个正在工作的