带条件分组的累计总额计算优化：PostgreSQL查询性能问题

绝对可以解决这个性能问题，而且你找对了方向——只是选错了窗口函数！LAG()是用来取前一行的值的，而你需要的是累计求和，这时候SUM() OVER()窗口函数才是正确的选择，它能彻底把你当前的O(n²)性能问题降到O(n log n)，再也不会出现数据量增大就锁库的情况。

原查询的性能瓶颈分析 #

你当前的cumulativeTotalsByUnit CTE里用了两个相关子查询，相当于对每一行记录都要重新扫描一遍该商品的所有历史记录求和。当数据量从6k涨到21k时，计算量会从6k²暴涨到21k²，这就是为什么性能指数级下降、甚至锁库的核心原因——数据库在做大量重复的冗余计算。

重构后的高效查询 #

直接用SUM() OVER()窗口函数替代关联子查询，一次扫描就能完成所有累计计算：

with deliveriesCTE as (
    select 
        row_number() over(partition by it.id order by dd.updated asc) as rn,
        sum(dd.quantity) as deliveryTotal,
        dd.updated as updated,
        it.id as item_id,
        d.warehouse_1 as outWH,
        d.warehouse_2 as inWH,
        d.company_code as company
    from deliveries d
    join deliveries_detail dd on dd.deliveries_id = d.id
    join items it on it.id = dd.item_id
    where ... -- 保留你的过滤条件
    group by dd.updated, it.id, d.warehouse_1, d.warehouse_2, d.company_code
    -- 移除CTE内部的order by：除非结合limit，否则CTE的order by不会生效还会浪费资源
),
cumulativeTotalsByUnit as (
    select
        item_id,
        updated,
        deliveryTotal,
        outWH,
        inWH,
        company,
        -- 按商品分组、时间顺序累计出库总量
        sum(case when outWH is not null then deliveryTotal else 0 end) 
            over(partition by item_id order by rn) as outWHTotal,
        -- 按商品分组、时间顺序累计入库总量
        sum(case when inWH is not null then deliveryTotal else 0 end) 
            over(partition by item_id order by rn) as inWHTotal,
        -- 标记每个商品的最新记录（按更新时间降序）
        row_number() over(partition by item_id order by updated desc) as latest_rn
    from deliveriesCTE
)
select 
    ct.item_id,
    (ct.inWHTotal - ct.outWHTotal) as quantity,
    p.price * (ct.inWHTotal - ct.outWHTotal) as price
from cumulativeTotalsByUnit ct
join prices p on ct.item_id = p.item_id
where ct.latest_rn = 1; -- 只取每个商品的最新累计数据

重构的核心优势 #

1. 性能量级提升：数据库只需扫描deliveriesCTE一次就能计算出所有商品的累计值，时间复杂度从O(n²)降到O(n log n)，数据量越大，性能提升越明显。
2. 逻辑更清晰：用row_number()标记最新记录替代原查询的distinct on，避免了排序冲突的风险，逻辑更直观。
3. 减少冗余操作：移除了CTE内部无用的order by，减少不必要的排序开销。

关于LAG()函数的疑问解答 #

你之前尝试用LAG()是走偏了方向：LAG()的作用是获取当前行的前N行的值，比如LAG(deliveryTotal)只能拿到上一条记录的配送量，要计算累计总和的话，你需要手动累加所有历史值，这反而需要递归或自定义变量，比直接用SUM() OVER()麻烦得多，性能也不会更好。所以累计求和场景下，SUM窗口函数才是最优解。

额外性能优化建议 #

为了进一步提升大数据量下的查询速度，建议给以下字段添加索引：

• deliveries_detail(deliveries_id, item_id)：加速关联和分组操作
• deliveries_detail(updated)：加速窗口函数的排序步骤
• prices(item_id)：加速最后的商品价格关联查询

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