基于共享数量池的反递归聚合实现技术问询

原始数据表结构 #

CREATE TABLE #Tree 
(
    Item VARCHAR(6),
    TreePosition VARCHAR(7),
    RequiredPerParentQty FLOAT,
    ExistingQty FLOAT
);

INSERT INTO #Tree 
VALUES
    ('item0', '0', 2, 1),
    ('item1', '0.0', 2, 2),
    ('item2', '0.1', 3, 1),
    ('item3', '0.1.0', 1, 2),
    ('item4', '0.2', 2, 0),
    ('item5', '0.2.0', 1, 2),
    ('item6', '0.2.1', 1, 3),
    ('item7', '0.3', 2, 0),
    ('item8', '0.3.0', 1, 1),
    ('item9', '0.3.0.0', 1, 1),
    ('item6', '0.3.0.1', 3, 3),
    ('item10', '0.3.1', 1, 1),
    ('item6', '0.3.2', 1, 3)

需求说明 #

需要计算每个物料的总需求数量（满足上层节点直至根节点item0的需求），以及实际可供给数量（自身现有库存+子节点可调配库存的总和，优先填补需求缺口），最终输出格式如下：

声明式SQL实现方案 #

核心思路是用递归CTE双向遍历树形结构：先从叶子节点向上汇总供给能力，再从根节点向下推导需求缺口，结合共享组件库存分配逻辑得到结果。

完整实现代码 #

WITH NodeHierarchy AS (
    -- 预处理节点层级与父子关系
    SELECT 
        Item,
        TreePosition,
        RequiredPerParentQty,
        ExistingQty,
        -- 提取父节点路径
        CASE 
            WHEN CHARINDEX('.', REVERSE(TreePosition)) > 0 
            THEN LEFT(TreePosition, LEN(TreePosition) - CHARINDEX('.', REVERSE(TreePosition)))
            ELSE NULL 
        END AS ParentPosition,
        -- 计算节点层级
        LEN(TreePosition) - LEN(REPLACE(TreePosition, '.', '')) AS Level
    FROM #Tree
),
SupplyCalculation AS (
    -- 从叶子节点向上汇总可供给总量
    SELECT 
        Item,
        TreePosition,
        ExistingQty AS AvailableQty,
        ExistingQty AS TotalSupply
    FROM NodeHierarchy
    WHERE Level = (SELECT MAX(Level) FROM NodeHierarchy)
    UNION ALL
    SELECT 
        nh.Item,
        nh.TreePosition,
        nh.ExistingQty + sc.TotalSupply AS AvailableQty,
        nh.ExistingQty + sc.TotalSupply AS TotalSupply
    FROM NodeHierarchy nh
    JOIN SupplyCalculation sc ON nh.TreePosition = sc.ParentPosition
),
DemandCalculation AS (
    -- 从根节点向下推导总需求与缺口
    SELECT 
        Item,
        TreePosition,
        RequiredPerParentQty AS Required,
        CASE WHEN ExistingQty < RequiredPerParentQty THEN RequiredPerParentQty - ExistingQty ELSE 0 END AS Gap
    FROM NodeHierarchy
    WHERE TreePosition = '0'
    UNION ALL
    SELECT 
        nh.Item,
        nh.TreePosition,
        dc.Gap * nh.RequiredPerParentQty AS Required,
        CASE WHEN sc.TotalSupply < (dc.Gap * nh.RequiredPerParentQty) THEN (dc.Gap * nh.RequiredPerParentQty) - sc.TotalSupply ELSE 0 END AS Gap
    FROM NodeHierarchy nh
    JOIN DemandCalculation dc ON nh.ParentPosition = dc.TreePosition
    JOIN SupplyCalculation sc ON nh.TreePosition = sc.TreePosition
),
SharedComponentStock AS (
    -- 汇总共享组件的总库存
    SELECT 
        Item,
        SUM(ExistingQty) AS TotalStock
    FROM #Tree
    GROUP BY Item
),
FinalResult AS (
    -- 计算最终需求与供给
    SELECT 
        dc.Item,
        dc.Required,
        CASE 
            WHEN sc.TotalSupply >= dc.Required THEN dc.Required
            ELSE sc.TotalSupply + IIF(scs.TotalStock >= (dc.Required - sc.TotalSupply), dc.Required - sc.TotalSupply, scs.TotalStock)
        END AS Supplied
    FROM DemandCalculation dc
    JOIN SupplyCalculation sc ON dc.TreePosition = sc.TreePosition
    LEFT JOIN SharedComponentStock scs ON dc.Item = scs.Item
    -- 过滤出预期输出的节点
    WHERE dc.Item IN ('item0','item1','item2','item4','item7','item8')
)
SELECT Item, Required, Supplied FROM FinalResult ORDER BY Item;

关键逻辑说明 #

1. 节点层级预处理：通过拆分TreePosition字段，明确每个节点的父节点和层级，为递归遍历做准备。
2. 供给能力汇总：从最底层叶子节点开始，向上累加自身与子节点的库存，得到每个节点的最大可供给量。
3. 需求缺口推导：从根节点出发，根据父节点的需求缺口，计算当前节点的总需求，同时记录未被填补的缺口。
4. 共享组件处理：对在多个节点重复出现的组件（如item6）统一汇总库存，优先填补上层节点的需求缺口。

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