Thinking in SQL系列之三：供需分配问题

原创： 牛超   2017-02-11   Mail：10867910@qq.com

供需分配，简单来说就是你有各种需求，我来个性化供应满足。很多问题都可以转化为此类问题，应用很普遍。比如餐桌上摆满各种茶杯，海碗，主人拿出可乐、雪碧、牛奶、啤酒等各种饮料来招待。直到客人喝饱或者饮料喝完都算供需分配完成。

从2006年第一次接触到货需求分配程序，就思考过一个问题，一个SQL能否处理该问题，当时由于对SQL的掌握程度有限，分析结论是不可以，原因是前一次分配会影响后面的处理，所以只能用ROW BY ROW的方式处理了。之后陆续遇到过类似的供需分配问题，都是采用PLSQL或者其它语言实现。

直到前几年在实现一个ERP系统的PO/RCV接收分配功能时，出于对ORACLE SQL掌握的自信程度。重新思考此类问题时，为了消除行与行之间的依赖，头脑风暴过程想到数字电路有个ALU加法器改进设计，即提前进位加法器通过增加额外的门电路，相临位进位无需等待，从而实现了一个脉冲完成8位加法的并行处理。

供需分配，也可以采用类似ALU的改进，将ROW BY ROW的处理方式转换为并行处理。通过提前窥探，无需等待前一次的分配结果，而通过统计函数提前算出之前的分配结果。用一个SQL完全可以搞定。

要完成这个提前窥探，有个前提很重要：有序的供应按照既定顺序分配到需求上，即需求与供应都要有序组合。

以到货分配入库为例，根据到货ID（REV_ID)的顺序以库存组织(ORGANIZATION_ID)和物料(ITEM_ID)维度按照货位优先级将到货数量分配到各个货位的空闲区（容量QUANTITY)，比如将REV_ID为5的到货，分配到L2货位，分配数量3，

可以简单表示为： {REV_ID，LOC，ALLOC_QTY} = {5，L2，3}

货位表

到货表

为简化代码量，暂不考虑货位会饱和的情况，如果感兴趣可以自己调整，具体SQL实现如下（个人环境ORACLE XE 11.2）：

WITH INV_INFO AS
 (SELECT 100 ORGANIZATION_ID, --组织
         501 ITEM_ID, --物料
         'S1' SUBINVENTORY_CODE, --子库存（根据优先级 priority)
         'L1' LOC, --货位
         2 QUANTITY, --货位剩余容量，可视为当前需求数量
         1 PRIORITY --分配优先级，可视为特殊的货位ID
    FROM DUAL
  UNION ALL
  SELECT 100, 501, 'S2', 'L2', 4, 2
    FROM DUAL
  UNION ALL
  SELECT 100, 502, 'S3', 'L3', 10, 1
    FROM DUAL
  UNION ALL
  SELECT 100, 502, 'S4', 'L4', 3, 2
    FROM DUAL
  UNION ALL
  SELECT 100, 502, 'S5', 'L5', 5, 3
    FROM DUAL    
    ) ,
REV_INFO AS
 (SELECT 1   REV_ID, --接收ID，做为需求优先级
         100 ORGANIZATION_ID, --组织
         501 ITEM_ID, --物料
         2   REV_QTY --当前供应量
    FROM DUAL
  UNION ALL
  SELECT 2, 100, 501, 2
    FROM DUAL
  UNION ALL
  SELECT 3, 100, 502, 14
    FROM DUAL
  UNION ALL
  SELECT 4, 100, 502, 1
    FROM DUAL
  UNION ALL
  SELECT 5, 100, 502, 2
    FROM DUAL    
    ) ,
ITEM_REQ AS --需求
 (SELECT INV_INFO.*,
         SUM(QUANTITY) OVER(PARTITION BY ORGANIZATION_ID, ITEM_ID ORDER BY PRIORITY) REQ_QTY --滚动需求量 （关键）
    FROM INV_INFO),
ITEM_SUP AS --供应
 (SELECT REV_INFO.*,
         SUM(REV_QTY) OVER(PARTITION BY ORGANIZATION_ID, ITEM_ID ORDER BY REV_ID) SUP_QTY --滚动供应量（关键）
    FROM REV_INFO),
TA AS
 (SELECT ORGANIZATION_ID,
         ITEM_ID,
         SUBINVENTORY_CODE,
         PRIORITY,
         LOC,
         QUANTITY,
         REQ_QTY, --滚动需求量 下界
         REQ_QTY - QUANTITY LAST_REQ_QTY, --上条数据滚动需求量, 上界  
         REV_ID,
         REV_QTY,
         SUP_QTY
    FROM ITEM_REQ C
    JOIN ITEM_SUP E
   USING (ORGANIZATION_ID, ITEM_ID)
   ORDER BY ORGANIZATION_ID,ITEM_ID,PRIORITY,REV_ID) ,
ALLOC_BOUND AS --货位边界集合
 (SELECT TA.*,
        LAG(PRIORITY,1,0) OVER (PARTITION BY ORGANIZATION_ID,
             ITEM_ID ORDER BY REV_ID )  PRIORITY_FROM --取上条为上界，起始货位
    FROM TA
   WHERE SUP_QTY > LAST_REQ_QTY AND SUP_QTY <= REQ_QTY --滚动供应量需要在（上次已分配总量，需求总量】区间内
  ), 
REV_INV AS
 (SELECT A.ORGANIZATION_ID,
         A.ITEM_ID,
         A.SUBINVENTORY_CODE,
         A.LOC,
         A.QUANTITY,--货位剩余容量，即需求量
         A.PRIORITY,--货位优先级
         A.REQ_QTY,--按优先级滚动需求总量
         B.REV_ID,--接收ID，接收优先级
         B.REV_QTY,--接收数量，即供应量
         B.SUP_QTY, --按优先级滚动供应总量
         LAG(LEAST(A.REQ_QTY, B.SUP_QTY),1,0) OVER (PARTITION BY A.ORGANIZATION_ID,
              A.ITEM_ID ORDER BY B.REV_ID,A.PRIORITY ) LAST_ALLOC_QTY, --关键处理，取上条滚动分配量，即提前窥探汇总
         B.PRIORITY_FROM,--起始货位，上界
         B.PRIORITY PRIORITY_TO --终止货位，下界
    FROM ITEM_REQ A
    JOIN ALLOC_BOUND B
      ON A.ORGANIZATION_ID = B.ORGANIZATION_ID
     AND A.ITEM_ID = B.ITEM_ID
     AND A.PRIORITY BETWEEN B.PRIORITY_FROM AND B.PRIORITY --关键处理，根据起始至结束货位边界，匹配需求
   ORDER BY B.REV_ID, A.PRIORITY),
ALLOC AS
 (SELECT ORGANIZATION_ID,
         ITEM_ID,
         SUBINVENTORY_CODE,
         LOC,--货位
         PRIORITY,--货位优先级
         QUANTITY,--货位容量
         REV_ID,--接收ID，接收优先级
         REV_QTY,--到货接收量
         LEAST(REQ_QTY, SUP_QTY) - LAST_ALLOC_QTY ALLOC_QTY --分配结果，解析如下：
         -- 滚动需求量与滚动供应量取相对小的量（可 接收/供应 量） - 上条滚动分配总量 = 本次分配量
    FROM REV_INV
   WHERE REQ_QTY > LAST_ALLOC_QTY --排除干扰数据
   )
SELECT *
  FROM ALLOC
  ORDER BY 1 , 2 , 5, 7;

思路很重要，ALLOC_BOUND块用来界定每个REV_ID与货位区间的范围，还有一段投影列 LAST_ALLOC_QTY 的计算，承上启下至关重要

如果在做大批次供需分配数据处理时，SQL易于优化，还有个好处就是可以指定并行度。

可以看到SQL的输出结果如下，可以关注ORGANIZATION_ID、ITEM_ID、PRIORITY、REV_ID、ALLOC_QTY，最后一列即为分配结果值，可以统计一下分配总量，物料501，502分别是4，17，说明供应量（到货）已经完全被分配：

以上这段脚本曾经被个人用来实现ERP PO/RCV接收分配、到货货位分配、MRP计算过程的PR/自由库存匹配分配、财务成本以及AP/AR往来余额帐龄分配报表，可以说，只要存在供需分配的场景，以上SQL应该都能满足。

总之，Thinking in SQL，数据处理，SQL为王。思路很关键，对于ORACLE，PLSQL永远只是SQL的补充，而非替代品，Row by row means slower and slower。SQL为我们提供了专注于集合处理的条件，提供了各类SQL监控、优化的手段。如果被我们“碎片化”再嵌入到PLSQL或者其他语言，沦为专门取数据的手段，那就有些背“道”而驰了。