库存/仓库库存追踪系统：查询与数据模型优化技术问询

1. 修改查询实现销售/领用及移库后的库存统计 #

要同时统计原位置剩余库存和移库后的新位置库存，需要拆分两种场景计算后合并结果：

-- 计算各批次的销售/领用总量
WITH consumed AS (
    SELECT 
        stock_id,
        SUM(transfer_quantity) AS total_consumed
    FROM transfers
    WHERE to_location_id IS NULL
    GROUP BY stock_id
),
-- 计算各批次的移库出库总量
transferred_out AS (
    SELECT 
        stock_id,
        SUM(transfer_quantity) AS total_transferred
    FROM transfers
    WHERE to_location_id IS NOT NULL
    GROUP BY stock_id
),
-- 计算各位置的移库入库总量
transferred_in AS (
    SELECT 
        t.to_location_id AS location_id,
        s.attributes,
        SUM(t.transfer_quantity) AS total_in
    FROM transfers t
    JOIN stocks s ON t.stock_id = s.id
    WHERE t.to_location_id IS NOT NULL
    GROUP BY t.to_location_id, s.attributes
)
-- 合并原位置剩余库存和移库新库存
SELECT 
    s.id AS batch_id,
    s.location_id,
    s.attributes,
    (s.quantity - COALESCE(c.total_consumed, 0) - COALESCE(t.total_transferred, 0)) AS remaining_qty
FROM stocks s
LEFT JOIN consumed c ON s.id = c.stock_id
LEFT JOIN transferred_out t ON s.id = t.stock_id
WHERE (s.quantity - COALESCE(c.total_consumed, 0) - COALESCE(t.total_transferred, 0)) > 0

UNION ALL

SELECT 
    NULL AS batch_id,
    ti.location_id,
    ti.attributes,
    ti.total_in AS remaining_qty
FROM transferred_in ti;

这个查询会返回两部分数据：原始批次在原位置的剩余数量，以及移库后在目标位置的库存数量。需要按位置筛选时，直接在最后加WHERE location_id = [目标位置ID]即可。

2. 当前数据模型的优化建议 #

当前模型并非最优，存在以下痛点：

• 库存剩余量需要实时计算，无法快速获取当前库存状态
• 移库产生的新库存没有独立批次记录，难以追踪来源和变动历史
• 按位置筛选库存时需要关联多表计算，性能随数据量增长下降明显

优化方案：拆分交易类型，新增库存余额表 #

调整后的表结构：

1. stocks表：保留为库存批次表，仅记录每批入库的基础信息（批次ID、初始入库数量、入库位置、属性、创建时间）
2. transfers表：新增transaction_type枚举字段（CONSUME=销售/领用，TRANSFER_OUT=移库出库，TRANSFER_IN=移库入库），明确交易类型；移库操作拆分为一进一出两条记录
3. 新增inventory_balances表：实时维护各批次在各位置的当前库存：

CREATE TABLE inventory_balances (
    stock_id text,
    location_id int,
    current_qty int,
    PRIMARY KEY (stock_id, location_id)
);

操作逻辑：

• 入库：向stocks插入记录，同时向inventory_balances插入初始库存数据
• 销售/领用：向transfers插入CONSUME记录，更新inventory_balances对应批次和位置的数量（扣减）
• 移库：向transfers插入TRANSFER_OUT（原位置）和TRANSFER_IN（目标位置）两条记录，同步更新inventory_balances：原位置扣减，目标位置增加

优势：

• 按位置筛选库存时直接查询inventory_balances，性能优异
• 库存状态实时可见，无需复杂计算
• 交易历史清晰，便于审计和追溯

3. 分类账（Ledger）式单表模型设计 #

分类账模型核心是所有库存变动通过单表记录，取消stocks的原始数量字段，通过交易流水的总和计算当前库存。

表结构设计： #

CREATE TABLE inventory_ledger (
    id text PRIMARY KEY,
    transaction_type text NOT NULL CHECK (transaction_type IN ('RECEIVE', 'CONSUME', 'TRANSFER_OUT', 'TRANSFER_IN')),
    batch_id text, -- 入库时生成唯一批次ID，后续交易关联该ID
    quantity int NOT NULL, -- 正数为库存增加（RECEIVE/TRANSFER_IN），负数为库存减少（CONSUME/TRANSFER_OUT）
    location_id int NOT NULL,
    attributes jsonb, -- 入库时记录属性，后续交易可继承
    transfer_ref text, -- 移库时，TRANSFER_OUT和TRANSFER_IN用同一编号关联
    created_at datetime NOT NULL
);

操作逻辑： #

1. 入库：插入transaction_type = 'RECEIVE'的记录，quantity为入库数量，生成唯一batch_id，填写库存属性
2. 销售/领用：插入transaction_type = 'CONSUME'的记录，quantity为负数（扣减数量），关联对应batch_id和库存所在位置
3. 移库：插入两条记录：
   • transaction_type = 'TRANSFER_OUT'：quantity为负数（移库数量），location_id为原位置，transfer_ref为同一移库编号
   • transaction_type = 'TRANSFER_IN'：quantity为正数（移库数量），location_id为目标位置，transfer_ref与上一条一致，关联同一batch_id

查询当前库存（支持按位置筛选）： #

SELECT 
    batch_id,
    location_id,
    attributes,
    SUM(quantity) AS current_qty
FROM inventory_ledger
GROUP BY batch_id, location_id, attributes
HAVING SUM(quantity) > 0;

优势： #

• 单表记录所有变动，结构简洁，便于审计
• 无需维护原始数量，通过求和即可得到当前库存
• 天然支持按位置、批次、属性等多维度筛选查询

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