PostgreSQL中按ID分组汇总发票周期内金额的实现方案

需求说明 #

现有按ID记录每月金额的表，部分月份标记为发票月（Invoiced=1）。需要仅保留各ID的发票月行，并将该行的金额替换为自上次发票以来（含当前发票月）所有月份的金额总和。

原SQL问题分析 #

你之前尝试的SQL错误在于按Invoiced分区，这会将所有Invoiced=1的行归为一组、Invoiced=0的行归为另一组，完全没有按ID和发票周期划分，无法得到正确的周期汇总结果。

正确实现方案 #

通过窗口函数标记发票周期，再按周期汇总金额：

WITH grouped_data AS (
    SELECT 
        "ID",
        "Date",
        "Invoiced",
        "Amount",
        -- 为每个ID的行标记所属发票周期：每遇到发票月，周期号递增
        SUM(CASE WHEN "Invoiced" = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY "ID" 
            ORDER BY "Date" 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS invoice_group
    FROM your_table -- 替换为你的实际表名
)
SELECT 
    "ID",
    "Date",
    "Invoiced",
    SUM("Amount") OVER (PARTITION BY "ID", invoice_group) AS total_amount
FROM grouped_data
WHERE "Invoiced" = 1
ORDER BY "ID", "Date";

逻辑说明 #

1. 标记发票周期：在CTEgrouped_data中，对每个ID按日期排序，每遇到Invoiced=1的行，就为当前行及后续行（直到下一个发票月）分配一个递增的周期号。这样同一个发票周期内的所有行（从上次发票后到当前发票月）会共享同一个周期号。
2. 汇总周期金额：主查询中按ID和周期号汇总金额，同时仅筛选出发票月的行，最终得到每个发票月对应的周期总金额。

示例验证 #

假设原表数据：

执行SQL后得到结果：

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