如何修改PostgreSQL查询以返回目标值以上最小累积和对应行

数据老陈

2026-7-28

修改PostgreSQL查询以返回累积和首次超过目标值的所有行

需求说明

需要调整现有查询逻辑，让它返回累积和首次超过target_sum的所有行，具体示例：

当target_sum为5时，仅返回id=1的行
当target_sum为8时，返回id=1和id=2的行
当target_sum为13时，返回id=1、2、3的行

数据表结构

id	marks
1	5
2	7
3	6
4	9

现有问题

原查询只筛选累积和≤target_sum的行，无法包含首次超过目标值的那一行，不符合需求。

修改后的查询方案

这里提供两种简洁的实现方式：

方案一：标记首超行并筛选

WITH cumulative_data AS (
    SELECT 
        *,
        SUM(marks) OVER (ORDER BY id) AS cum_sum,
        -- 标记当前行是否超过目标值
        CASE WHEN SUM(marks) OVER (ORDER BY id) > target_sum THEN 1 ELSE 0 END AS is_over,
        -- 获取第一个超过目标值的累积和
        FIRST_VALUE(CASE WHEN SUM(marks) OVER (ORDER BY id) > target_sum THEN SUM(marks) OVER (ORDER BY id) END) OVER (ORDER BY id) AS first_over_sum
    FROM students
)
SELECT id, marks, cum_sum
FROM cumulative_data
WHERE 
    cum_sum <= target_sum 
    OR (is_over = 1 AND cum_sum = first_over_sum)
ORDER BY id;

方案二：通过前后行累积和对比

WITH cumulative_data AS (
    SELECT 
        *,
        SUM(marks) OVER (ORDER BY id) AS cum_sum,
        -- 获取前一行的累积和（首行则为0）
        COALESCE(SUM(marks) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_cum_sum
    FROM students
)
SELECT id, marks, cum_sum
FROM cumulative_data
WHERE 
    cum_sum <= target_sum 
    OR (prev_cum_sum <= target_sum AND cum_sum > target_sum)
ORDER BY id;