Oracle SQL：按分组查询指定站点前后3行含自身的优化方案

需求：从公交站点序列数据集中，针对每个分组，提取指定站点（如XXX）的前3个站点、自身及后3个站点。原使用子查询的方案因性能问题超时，以下是高效替代方案。

示例数据表 #

期望查询结果 #

原超时SQL代码 #

select Group,Bus_Stop,Sequence from table t
where t.Sequence-3<=(select Sequence from table where Bus_Stop = XXX and Group = t.Group)
and t.Sequence+3 >=(select Sequence from table where Bus_Stop = XXX and Group = t.Group)

高效解决方案 #

方案1：CTE预获取目标站点序列值后关联 #

WITH target_stops AS (
    SELECT "Group", Sequence AS target_seq
    FROM your_table_name
    WHERE Bus_Stop = 'XXX'
)
SELECT t."Group", t.Bus_Stop, t.Sequence
FROM your_table_name t
JOIN target_stops ts ON t."Group" = ts."Group"
WHERE t.Sequence BETWEEN ts.target_seq - 3 AND ts.target_seq + 3
ORDER BY t."Group", t.Sequence;

说明：先通过CTE一次性提取每个分组中XXX站点的序列值，再通过JOIN关联原表筛选符合范围的记录。避免了原方案中每行执行两次子查询的重复计算，大幅降低性能损耗。

方案2：窗口函数直接计算目标序列值 #

SELECT "Group", Bus_Stop, Sequence
FROM (
    SELECT 
        "Group", 
        Bus_Stop, 
        Sequence,
        MAX(CASE WHEN Bus_Stop = 'XXX' THEN Sequence END) OVER (PARTITION BY "Group") AS target_seq
    FROM your_table_name
)
WHERE Sequence BETWEEN target_seq - 3 AND target_seq + 3
ORDER BY "Group", Sequence;

说明：利用窗口函数MAX() OVER (PARTITION BY "Group")在每个分组内直接获取XXX站点的序列值，仅需一次全表扫描即可完成计算，性能更优，适合大数据量表场景。

性能优化建议 #

• 创建复合索引：CREATE INDEX idx_group_seq ON your_table_name("Group", Sequence);，加速分组和序列范围筛选。
• 确保每个分组中Bus_Stop='XXX'的记录唯一（可添加唯一约束），避免窗口函数或CTE返回多个目标序列值导致结果异常。

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