SQL:如何将Oracle表中的100000条记录拆分为5个块

Question

我正试图找出如何将一个拥有100多万条记录的表中的前100000条记录拆分为5（五）个20000条记录块，以放入一个文件中？也许有些SQL会为20000条记录的每5个块获取最小和最大rowid或主id，所以我可以将最小和最大值放入一个变量中，并将其传递到SQL中，并在where子句中使用BETWEEN来传递到SQL。

这能做到吗？

我使用的是Oracle 11g数据库。

提前谢谢。

Answer 1

如果只想将值1-5分配给大小基本相等的组，则使用ntile():

select t.*, ntile(5) over (order by NULL) as num
from (select t.*
      from t
      where rownum <= 100000
     ) t;

如果要插入5个不同的表，请使用insert all:

insert all
    when num = 1 then into t1
    when num = 2 then into t2
    when num = 3 then into t3
    when num = 4 then into t4
    when num = 5 then into t5
    select t.*, ntile(5) over (order by NULL) as num
    from (select t.*
          from t
          where rownum <= 100000
         ) t;

Answer 2

有点苛刻地否决了另一个公平的问题。

不管怎样，NTILE对我来说是新的，所以如果不是因为你的问题，我不会发现。

我做这件事的方法，老派的方法，是修改行号以获得组号，例如

select t.*, mod(rn,5) as num
from (select t.*, rownnum rn
      from t
     ) t;

这解决了SQL部分，或者更确切地说，如何将行分组为相等的块，但这只是问题的一半。下半部分是如何将这些内容写入5个单独的文件。

您可以有5个单独的查询，每个查询假脱机到一个单独的文件，例如：

spool f1.dat
    select t.*
    from (select t.*, rownnum rn
          from t
         ) t
    where mod(t.rn,5) = 0;
spool off
spool f2.dat
    select t.*
    from (select t.*, rownnum rn
          from t
         ) t
    where mod(t.rn,5) = 1;
spool off

等等。

或者，使用UTL_FILE。你可以用一个查询来尝试一些巧妙的方法，并拥有一个UTL_FILE类型的数组，其中数组索引与MOD（rn，5）匹配，那么你就不需要像"如果rn＝0，那么UTL_FILE.WRITELN（f0，…"这样的逻辑了。

所以，类似于（没有测试，只是以粗略的形式提供指导，我自己从未尝试过）：

DECLARE
   TYPE fname IS VARRAY(5) OF VARCHAR2(100);
   TYPE fh    IS VARRAY(5) OF UTL_FILE.FILE_TYPE;
   CURSOR c1 IS 
    select t.*, mod(rn,5) as num
    from (select t.*, rownnum rn
          from t
         ) t;
   idx INTEGER;
BEGIN
  FOR idx IN 1..5 LOOP
      fname(idx) := 'data_' || idx || '.dat';
      fh(idx) := UTL_FILE.'THE_DIR', fname(idx), 'w');
  END LOOP;
  FOR r1 IN c1 LOOP
     UTL_FILE.PUT_LINE ( fh(r1.num+1), r1.{column value from C1} );
  END LOOP;
  FOR idx IN 1..5 LOOP
      UTL_FILE.FCLOSE (fh(idx));
  END LOOP;
END;

Answer 3

非常感谢f的Gordon Lin为我提供了代码入门。

只是关于如何获得5个块的最小值和最大值的更新。

select num, min(cre_surr_id), max(cre_surr_id)
from
(select p.cre_surr_id, ntile(5) over (order by NULL) as num
from (select p.*
      from productions p
      where rownum <= 100000
 ) p )
group by num

Answer 4

您甚至可以尝试使用简单的聚合：

create table test_chunk(val) as
(
    select floor(dbms_random.value(1, level * 10)) from dual
    connect by level <= 100
)
select min(val), max(val), floor((num+1)/2)
from (select rownum as num, val from test_chunk)
group by floor((num+1)/2)