邯郸
乾学院 14 号会员
1,027 浏览  • 
     ...

打乱数据库表内的数据顺序

 
sql(122) 排序(10)

【问题】

What is a best way to shuffle a database column using pl/sql. how to shuffle a database column eg first name with around 70000 of rows . TIA

针对此问题，有人给了下面的方案：

DECLARE  
SUBTYPE Name IS VARCHAR2(200);  
TYPE Names_tab IS TABLE OF Name;  
--  
tabOrigin Names_tab;  
tabShuffled Names_tab;  
vSwap Name;  
idx BINARY_INTEGER;  
BEGIN  
\-\- Do simple play WITH DUAL instead of real data  
WITH rs AS (  
SELECT DBMS_RANDOM.STRING( 'A', 1) name FROM dual  
UNION ALL  
SELECT DBMS_RANDOM.STRING( 'A', 1) name FROM dual  
UNION ALL  
SELECT DBMS_RANDOM.STRING( 'A', 1) name FROM dual  
UNION ALL  
SELECT DBMS_RANDOM.STRING( 'A', 1) name FROM dual  
UNION ALL  
SELECT DBMS_RANDOM.STRING( 'A', 1) name FROM dual  
UNION ALL  
SELECT DBMS_RANDOM.STRING( 'A', 1) name FROM dual  
)  
SELECT name origin, name shuffle  
BULK COLLECT INTO tabOrigin, tabShuffled  
FROM rs  
;  
--  
\-\- As mentioned in 'Operational Notes' for package DBMS_RANDOM

\-\- invocation of DBMS_RANDOM.SEED is necessary only for obtaining  
\-\- repeatable sequence of values, therefore omit this call

--  
FOR i IN 1 .. tabOrigin.COUNT LOOP  
idx := DBMS_RANDOM.VALUE( 1, tabOrigin.COUNT);  
vSwap := tabShuffled(i);  
tabShuffled(i) := tabShuffled(idx);  
tabShuffled(idx) := vSwap;  
END LOOP;  
\-\- Lookup result after shuffling  
FOR i IN 1 .. tabOrigin.COUNT LOOP  
dbms\_output.put\_line(  
'Origin: ' || tabOrigin(i) || ' Shuffled to: ' || tabShuffled(i)  
);  
END LOOP;  
END;  
/

Output for this script is:

Origin: D Shuffled to: N
Origin: U Shuffled to: n
Origin: j Shuffled to: K
Origin: N Shuffled to: D
Origin: K Shuffled to: j
Origin: n Shuffled to: U

【回答】

上面给出的方案中，SQL 可以生成打乱后的列，但如果想把数据写回 SHUFFLED_VALUE 字段，则还要经过进一步处理，整个过程很麻烦。 但是用 SPL 来实现这个功能就很容易了，而且代码简洁明了：

A

1

=connect("myDB1").query("select   id,ORIGINAL_VALUE from REF_VALUES")

2

=A1.sort(rand())

3

=join@p(A1.(ID);A2.(ORIGINAL_VALUE))

4

=connect("myDB1").update@u(A3,REF_VALUES,ID:_1,SHUFFLED_VALUE:_2   ;ID)


A1：执行 SQL 取数

A2：对序列成员进行随机排序

A3：将 A1 中的 ID 列与 A2 中的 ORIGINAL_VALUE 列按位置连接起来，返回新序表

A4：根据键 ID，将 A3 中的列更新到数据库表 REF_VALUES 中

函数详细解释可参加文档《函数参考》

sql(122) 排序(10)