Oracle按列分区为每组行生成唯一序列值的实现方案

测试环境准备 #

首先创建测试表、插入数据并创建序列：

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);

create sequence foo_s;

需求说明 #

需要按c1列对数据分组，同一分组内的所有行使用同一个序列值。例如c1='A'的两行共用一个序列值，c1='B'的两行共用另一个序列值，期望输出如下：

c1  |  c2  | batch_id
A   |  10  |  1024
A   |  11  |  1024
B   |  12  |  1025
B   |  13  |  1025

无效尝试 #

直接在窗口函数中调用序列nextval的写法不被Oracle支持：

select c1, c2, foo_s.nextval over (partition by c1) batch_id
from foo

实现优先级 #

优先采用纯SQL查询实现，其次考虑MERGE/UPDATE语句，最后再考虑PL/SQL块。此前尝试的MERGE方案触发ORA-02287: sequence number not allowed here错误。

PostgreSQL参考方案 #

PostgreSQL中可以通过子查询生成序列值后，用窗口函数取同组第一个值实现需求：

select c1, c2, first_value(batch_id) over (partition by c1) 
from (
    select c1, c2, nextval('foo_s') batch_id from foo
) foo;

但该逻辑在Oracle中会触发ORA-02287错误，因为Oracle对序列的使用场景限制更严格。

Oracle可行方案 #

方案1：纯SQL查询（推荐） #

方法A：分组关联序列值

先获取唯一分组并生成对应序列值，再关联回原表：

WITH grouped_foo AS (
    SELECT DISTINCT c1, foo_s.nextval AS batch_id
    FROM foo
    ORDER BY c1
)
SELECT f.c1, f.c2, g.batch_id
FROM foo f
JOIN grouped_foo g ON f.c1 = g.c1
ORDER BY f.c1, f.c2;

该方法先为每个唯一的c1分组生成一个序列值，再通过关联将值填充到同组的所有行，确保同组batch_id一致。

方法B：窗口函数+条件序列调用

通过子查询标记组内行号，仅在组内第一行调用序列，再通过窗口函数将值覆盖同组所有行：

SELECT 
    c1, 
    c2, 
    (SELECT foo_s.nextval FROM dual WHERE rn = 1) OVER (PARTITION BY c1) AS batch_id
FROM (
    SELECT c1, c2, ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY c2) AS rn
    FROM foo
) t;

这种方式确保每个分组仅消耗一个序列值，同组所有行共享该值。

方案2：MERGE更新表（需新增列） #

如果需要将batch_id持久化到原表，先添加列：

ALTER TABLE foo ADD batch_id NUMBER;

再用MERGE语句完成更新：

MERGE INTO foo f
USING (
    SELECT c1, foo_s.nextval AS batch_id
    FROM (SELECT DISTINCT c1 FROM foo)
) g
ON (f.c1 = g.c1)
WHEN MATCHED THEN UPDATE SET f.batch_id = g.batch_id;

方案3：PL/SQL块（备选） #

若上述SQL方案无法满足需求，可使用PL/SQL遍历分组并赋值：

DECLARE
    v_batch_id NUMBER;
BEGIN
    FOR rec IN (SELECT DISTINCT c1 FROM foo) LOOP
        v_batch_id := foo_s.nextval;
        UPDATE foo SET batch_id = v_batch_id WHERE c1 = rec.c1;
    END LOOP;
    COMMIT;
END;
/

执行前同样需要先给foo表添加batch_id列。

内容的提问来源于stack exchange，提问作者Matthew Moisen