对比A、B两表列值 按QtyShort区间匹配生成NeedDate列的实现方法

核心思路 #

不需要硬写逐行循环逻辑，先预处理收货表（表A）生成区间映射规则，再和短缺表（表B）做匹配即可，执行效率更高，也方便后续多物料场景扩展：

• 第一步：预处理表A，按Item+DueDate升序排序，计算每个收货日期对应的累计收货量，生成区间匹配规则：

  区间下限（不含）	区间上限（含）	对应NeedDate
  0	6	10/08/2021
  6	10	10/22/2021
  10	20	02/01/2022
  20	无穷大	09/09/9999

• 第二步：逐行判断表B的QtyShort字段：
  • 若QtyShort = 0，NeedDate直接取当前行的MatlDueDate
  • 若QtyShort > 0，对照上述区间规则，找到QtyShort所属区间，取对应日期即可
  • 若QtyShort大于表A该物料的累计总收货量，NeedDate统一取09/09/9999

代码实现示例 #

1. Python Pandas 实现 #

适用于离线表格处理场景，自动支持多物料分组匹配：

import pandas as pd

# 构造样例数据
df_a = pd.DataFrame({
    'Item': ['A1','A1','A1'],
    'DueDate': ['10/08/2021','10/22/2021','02/01/2022'],
    'QtyReceive': [6,4,10] # 若表中存储的是累计收货量，直接替换为[6,10,20]即可
})
df_b = pd.DataFrame({
    'Item': ['A1']*9,
    'MatlDueDate': ['06/01/2022','06/02/2022','06/03/2022','06/04/2022','06/05/2022','06/06/2022','06/07/2022','06/08/2022','06/09/2022'],
    'QtyShort': [0,0,1,2,5,7,10,15,25]
})

# 预处理表A，生成累计区间
df_a = df_a.sort_values(['Item','DueDate']).reset_index(drop=True)
# 若QtyReceive是累计值，把下一行的cumsum替换为df_a['QtyReceive']即可
df_a['cum_qty'] = df_a.groupby('Item')['QtyReceive'].cumsum()
df_a['lower_bound'] = df_a.groupby('Item')['cum_qty'].shift(1).fillna(0)
total_qty_map = df_a.groupby('Item')['cum_qty'].max().to_dict()

# 匹配生成NeedDate
def match_date(row):
    if row['QtyShort'] == 0:
        return row['MatlDueDate']
    if row['QtyShort'] > total_qty_map[row['Item']]:
        return '09/09/9999'
    match_rule = df_a[
        (df_a['Item'] == row['Item']) &
        (df_a['lower_bound'] < row['QtyShort']) &
        (df_a['cum_qty'] >= row['QtyShort'])
    ]
    return match_rule.iloc[0]['DueDate']

df_b['NeedDate'] = df_b.apply(match_date, axis=1)
print(df_b)

执行后输出结果和需求给出的预期完全一致。

2. SQL 实现（以Hive/Spark SQL为例，其他数据库语法基本兼容） #

适用于数仓表批量计算场景：

WITH a_cum AS (
    SELECT
        Item,
        DueDate,
        -- 若QtyReceive为累计值，直接替换为QtyReceive AS cum_qty即可
        SUM(QtyReceive) OVER(PARTITION BY Item ORDER BY DueDate) AS cum_qty,
        COALESCE(
            SUM(QtyReceive) OVER(PARTITION BY Item ORDER BY DueDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
            0
        ) AS lower_bound
    FROM table_a
),
a_total AS (
    SELECT Item, MAX(cum_qty) AS total_qty FROM a_cum GROUP BY Item
)
SELECT
    b.Item,
    b.MatlDueDate,
    b.QtyShort,
    CASE
        WHEN b.QtyShort = 0 THEN b.MatlDueDate
        WHEN b.QtyShort > t.total_qty THEN '09/09/9999'
        ELSE a.DueDate
    END AS NeedDate
FROM table_b b
LEFT JOIN a_total t ON b.Item = t.Item
LEFT JOIN a_cum a
    ON b.Item = a.Item
    AND b.QtyShort > a.lower_bound
    AND b.QtyShort <= a.cum_qty

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