问题背景:
我组承接中心入湖任务,现剩余近400张表,尚未入湖,现在要按月拆分,与今年12月入湖完毕.
现在要排9,10,11,12月的计划,假设按表排序后,两表间大小差距不大,要平均将工作排期到四个月份中,并保证存量数据能跑完.
表数量对应工作量
表大小对应存量数据文件生成时间.
问题描述:
上面的问题可以抽象为:
有数列1,2,3,4,5,6,7,8,9,10,…30
现在想拆分到四个队列中,要求数列长度接近,并且数列的和也最接近.
SQL 实现
SQL> with t as
2 (select rownum c1 from dual connect by level <= 30)
3 select c1 ,case when c4 = 1 then row_number() over( partition by c3 order by c1 asc )
4 else row_number() over( partition by c3 order by c1 desc )
5 end
6 from (select c1,
7 trunc(c1 + max_c1 - 1) c2,
8 trunc((c1 + max_c1 - 1) / 4) c3,
9 mod(trunc((c1 + max_c1 - 1) / 4), 2) c4
10 from t, (select mod(max(c1), 4) max_c1 from t)
11 where c1 > max_c1)
12 union all
13 select c1 , row_number() over( order by c1 desc )
14 from t
15 where c1 <=(select mod(max(c1), 4) max_c1 from t)
16 order by 2,1
17 ;
C1 CASEWHENC4=1THENROW_NUMBER()OV
---------- ------------------------------
2 1
3 1
10 1
11 1
18 1
19 1
26 1
27 1
1 2
4 2
9 2
12 2
17 2
20 2
25 2
28 2
5 3
8 3
13 3
16 3
21 3
24 3
29 3
6 4
7 4
14 4
15 4
22 4
23 4
30 4
30 rows selected