多重叠条件下的SQL Gaps-and-Islands问题求解

我有包含多条独立人行道的匿名数据，每条人行道上有不同颜色的粉笔线条。需要找出每个颜色对应的唯一连续绘画区段的起止位置，这本质是Gaps-and-Islands问题，但我的需求是合并最长的等价重叠连续区段——现有基于row_number()分区或排序的查询，要么会把每个小重叠片段单独拆分，要么会把同颜色的所有区段合并在一起，都无法满足要求。

初始数据 #

create table #chalk (
    sidewalkId int,
    "start" int,
    "end" int,
    color varchar(50)
);
insert into #chalk values
(1, 0, 5, 'blue'),
(1, 5, 10, 'blue'),
(1, 10, 15, 'blue'),
--(1, 15, 20, null),--空值可能是显式或隐式的
(1, 20, 25, 'blue'),
--(1, 25, 30, null),--空值可能是显式或隐式的
(1, 30, 35, 'blue'),
(1, 35, 40, 'blue'),
(1, 0, 5, 'red'),
(1, 5, 10, 'red'),
(1, 10, 15, 'red'),
(1, 30, 35, 'red')

期望结果 #

错误查询1 #

with cte as (
    select sidewalkId, color
        , row_number() over (partition by sidewalkId order by sidewalkId, "start") rna
        , row_number() over (partition by sidewalkId, color order by sidewalkId, "start") rnc
        , start, "end"
    from #chalk
)
select sidewalkId, color
    , min("start") "start", max("end") "end"
    , min(rna) rna_start, max(rna) rna_end
from cte
group by sidewalkId, color, rna-rnc
order by sidewalkId, min(rna)

错误结果1 #

错误查询2 #

with cte as (
    select sidewalkId, color
        , row_number() over (partition by sidewalkId order by sidewalkId, color, "start") rna
        , row_number() over (partition by sidewalkId, color order by sidewalkId, "start") rnc
        , "start", "end"
    from #chalk
)
select sidewalkId, color
    , min("start"), max("end")
    , min(rna), max(rna)
from cte
group by sidewalkId, color, rna-rnc
order by sidewalkId, min(rna)

错误结果2 #

我尝试用rank()或dense_rank()替代row_number()，但仍然无法满足需求。

内容的提问来源于stack exchange，提问作者Jed Schaaf