MySQL中基于Model、Part维度做库存逐次扣减生成目标结果表是否可行？

这个运算逻辑完全可落地，本质是按Part分组、按Model优先级排序的滚动库存扣减，两种主流实现方案如下：

方案1：SQL实现（适配支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、Hive等） #

实现思路：

1. 对表1按Part分组、Model升序排序，计算每个Part下当前行及之前所有行的累计需求
2. 计算当前行扣减前的可用库存 = Part初始库存 - 当前行之前的累计需求（首行之前无需求，按0计算）
3. 扣减后剩余库存 = 扣减前可用库存 - 当前行需求数量

参考代码：

WITH t1_cumulative AS (
    SELECT 
        Model,
        Part,
        `Qty Need`,
        -- 计算当前行及之前的累计需求
        SUM(`Qty Need`) OVER(PARTITION BY Part ORDER BY Model ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumu_need,
        -- 计算当前行之前的累计需求，首行默认取0
        COALESCE(SUM(`Qty Need`) OVER(PARTITION BY Part ORDER BY Model ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS pre_cumu_need
    FROM 表1
)
SELECT 
    t1.Model,
    t1.Part,
    t1.`Qty Need`,
    t2.`Qty Stock` - t1.pre_cumu_need AS `Qty Stock`,
    t2.`Qty Stock` - t1.cumu_need AS `Qty Sub`
FROM t1_cumulative t1
LEFT JOIN 表2 t2 ON t1.Part = t2.Part
ORDER BY t1.Model, t1.Part;

方案2：Python Pandas实现 #

实现思路和SQL逻辑一致，分组排序计算累计值即可，参考代码：

import pandas as pd

# 构造示例数据，实际使用时替换为自己的数据源读取逻辑
df1 = pd.DataFrame({
    'Model': ['Model A', 'Model A', 'Model B', 'Model B'],
    'Part': ['Part A', 'Part B', 'Part A', 'Part B'],
    'Qty Need': [5, 3, 2, 4]
})
df2 = pd.DataFrame({
    'Part': ['Part A', 'Part B'],
    'Qty Stock': [10, 20]
})

# 按Part分组、Model排序，计算累计需求
df1 = df1.sort_values(by=['Part', 'Model']).reset_index(drop=True)
df1['pre_cumu_need'] = df1.groupby('Part')['Qty Need'].shift(1).fillna(0).groupby(df1['Part']).cumsum()
df1['cumu_need'] = df1.groupby('Part')['Qty Need'].cumsum()

# 关联库存表计算最终结果
df_result = df1.merge(df2, on='Part', how='left')
df_result['Qty Stock'] = df_result['Qty Stock'] - df_result['pre_cumu_need']
df_result['Qty Sub'] = df_result['Qty Stock'] - df_result['Qty Need']

# 调整输出顺序和排序规则
df_result = df_result[['Model', 'Part', 'Qty Need', 'Qty Stock', 'Qty Sub']].sort_values(by=['Model', 'Part']).reset_index(drop=True)
print(df_result)

如果存在库存不足的场景，可额外加判断逻辑，当扣减前可用库存小于当前行需求时，按实际可用库存扣减，剩余部分标记为缺料即可。

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