PostgreSQL中如何筛选有序表至累计长度首次超阈值的行？

你的原SQL问题在于，WHERE cum_length <= 6直接过滤掉了累计长度超过阈值的行，但需求是要包含首次触发阈值的那一行——哪怕它的累计长度超过了阈值。

正确SQL方案 #

WITH ordered_table AS (
  SELECT id,
    distance,
    length,
    SUM(length) OVER (ORDER BY distance) AS cum_length,
    -- 计算当前行之前所有行的累计长度，第一行则返回0
    COALESCE(SUM(length) OVER (ORDER BY distance ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_cum_length
  FROM your_table -- 替换为你的实际表名
)
SELECT id, distance, length, cum_length
FROM ordered_table
WHERE prev_cum_length <= 6; -- 替换为你的指定阈值

逻辑说明 #

1. 按distance排序后，计算每行的累计长度cum_length；
2. 新增prev_cum_length字段，代表当前行之前所有行的长度总和（第一行无前置行，用COALESCE设为0）；
3. 通过prev_cum_length <= 6的条件，筛选出所有前置累计未超过阈值的行：
   • 前两行的前置累计分别是0和3，均<=6，保留；
   • 第三行的前置累计是5<=6，保留（该行的累计长度7首次超过阈值，符合需求）；
   • 后续行的前置累计已超过6，自动被过滤。

另一种等价方案（通过行号筛选） #

如果需要更直观地控制筛选范围，也可以先定位首次超过阈值的行号，再取到该行的所有数据：

WITH ordered_table AS (
  SELECT id,
    distance,
    length,
    SUM(length) OVER (ORDER BY distance) AS cum_length,
    ROW_NUMBER() OVER (ORDER BY distance) AS rn
  FROM your_table
),
threshold_row AS (
  -- 找到首次超过阈值的最小行号
  SELECT MIN(rn) AS min_rn
  FROM ordered_table
  WHERE cum_length > 6
)
SELECT ot.id, ot.distance, ot.length, ot.cum_length
FROM ordered_table ot
CROSS JOIN threshold_row tr
WHERE ot.rn <= tr.min_rn;

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