如何编写PostgreSQL查询，基于输入参数计算分级区间累计总和

数据表结构 #

需求描述 #

编写PostgreSQL查询语句，接收一个level类型参数（示例值170000），按照以下阶梯逻辑计算总和：

• 第一部分：(第二行level值 - 第一行level值) × 第一行value值
• 第二部分：(第三行level值 - 第二行level值) × 第二行value值
• 第三部分：(输入参数level值 - 第三行level值) × 第三行value值
  三部分结果相加即为最终总和，对应公式示例：(100000-30000)*0.05 + (120000-100000)*0.06 + (170000-120000)*0.07

查询实现 #

使用窗口函数LEAD()获取下一个阶梯的level值，结合参数计算各段金额后求和：

WITH tier_data AS (
    SELECT 
        level,
        value,
        LEAD(level) OVER (ORDER BY id) AS next_level
    FROM your_table_name -- 替换为实际表名
)
SELECT SUM(
    CASE 
        WHEN next_level IS NOT NULL THEN (next_level - level) * value
        ELSE (:input_level - level) * value
    END
) AS total_amount
FROM tier_data
WHERE level <= :input_level; -- :input_level为传入的参数，如170000

逻辑说明 #

• LEAD(level) OVER (ORDER BY id)：按id顺序获取当前行的下一个阶梯level值，最后一行无后续level，next_level为NULL
• CASE分支：有下一个level时计算当前阶梯的区间金额，无下一个level时用输入参数与当前level的差值计算最后一段金额
• 过滤条件level <= :input_level：确保只计算不超过输入参数的阶梯段

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