PostgreSQL 9.3中对stock_rotation表按行不同条件求和

嘿，针对你在PostgreSQL 9.3里处理stock_rotation表的需求，我来给你梳理下可行的解决思路——核心就是按时间顺序计算每行对应的实时库存，同时要考虑不同操作类型的特殊逻辑（比如盘点记录是直接设定当前库存，不是累加变化量）。

场景1：没有盘点记录的简单累计 #

如果你的表中只有PURCHASE（采购）和SALE（销售）类型的记录，直接用窗口函数就能轻松计算累计库存：

SELECT 
    id,
    quantity_change,
    stock_rotation_type,
    article_id,
    date,
    -- 按商品分区，按时间+ID排序，累加从开头到当前行的数量变化
    SUM(quantity_change) OVER (
        PARTITION BY article_id 
        ORDER BY date, id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS current_stock
FROM stock_rotation
ORDER BY article_id, date, id;

代码说明： #

• PARTITION BY article_id：确保每个商品的库存单独计算，不会和其他商品混淆
• ORDER BY date, id：用id作为日期的补充排序键，避免同一日期多条记录的顺序混乱
• 窗口函数的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从分区的第一行到当前行的范围求和

场景2：包含盘点记录的复杂累计 #

如果存在INVENTORY（盘点）类型的记录，这时候要注意：盘点记录的quantity_change是当前实际库存，不是增量，所以需要以盘点记录为起点重置累计逻辑。这里用CTE（公共表表达式）来实现：

WITH inventory_markers AS (
    SELECT 
        id,
        article_id,
        -- 给每个行标记所属的"盘点组"：每遇到一条盘点记录，组号+1
        SUM(CASE WHEN stock_rotation_type = 'INVENTORY' THEN 1 ELSE 0 END) OVER (
            PARTITION BY article_id 
            ORDER BY date, id 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS inventory_group
    FROM stock_rotation
),
inventory_groups AS (
    -- 获取每个盘点组的初始库存：盘点记录的数量，无盘点的组初始为0
    SELECT 
        sr.article_id,
        im.inventory_group,
        sr.quantity_change AS initial_stock
    FROM stock_rotation sr
    JOIN inventory_markers im ON sr.id = im.id
    WHERE sr.stock_rotation_type = 'INVENTORY'
    UNION ALL
    SELECT 
        DISTINCT article_id,
        0 AS inventory_group,
        0 AS initial_stock
    FROM stock_rotation
    WHERE article_id NOT IN (SELECT article_id FROM stock_rotation WHERE stock_rotation_type = 'INVENTORY')
)
SELECT 
    sr.id,
    sr.quantity_change,
    sr.stock_rotation_type,
    sr.article_id,
    sr.date,
    -- 计算实时库存：盘点行直接取自身数量，其他行从组初始库存累加后续变化
    CASE 
        WHEN sr.stock_rotation_type = 'INVENTORY' THEN sr.quantity_change
        ELSE ig.initial_stock + SUM(sr.quantity_change) OVER (
            PARTITION BY sr.article_id, im.inventory_group 
            ORDER BY sr.date, sr.id 
            ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
        )
    END AS current_stock
FROM stock_rotation sr
JOIN inventory_markers im ON sr.id = im.id
JOIN inventory_groups ig ON sr.article_id = ig.article_id AND im.inventory_group = ig.inventory_group
ORDER BY sr.article_id, sr.date, sr.id;

代码说明： #

1. inventory_markers：给每个行分配一个组号，每出现一条盘点记录，组号递增，这样就能把盘点记录之后的行归为同一个组
2. inventory_groups：收集每个组的初始库存——盘点记录的quantity_change，没有盘点的商品组初始库存设为0
3. 最后通过CASE分支处理：盘点行直接用自身的数量作为当前库存，其他行从组的初始库存开始累加后续的数量变化

注意事项 #

• 你的PostgreSQL 9.3完全支持这些窗口函数语法，不需要升级版本
• 一定要用date, id作为排序条件，否则同一日期的记录顺序不确定会导致库存计算错误

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