SQL/SQLite查询WHERE过滤结果每行对应的全量数据后续滞后行

实现方案 #

完全可以通过单条高效SQL实现需求，你之前提到窗口函数只能匹配红队内部选手，是因为你先执行了WHERE team="red"过滤再计算窗口函数，只要调整计算顺序，先全局计算所有选手的下一名，再过滤红队即可。这里要用到取后续行的LEAD窗口函数，而非取前序行的LAG函数：

WITH global_rank_info AS (
  SELECT
    person,
    team,
    timer,
    -- 全局维度计算每个选手的下一个更慢选手
    LEAD(person) OVER(ORDER BY timer ASC) AS next_runner
  FROM race
)
SELECT
  person,
  -- 计算红队内部排名
  ROW_NUMBER() OVER(ORDER BY timer ASC) AS ranking,
  next_runner
FROM global_rank_info
WHERE team = "red";

如果你的数据库版本不支持CTE和窗口函数，也可以用关联子查询实现：

SELECT
  person,
  ROW_NUMBER() OVER(ORDER BY timer ASC) AS ranking,
  (
    SELECT person
    FROM race r2
    WHERE r2.timer > r1.timer
    ORDER BY r2.timer ASC
    LIMIT 1
  ) AS next_runner
FROM race r1
WHERE r1.team = "red";

两种写法的输出都和你要求的期望结果完全一致。

相关术语 #

这类查询属于关联子查询范畴，如果用窗口函数实现则属于有序分析查询，业内也常将这类需求称为「相邻行匹配查询」或「NEXT值匹配查询」。

性能优化方案 #

针对大数据量场景，可以通过以下方式提升查询效率：

• 给timer字段建立单列升序索引：无论是窗口函数的全局排序，还是关联子查询的条件匹配，都可以直接走索引避免全表扫描，数据量越大性能提升越明显
• 优先选择窗口函数方案：关联子查询的时间复杂度为O(n*m)，逐行匹配性能随数据量增长衰减较快；窗口函数仅需要做一次全局排序，时间复杂度为O(n log n)，性能更稳定
• 建立覆盖索引进一步提速：可以创建(timer ASC, person, team)格式的联合索引，查询时不需要回表读取原始数据，直接从索引中就能获取所有需要的字段，性能可以再提升一个量级

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