Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;
28 rows affected

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
)


CTO
NIF
FILE_
PARTITION
SUBPARTITION
TOTAL_SIZE


1
A
C1
1
1
1

1
A
C2
1
1
2

1
A
C3
1
1
4

1
A
C4
1
1
5

1
B
C5
1
2
5

1
B
C6
1
2
6

1
C
C7
1
2
8

1
C
C8
1
2
9

1
D
C9
1
3
4

1
D
C10
1
3
9

1
D
C11
1
3
10

1
D
C12
1
4
2

1
D
C13
1
4
5

1
D
C14
1
4
9

1
D
C15
1
5
5

1
E
C16
1
6
3

1
E
C17
1
6
5

1
E
C18
1
6
8

1
E
C19
1
7
4

1
F
C20
2
1
6

1
F
C21
2
1
8

1
G
C22
3
1
10

1
G
C23
3
2
10

1
G
C24
3
3
10

1
G
C25
3
4
10

1
G
C26
3
5
10

1
G
C27
3
6
10

1
G
C28
3
7
10

CTO	NIF	FILE_	PARTITION	SUBPARTITION	TOTAL_SIZE
1	A	C1	1	1	1
1	A	C2	1	1	2
1	A	C3	1	1	4
1	A	C4	1	1	5
1	B	C5	1	2	5
1	B	C6	1	2	6
1	C	C7	1	2	8
1	C	C8	1	2	9
1	D	C9	1	3	4
1	D	C10	1	3	9
1	D	C11	1	3	10
1	D	C12	1	4	2
1	D	C13	1	4	5
1	D	C14	1	4	9
1	D	C15	1	5	5
1	E	C16	1	6	3
1	E	C17	1	6	5
1	E	C18	1	6	8
1	E	C19	1	7	4
1	F	C20	2	1	6
1	F	C21	2	1	8
1	G	C22	3	1	10
1	G	C23	3	2	10
1	G	C24	3	3	10
1	G	C25	3	4	10
1	G	C26	3	5	10
1	G	C27	3	6	10
1	G	C28	3	7	10

CTO	NIF	FILE_	PARTITION	SUBPARTITION	TOTAL_SIZE
1	A	C1	1	1	1
1	A	C2	1	1	2
1	A	C3	1	1	4
1	A	C4	1	1	5
1	B	C5	1	2	5
1	B	C6	1	2	6
1	C	C7	1	2	8
1	C	C8	1	2	9
1	D	C9	1	3	4
1	D	C10	1	3	9
1	D	C11	1	3	10
1	D	C12	1	4	2
1	D	C13	1	4	5
1	D	C14	1	4	9
1	D	C15	1	5	5
1	E	C16	1	6	3
1	E	C17	1	6	5
1	E	C18	1	6	8
1	E	C19	1	7	4
1	F	C20	2	1	6
1	F	C21	2	1	8
1	G	C22	3	1	10
1	G	C23	3	2	10
1	G	C24	3	3	10
1	G	C25	3	4	10
1	G	C26	3	5	10
1	G	C27	3	6	10
1	G	C28	3	7	10

CTO	NIF	FILE_	PARTITION	SUBPARTITION	TOTAL_SIZE
1	A	C1	1	1	1
1	A	C2	1	1	2
1	A	C3	1	1	4
1	A	C4	1	1	5
1	B	C5	1	2	5
1	B	C6	1	2	6
1	C	C7	1	2	8
1	C	C8	1	2	9
1	D	C9	1	3	4
1	D	C10	1	3	9
1	D	C11	1	3	10
1	D	C12	1	4	2
1	D	C13	1	4	5
1	D	C14	1	4	9
1	D	C15	1	5	5
1	E	C16	1	6	3
1	E	C17	1	6	5
1	E	C18	1	6	8
1	E	C19	1	7	4
1	F	C20	2	1	6
1	F	C21	2	1	8
1	G	C22	3	1	10
1	G	C23	3	2	10
1	G	C24	3	3	10
1	G	C25	3	4	10
1	G	C26	3	5	10
1	G	C27	3	6	10
1	G	C28	3	7	10