Sql 每次计数器增加时通过运行总重置进行分组

在Oracle 12中，您可以结合使用分析函数和MATCH_RECOGNIZE来执行逐行处理:

SELECT cto,
       nif,
       file_,
       partition,
       mn - first_mn + 1 AS subpartition,
       total_size
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY cto
           ORDER BY SUBSTR(file_, 1, 1), TO_NUMBER(SUBSTR(file_, 2))
         ) AS rn,
         SUM(filesize) OVER (PARTITION BY nif) AS nif_size
  FROM   my_table t
)
MATCH_RECOGNIZE(
  PARTITION BY cto
  ORDER BY rn
  MEASURES
    MATCH_NUMBER() AS mn,
    SUM(filesize) AS total_size
  ALL ROWS PER MATCH
  PATTERN ( (same_nif* nif)* same_nif* end_nif )
  DEFINE
    same_nif AS nif = NEXT(nif),
    nif      AS  (NEXT(nif) IS NULL OR nif != NEXT(nif))
             AND SUM(filesize) + NEXT(nif_size) <= 10,
    end_nif  AS  SUM(filesize) <= 10
)
MATCH_RECOGNIZE(
  PARTITION BY cto
  ORDER BY rn
  MEASURES
    MATCH_NUMBER() AS partition,
    FIRST(mn) AS first_mn
  ALL ROWS PER MATCH
  PATTERN ( (same_nif* nif)+ )
  DEFINE
    same_nif AS nif = NEXT(nif),
    nif      AS (NEXT(nif) IS NULL OR nif != NEXT(nif))
             AND mn - FIRST(mn) < 7
);

在第一次匹配中，对子分区，模式:

• same_nif* nif将从当前行匹配到当前NIF分区的末尾，并通过判断当前大小加上下一个NIF分区的总大小是否在限制内来确保下一个NIF分区也将完全包含在匹配中；
• 将其包装在()*中允许零次或多次匹配(尽可能多)；
• 最后的same_nif* end_nif允许匹配部分匹配具有相同nif值的一组行(例如文件C9、C10和C11，其具有与C12、C13和C14相同的nif)，但由于它们的文件大小总和超过限制，它们将处于不同的组中.如果没有不同的结束条件，您可能会将C1分组到C5，或者无法拆分D和nif的值.

在第二个匹配中，对于分区，nif个值需要保持在同一匹配中(而对于子分区，nif个值可以跨越多个匹配)，除此之外，唯一需要判断的是一个组中没有超过7个子分区.

对于样本数据(其具有添加的CTO列和附加的nif组G，其太大而不能与先前的nif组F组合在同一分区中):

CREATE TABLE my_table (
  CTO NUMBER,
  NIF VARCHAR2(10),
  FILE_ VARCHAR2(10),
  FILESIZE NUMBER
);

INSERT INTO my_table (CTO, NIF, FILE_, FILESIZE)
    SELECT 1, 'A', 'C1', 1 FROM DUAL UNION ALL
    SELECT 1, 'A', 'C2', 1 FROM DUAL UNION ALL
    SELECT 1, 'A', 'C3', 2 FROM DUAL UNION ALL
    SELECT 1, 'A', 'C4', 1 FROM DUAL UNION ALL
    SELECT 1, 'B', 'C5', 5 FROM DUAL UNION ALL
    SELECT 1, 'B', 'C6', 1 FROM DUAL UNION ALL
    SELECT 1, 'C', 'C7', 2 FROM DUAL UNION ALL
    SELECT 1, 'C', 'C8', 1 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C9', 4 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C10', 5 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C11', 1 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C12', 2 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C13', 3 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C14', 4 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C15', 5 FROM DUAL UNION ALL
    SELECT 1, 'E', 'C16', 3 FROM DUAL UNION ALL
    SELECT 1, 'E', 'C17', 2 FROM DUAL UNION ALL
    SELECT 1, 'E', 'C18', 3 FROM DUAL UNION ALL
    SELECT 1, 'E', 'C19', 4 FROM DUAL UNION ALL
    SELECT 1, 'F', 'C20', 6 FROM DUAL UNION ALL
    SELECT 1, 'F', 'C21', 2 FROM DUAL UNION ALL
    SELECT 1, 'G', 'C' || (LEVEL + 21), 10 FROM DUAL CONNECT BY LEVEL <= 7;

输出:

fiddle个