SQL关联查询未过滤日期致库存统计异常问题排查

问题背景 #

现有两张业务表：

• Inventory：存储商品在指定日期的库存快照数据
• Attributes：存储商品当前的分类属性信息

最初通过子查询按单个分类统计库存（语句如下），但需要为每个分类单独执行一次查询，效率低下：

select
    warehouse_id,
    ItemId,
    sum(on_hand_quantity) as inventory
from 
    inventory a
where 
    warehouse_id in ('Warehouse1', 'Warehouse2')
    and to_char(snapshot_day,'YYYY,MM,DD') = '202X,XX,XX' -- 替换为实际目标日期
    and ItemId in (Select ItemId
from 
    attributes
where 
    Class  in ('Class1'))
group by   
    1,2

改为关联查询批量统计多分类库存后，结果数值远高于实际值，怀疑sum计算了非指定日期的数据，但无法定位问题，关联查询语句如下：

SELECT 
    uic.snapshot_day,
    uic.warehouse_id,
    Sum(Case When att.Class = 'Class1' 
         Then uic.on_hand_quantity Else 0 End) as Class1_inventory
    Sum(Case When att.Class= 'Class2' 
         Then uic.on_hand_quantity Else 0 End) as Class2_inventory,
    Sum(Case When att.stamp = 'Class3' 
         Then uic.on_hand_quantity Else 0 End) as Class3_inventory

FROM inventory uic  JOIN attributes att 
    ON uic.ItemId = att.ItemId

WHERE 
    AND uic.warehouse_id in (‘Warehouse1’, ‘Warehouse2’)
    AND att.class in ('Class1','Class2','Class3')
    AND to_char(uic snapshot_day,'YYYY,MM,DD') = 'YYYY,MM,DD' 
    
GROUP BY 2,1

问题核心原因 #

1. 数据重复关联：若Attributes表中同一ItemId存在多条记录（比如商品分类变更后保留了历史记录），JOIN操作会让Inventory的单条库存快照被重复匹配，最终sum时重复计算，导致数值虚高。
2. 语法错误：原关联查询存在多处语法问题：
   • to_char(uic snapshot_day)缺少点号，应为to_char(uic.snapshot_day)
   • 第一个Sum(Case...)语句后缺失逗号
   • att.stamp是笔误，应为att.Class
   • WHERE子句开头多了无效的AND
   • GROUP BY列顺序与SELECT列顺序不匹配，易导致分组逻辑混乱

修正后的查询方案 #

方案1：先聚合库存再关联分类（推荐） #

先对指定日期的库存按仓库、商品维度聚合，再关联分类表，避免重复计算：

SELECT 
    agg.warehouse_id,
    att.Class,
    SUM(agg.inventory) AS total_inventory
FROM (
    -- 先统计指定日期、指定仓库的单商品库存
    SELECT 
        warehouse_id,
        ItemId,
        SUM(on_hand_quantity) AS inventory
    FROM inventory
    WHERE 
        warehouse_id IN ('Warehouse1', 'Warehouse2')
        AND to_char(snapshot_day, 'YYYY,MM,DD') = '202X,XX,XX'
    GROUP BY warehouse_id, ItemId
) agg
JOIN attributes att ON agg.ItemId = att.ItemId
WHERE att.Class IN ('Class1', 'Class2', 'Class3')
GROUP BY agg.warehouse_id, att.Class
-- 如需转成列展示，可添加PIVOT：
-- PIVOT (
--     SUM(total_inventory)
--     FOR Class IN ('Class1' AS Class1_inventory, 'Class2' AS Class2_inventory, 'Class3' AS Class3_inventory)
-- )

方案2：修正语法并确保分类记录唯一 #

如果Attributes表中每个ItemId仅存一条当前分类记录，直接修正语法错误即可：

SELECT 
    uic.snapshot_day,
    uic.warehouse_id,
    SUM(CASE WHEN att.Class = 'Class1' THEN uic.on_hand_quantity ELSE 0 END) AS Class1_inventory,
    SUM(CASE WHEN att.Class = 'Class2' THEN uic.on_hand_quantity ELSE 0 END) AS Class2_inventory,
    SUM(CASE WHEN att.Class = 'Class3' THEN uic.on_hand_quantity ELSE 0 END) AS Class3_inventory
FROM inventory uic
JOIN attributes att ON uic.ItemId = att.ItemId
WHERE 
    uic.warehouse_id IN ('Warehouse1', 'Warehouse2')
    AND att.Class IN ('Class1', 'Class2', 'Class3')
    AND to_char(uic.snapshot_day, 'YYYY,MM,DD') = '202X,XX,XX'
GROUP BY uic.snapshot_day, uic.warehouse_id -- 明确写列名，避免顺序错误

若Attributes表存在多历史分类记录，需先取每个商品的最新分类再关联：

WITH latest_attributes AS (
    SELECT 
        ItemId,
        Class,
        -- 假设用update_timestamp判断最新分类记录，可替换为实际自增ID或其他时间字段
        ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY update_timestamp DESC) AS rn
    FROM attributes
    WHERE Class IN ('Class1', 'Class2', 'Class3')
)
SELECT 
    uic.snapshot_day,
    uic.warehouse_id,
    SUM(CASE WHEN la.Class = 'Class1' THEN uic.on_hand_quantity ELSE 0 END) AS Class1_inventory,
    SUM(CASE WHEN la.Class = 'Class2' THEN uic.on_hand_quantity ELSE 0 END) AS Class2_inventory,
    SUM(CASE WHEN la.Class = 'Class3' THEN uic.on_hand_quantity ELSE 0 END) AS Class3_inventory
FROM inventory uic
JOIN latest_attributes la ON uic.ItemId = la.ItemId AND la.rn = 1
WHERE 
    uic.warehouse_id IN ('Warehouse1', 'Warehouse2')
    AND to_char(uic.snapshot_day, 'YYYY,MM,DD') = '202X,XX,XX'
GROUP BY uic.snapshot_day, uic.warehouse_id

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