如何用PL/SQL创建按位置展示当前库存的视图？

需要用PL/SQL创建一个按库存位置展示当前库存的视图，现有数据集如下：

product    location    quantity     moved     dttm

apple      shop1       30           null      '08/10/22' 
orange     shop1       20           null      '08/15/22' 
pear       shop1       40           null      '08/20/22' 
apple      shop2       10           shop1     '08/22/22' 
orange     shop3       15           shop1     '08/22/22' 

字段说明：

• location：产品当前位置及对应数量
• moved：库存之前的位置（新增库存时为null）
• dttm：库存变更发生日期

期望视图效果：

Location    Product    Quantity 

shop1      apple       20 
shop1      orange      5 
shop1      pear        40
shop2      apple       10
shop3      orange      15

已通过OUTER APPLY实现库存新增到位置的逻辑，但在通过moved列对指定位置的产品库存做减法时遇到瓶颈，参考过库存追踪相关的SQL方案，但因多位置因素增加了计算复杂度，疑问如下：

• 遗漏了什么逻辑？
• 是否需要重构数据集？

不需要重构数据集，核心是把每一条库存变更记录拆解成位置的增减操作：

• 当moved为null时：仅在当前location增加对应quantity
• 当moved不为null时：在当前location增加quantity，同时在原moved位置减少对应quantity

基于这个逻辑，可通过UNION ALL拆分操作记录，再分组求和实现需求，PL/SQL视图创建语句如下：

CREATE OR REPLACE VIEW current_inventory AS
WITH inventory_changes AS (
    -- 新增库存：当前位置加数量
    SELECT 
        product,
        location AS loc,
        quantity AS qty
    FROM your_inventory_table
    UNION ALL
    -- 移库出库：原位置减数量
    SELECT 
        product,
        moved AS loc,
        -quantity AS qty
    FROM your_inventory_table
    WHERE moved IS NOT NULL
)
SELECT 
    loc AS Location,
    product AS Product,
    SUM(qty) AS Quantity
FROM inventory_changes
GROUP BY loc, product
HAVING SUM(qty) > 0; -- 可选：过滤库存为0或负数的记录

逻辑说明 #

1. 用CTEinventory_changes拆分每条记录为对应增减操作：
   • 新增记录直接保留为当前位置的正数量
   • 移库记录额外生成一条原位置的负数量记录
2. 按位置和产品分组求和，得到每个位置的实际库存
3. HAVING SUM(qty) > 0可过滤已被移空的库存条目，不需要可去掉

该方案避免了复杂关联逻辑，通过拆分操作记录实现库存加减，比OUTER APPLY的关联方式更简洁高效。

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