您评论说最终结果将位于另一个表中;你没有发布它，所以我会假设几件事-看看它是否有帮助。
这是一个源表（您已提交）：

SQL> select * From foo;

C1                 C2
---------- ----------
A                  10
A                  11
B                  12
B                  13

这是一个目标表：

SQL> create table test (c1 varchar2(5), c2 number, batch_id number);

Table created.

查询不需要任何序列（作为Oracle对象）;CTE查找最后一个batch_id，然后添加由dense_rank分析函数生成的值：

SQL> insert into test (c1, c2, batch_id)
  2  with max_bid as
  3    (select nvl(max(batch_id), 0) max_bid from test)
  4  select f.c1,
  5    f.c2,
  6    dense_rank() over (order by f.c1) + m.max_bid as batch_id
  7  from foo f cross join max_bid m;

4 rows created.

目标表的内容：

SQL> select * from test;

C1            C2   BATCH_ID
----- ---------- ----------
A             10          1
A             11          1
B             12          2
B             13          2

SQL>

现在，由于您没有解释如何生成下一个数据集，我将截断源表并向其中插入更多的行，您可能会有一些日期列（因此您将始终使用"今天的数据"），或者......除了您之外，谁知道呢？

SQL> truncate table foo;

Table truncated.

SQL> insert all
  2    into foo values ('A', 20)
  3    into foo values ('A', 21)
  4    into foo values ('B', 22)
  5    into foo values ('B', 23)
  6  select * From dual;

4 rows created.

SQL> select * From foo;

C1                 C2
---------- ----------
A                  20
A                  21
B                  22
B                  23

SQL>

重新运行相同的INSERT语句：

SQL> insert into test (c1, c2, batch_id)
  2  with max_bid as
  3    (select nvl(max(batch_id), 0) max_bid from test)
  4  select f.c1,
  5    f.c2,
  6    dense_rank() over (order by f.c1) + m.max_bid as batch_id
  7  from foo f cross join max_bid m;

4 rows created.

这导致了

SQL> select * from test;

C1            C2   BATCH_ID
----- ---------- ----------
A             10          1
A             11          1
B             12          2
B             13          2
A             20          3
A             21          3
B             22          4
B             23          4

8 rows selected.

SQL>

batch_id会按照您的要求递增。

drop table foo;
create table foo (c1 varchar2(10), c2 int);

insert into foo values ('A', 10);
insert into foo values ('A', 11);
insert into foo values ('B', 12);
insert into foo values ('B', 13);

drop sequence foo_s;
create sequence foo_s;

DROP TABLE temp_tbl;

CREATE TABLE temp_tbl AS
SELECT
            d.distinct_c1,
            foo_s.nextval as val
    FROM
        (
            SELECT 
                DISTINCT c1 AS distinct_c1
            FROM
                foo
        
        )d;

SELECT
    foo.c1,
    foo.c2,
    t.val
 FROM
    foo
 LEFT JOIN
    temp_tbl t
ON
    t.distinct_c1=foo.c1;

重复分配每个查询：

SELECT c1, c2, DENSE_RANK() OVER (ORDER BY c1) batch_id
  FROM foot

仅当存在新的C1值时才分配新的批次1值：

MERGE INTO foo2 tgt
USING (WITH data AS (SELECT src.c1,
                             src.c2,
                             tgt.batch_id
                        FROM foo src,
                             (SELECT c1,MAX(batch_id) batch_id
                                FROM foo2
                               GROUP BY c1) tgt
                       WHERE src.c1 = tgt.c1(+))     
        SELECT *  -- remove this SELECT if you don't need to re-update existing rows for some other reason. they already have the correct batch_id
          FROM data
         WHERE batch_id IS NOT NULL
        UNION ALL  
        SELECT c1,
               c2,
               x.last_batch_id + DENSE_RANK() OVER (PARTITION BY c1 ORDER BY c1) batch_id
          FROM data,
               (SELECT MAX(batch_id) last_batch_id
                  FROM data) x
         WHERE batch_id IS NULL) src
    ON (src.c1 = tgt.c1,
        src.c2 = tgt.c2)
 WHEN MATCHED THEN UPDATE SET tgt.batch_id = src.batch_id
 WHEN NOT MATCHED THEN INSERT (c1,c2,batch_id)
                       VALUES (src.c1,src.c2,src.batch_id)

你可以将sequence.nextval分离成一个函数，并在任何地方使用这个函数。从12c开始，在select语句中声明函数是可能的。

create view test_batch
as
with function f_get_seq
return number
as
  pragma udf;
begin
  return foo_s.nextval;
end;

grp as (
  select
    c1, f_get_seq() as batch_id
  from foo
  group by c1
)
select *
from foo
  join grp
  using(c1)

select *
from test_batch

| C1|C2|批次ID|
| - ------|- ------|- ------|
| 乙|十二|第二章|
| 乙|十三|第二章|
| A类|十个|1个|
| A类|十一|1个|

select *
from test_batch

| C1|C2|批次ID|
| - ------|- ------|- ------|
| A类|十个|三个|
| A类|十一|三个|
| 乙|十二|四个|
| 乙|十三|四个|
fiddle

• • 统一采购司**。

或者，如果您希望避免对foo的双重访问，请对match_recognize执行相同的操作：

create view test_batch
as
with function f_get_seq
return number
as
  pragma udf;
begin
  return foo_s.nextval;
end;

select *
from foo
match_recognize (
  partition by c1
  measures
    final first(f_get_seq()) as batch_id
  all rows per match
  pattern (a+)
  define
    a as 1=1
)

fiddle
...或使用model：

create view test_batch
as
with function f_get_seq
return number
as
  pragma udf;
begin
  return foo_s.nextval;
end;

select *
from foo
model
  partition by (c1)
  dimension by (
    row_number() over(partition by c1 order by null) as rn
  )
  measures (
    c2, 0 as batch_id
  )
  rules update (
    batch_id[1] = f_get_seq(),
    batch_id[rn > 1] = batch_id[1]
  )

fiddle