如何在SQL的CTE中高效计算当前分块的chunk_start_value？

问题描述（翻译） #

我有一个CTE，结构与以下示例表一致：

create table example (
rn int, -- 生成的行号
id int,
start_value int,
end_value int,
starts_chunk int -- 1表示新分块开始，0表示分块延续
);

我希望新增一列chunk_start_value，存储当前分块对应的start_value。

尝试过嵌套子查询，但40万行数据下性能极差：

select *
, (select start_value
   from example em
   where rn = (
     select max(rn)
     from example ei
     where rn <= eo.rn
     and starts_chunk = 1
   )
  ) chunk_start_value
from example eo

无法使用自引用窗口函数，也不能物化临时表，求可行方案。

可行实现方案 #

可以通过累计求和生成分块ID + 窗口函数取分组起始值的方式实现，性能高效且无需临时表。

核心逻辑 #

1. 利用sum(starts_chunk) over (order by rn)生成每个行的分块ID：每当遇到starts_chunk=1的行，累计值递增，同一分块内的行共享相同ID。
2. 基于分块ID，用first_value(start_value)窗口函数提取当前分块的起始start_value。

完整SQL代码 #

select 
    rn,
    id,
    start_value,
    end_value,
    starts_chunk,
    first_value(start_value) over (
        partition by chunk_id 
        order by rn 
        rows between unbounded preceding and unbounded following
    ) as chunk_start_value
from (
    select 
        *,
        sum(starts_chunk) over (order by rn) as chunk_id
    from example
) t

细节说明 #

• 内层查询的sum(starts_chunk) over (order by rn)是关键：它会按行号顺序累计starts_chunk的值，天然完成分块标记，时间复杂度O(n)。
• 外层的first_value窗口函数在每个分块内取第一行的start_value，rows between unbounded preceding and unbounded following确保无论排序规则如何，都能正确获取分组内的起始值（部分数据库默认窗口范围可能仅包含当前行之前的数据，加上这个子句更稳妥）。
• 该方案仅需两次线性扫描数据，远优于嵌套子查询的O(n²)复杂度，40万行数据下性能会有显著提升。

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