如何查询DP_ID=A的相邻行区间内Value列的最大值？

示例表数据 #

期望输出 #

可以通过窗口函数定义区间 + 分组取最大值的方式实现需求，以下是通用SQL方案（兼容多数主流数据库，如PostgreSQL、MySQL8+、BigQuery等）：

WITH a_intervals AS (
    -- 生成每个DP_ID=A行对应的下一个A行ID，形成区间
    SELECT 
        ID AS start_a_id,
        LEAD(ID) OVER (ORDER BY ID) AS end_a_id
    FROM your_table
    WHERE DP_ID = 'A'
)
-- 筛选出两个A之间的B行，并取每个区间内Value最大的行
SELECT t.ID, t.DP_ID, t.Value
FROM your_table t
JOIN a_intervals ai 
  ON t.ID > ai.start_a_id 
  AND t.ID < ai.end_a_id -- 只取两个A之间的行（排除A行本身）
WHERE t.DP_ID = 'B'
QUALIFY ROW_NUMBER() OVER (PARTITION BY ai.start_a_id ORDER BY t.Value DESC) = 1;

逻辑说明 #

1. a_intervals CTE：用LEAD窗口函数获取每个A行的下一个A行ID，得到所有[start_a_id, end_a_id]的区间，这些区间就是我们要分析的“两个A之间”的范围。
2. 关联筛选：将原表的B行关联到对应的区间，确保只取两个A之间的B行。
3. 取最大值行：用ROW_NUMBER()按区间分组，按Value降序排序后取第一行，就是每个区间内Value最大的B行。

兼容注意事项 #

如果你的数据库不支持QUALIFY（如MySQL 8.0以下版本），可以改用子查询实现：

WITH a_intervals AS (
    SELECT 
        ID AS start_a_id,
        LEAD(ID) OVER (ORDER BY ID) AS end_a_id
    FROM your_table
    WHERE DP_ID = 'A'
),
ranked_b AS (
    SELECT 
        t.ID, 
        t.DP_ID, 
        t.Value,
        ROW_NUMBER() OVER (PARTITION BY ai.start_a_id ORDER BY t.Value DESC) AS rn
    FROM your_table t
    JOIN a_intervals ai 
      ON t.ID > ai.start_a_id 
      AND t.ID < ai.end_a_id
    WHERE t.DP_ID = 'B'
)
SELECT ID, DP_ID, Value
FROM ranked_b
WHERE rn = 1;

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