基于值的总和生成数据组

如何计算总和和总文件.

这是我的桌子

**FileId**          **FileSize(MB)**
    1                      5
    2                      4
    3                      1
    4                      6
    5                      8
    6                      1
    7                      7
    8                      2

预期结果

BatchNo         StartId          EndId        BatchSize
   1               1               3             10
   2               4               4              6
   3               5               6              9
   4               7               8              9  

如果文件大小> = 10,则启动新批次

每批文件计数> = 10然后开始新批次

StartId和EndId基于FileId

和BatchNo是AutoIncrement

您可以使用这样的递归查询

with rdata as
(
      select row_number() over (order by fileId) rn, * from data
), rcte as
(
  select 1 no, 1 gr, fileSize fileSizeSum , * 
  from rdata where fileid = 1
  union all
  select case when fileSizeSum + d.fileSize > 10 or r.no = 10 then 1 else r.no + 1 end gr,
         case when fileSizeSum + d.fileSize > 10 or r.no = 10 then r.gr + 1 else r.gr end gr,
         case when fileSizeSum + d.fileSize > 10 or r.no = 10 then d.fileSize else d.fileSize + fileSizeSum  end fileSizeSum, 
         d.*
  from rcte r
  join rdata d on r.rn + 1 = d.rn
)
select r.gr, 
       min(fileId), 
       max(fileId), 
       max(fileSizeSum)
from rcte r
group by r.gr

dbfiddle