Oracle无需游标单SQL统计待上架商品对应货架实时数量方案

核心实现逻辑 #

你要的统计可以直接通过两张表的时间区间匹配完成，完全不需要提前生成分钟级时间序列，核心匹配规则为：统计对应货架下，上架时间<=统计时间且（下架时间>统计时间或下架时间为空） 的商品总数即可。

最简实现（标量子查询写法） #

直接在查询字段中嵌入统计逻辑，写法最简洁，适合中小数据量场景：

SELECT 
    a.ITEM,
    a.STATUS,
    a.SHELF,
    a.WAIT_DT,
    (SELECT COUNT(*) 
     FROM TABLEB b 
     WHERE b.SHELF = a.SHELF 
       AND b.LOAD_DATE <= a.WAIT_DT 
       AND (b.UNLOAD_DATE > a.WAIT_DT OR b.UNLOAD_DATE IS NULL)) AS SHELF_COUNT
FROM TABLEA a;

高性能优化实现（关联聚合写法） #

如果数据量较大，且TABLEA中存在大量重复的SHELF + WAIT_DT组合，可以用预聚合的方式降低计算开销：

SELECT 
    a.ITEM,
    a.STATUS,
    a.SHELF,
    a.WAIT_DT,
    NVL(b.SHELF_COUNT, 0) AS SHELF_COUNT
FROM TABLEA a
LEFT JOIN (
    SELECT 
        a_inner.SHELF,
        a_inner.WAIT_DT,
        COUNT(b_inner.ITEM) AS SHELF_COUNT
    FROM TABLEA a_inner
    LEFT JOIN TABLEB b_inner
        ON b_inner.SHELF = a_inner.SHELF
        AND b_inner.LOAD_DATE <= a_inner.WAIT_DT
        AND (b_inner.UNLOAD_DATE > a_inner.WAIT_DT OR b_inner.UNLOAD_DATE IS NULL)
    GROUP BY a_inner.SHELF, a_inner.WAIT_DT
) b ON a.SHELF = b.SHELF AND a.WAIT_DT = b.WAIT_DT;

注意事项 #

• 需根据业务规则判断是否要保留UNLOAD_DATE IS NULL的条件，如果业务中下架时间为空代表商品仍在架，必须保留该条件否则会漏算数据
• 数据量较大时可以在TABLEB上创建(SHELF, LOAD_DATE, UNLOAD_DATE)联合索引，大幅提升查询效率
• 两种写法均为单SQL实现，输出字段完全匹配要求，不需要存储过程游标循环，也不需要生成额外的时间序列

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