Oracle 如何根据块大小将行分组到存储桶中

嗨，奇尔斯/康纳，

生活质量:https://livesql.oracle.com/apex/livesql/s/flfiwlt4m3yqb6v2tync6gr4o

我有一个表测试，从中我想获得以下详细信息:
获取文件大小 <= 2gb的块的编号
例如id = 1,2，这三行文件的3个总和为 <= 2gb，因此它将被视为一个块
同样id = 4,5形成块
id = 6,7，8,9形成一个块
块的id = 10

因此，对于给定的created_date = TO_DATE (“09/21/2017”，“mm/DD/yyyy”)，我们有4个文件大小总和 <= 2gb。

所以我想要一个sql，它的结果如下:

你能在这个SQL中帮忙吗

id  created_date     no_of_chunks   Chunk_Id
--  ---------------  -------------  ---------
1    21-SEP-17       4              1 
2    21-SEP-17       4              1
3    21-SEP-17       4              1
4    21-SEP-17       4              2
5    21-SEP-17       4              2
6    21-SEP-17       4              3
7    21-SEP-17       4              3
8    21-SEP-17       4              3
9    21-SEP-17       4              3
10   21-SEP-17       4              4

或者

no_of_chunks  id  Chunk_Id   
-----------   --  ---------  
4             1   1          
              2   1
              3   1
              4   2
              5   2
              6   3
              7   3
              8   3
              9   3
              10  4

您可以在以下位置找到讨论的类似问题:

https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-split-rows-into-balanced-sets-based-on-a-running-total-limited-to-2000

IMO最佳解决方案使用匹配 _ 识别...需要12c。

如果你在11.2，你可以使用递归:

with data
  as (select sum(file_size) over(order by to_number(substr(file_name, 6))) as running_total,
             to_number(substr(file_name, 6)) ord,
             a.*
       from  test a
      )
    ,row_generator as
     (select level as lvl
              ,(level-1)*2048 +1 as lo_lvl
              ,level*2048 as hi_lvl
          from dual
        connect by level<=(select ceil(max(data.running_total)/2048)
                             from data
                           )     
     )
select a1.id
      ,a1.created_date
      ,a1.file_size
      ,b1.lvl as chunk_id
      ,sum(a1.file_size) over(order by a1.ord) as running_tot
  from data a1
  join row_generator b1
     on a1.running_total>=b1.lo_lvl
    and a1.running_total<=b1.hi_lvl
order by 1,2  ;

ID  CREATED_DATE          FILE_SIZE  CHUNK_ID  RUNNING_TOT  
1   21-SEP-2017 00:00:00  1024       1         1024         
2   21-SEP-2017 00:00:00  512        1         1536         
3   21-SEP-2017 00:00:00  512        1         2048         
4   21-SEP-2017 00:00:00  1024       2         3072         
5   21-SEP-2017 00:00:00  1024       2         4096         
6   21-SEP-2017 00:00:00  512        3         4608         
7   21-SEP-2017 00:00:00  512        3         5120         
8   21-SEP-2017 00:00:00  512        3         5632         
9   21-SEP-2017 00:00:00  512        3         6144         
10  21-SEP-2017 00:00:00  2048       4         8192  

否则型号:

select id, created_date, file_size, fs_tot, chunk_id
from   test
model
  dimension by (row_number() over(order by to_number(substr(file_name, 6))) rn)
  measures (id, created_date, file_size, 0 fs_tot, 1 chunk_id)
  rules(
    fs_tot[any] = file_size[cv()] +
      case when fs_tot[cv()-1] + file_size[cv()] <= 2048
           then fs_tot[cv()-1] else 0 end,
    chunk_id[rn>1] = chunk_id[cv()-1] +
      case when fs_tot[cv()-1] + file_size[cv()] <= 2048
           then 0 else 1 end
  );

ID  CREATED_DATE          FILE_SIZE  FS_TOT  CHUNK_ID  
1   21-SEP-2017 00:00:00  1024       1024    1         
2   21-SEP-2017 00:00:00  512        1536    1         
3   21-SEP-2017 00:00:00  512        2048    1         
4   21-SEP-2017 00:00:00  1024       1024    2         
5   21-SEP-2017 00:00:00  1024       2048    2         
6   21-SEP-2017 00:00:00  512        512     3         
7   21-SEP-2017 00:00:00  512        1024    3         
8   21-SEP-2017 00:00:00  512        1536    3         
9   21-SEP-2017 00:00:00  512        2048    3         
10  21-SEP-2017 00:00:00  2048       2048    4 

为这些解决方案向杜克·加诺特和斯图·阿什顿提供服务。