Oracle中如何为连续相同num1值的分组分配相同排名

样本数据集 #

select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:00:00' as start_time, 1 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:01:00' as start_time, 1 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:05:00' as start_time, 1 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:10:00' as start_time, 2 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:15:00' as start_time, 2 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:20:00' as start_time, 2 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:25:00' as start_time, 3 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:30:00' as start_time, 3 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-03 02:00:00' as start_time, 3 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 02:00:45' as start_time, 4 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 02:15:00' as start_time, 4 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 09:20:00' as start_time, 4 as flag;

现有尝试脚本 #

select a.num0
     , a.num1
     , a.start_time
     , a.flag as expected_value
     , rank() over (partition by a.num1 order by start_time) as flag2
from test_01 as a
order by a.start_time;

需求规则 #

我创建的flag列（实际作为计数器）用于展示flag2列的期望结果，规则如下：

• 当num1列的连续值相同时，该组所有行需分配相同的flag值；
• num0列所有行的值保持一致；
• 若相同的num1值被其他值隔开，则视为全新分组，其flag值需比前一个分组大1；即使后续再次出现与之前分组相同的num1值，也需按新分组处理，flag值继续递增。

现有问题 #

使用Oracle的rank()函数无法满足需求，因为start_time列的存在，partition by num1会把所有相同num1的行归为一组，无法区分被隔开的非连续分组，导致flag值不符合预期。

内容的提问来源于stack exchange，提问作者Kiazim Khutaba