SQL：您如何将 Oracle 表中的 100,000 条记录拆分为 5 个块？

Question

我试图弄清楚将前 100,000 条记录从具有 100 万条以上记录的表中拆分为 5（五）20,000 条记录块以放入文件中？也许某些 SQL 会为每 5 块 20,000 条记录获取最小和最大 rowid 或主 id，所以我可以将最小和最大值放入一个变量中并将其传递给 SQL 并在 where 子句中使用 BETWEEN 到SQL。

这可以做到吗？

我在 Oracle 11g 数据库上。

提前致谢。

原文由 Shaun Kinnair 发布，翻译遵循 CC BY-SA 4.0 许可协议

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;

原文由 Gordon Linoff 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

对另一个公平的问题投票有点苛刻。

无论如何，NTILE 对我来说是新的，所以如果不是你的问题，我不会发现它。

我这样做的方式，老派的方式，本来是 MOD the rownum 来获取组号，例如

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) 匹配，那么您就不需要像“IF rn = 0 THEN 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;

原文由 TenG 发布，翻译遵循 CC BY-SA 3.0 许可协议