计算生产出货数据来源于那些工单、那些转道数据？先进先出原理

Question

第一张表是4条数据生产数据生产13300，第二张表是3条数据出货数据出了13300，需要把出货的13300平分到第一张表，并且要把出货时间给匹配到出货单上，匹配原则就是按照先完工的工单匹配先出货数据，完工时间相同按照工单号小的先分配,用python或者sql

Answer 1

import pandas as pd
# 创建DataFrame
data1 = {
    'sd_date': ['20210622', '20210623', '20210624', '20210625','20210629','20210630'],
    'sd_id': ['A', 'A', 'A', 'A','B','B'],
    'sd_seq': [1,1,1,1,1,1],
    'item_num': [5000, 2200, 2800, 3300,3000,4000]
}

data2 = {
    'ch_date': ['20210725', '20210729', '20210816','20210630','20210830'],
    'sd_id': ['A', 'A', 'A','B','B'],
    'ship_num': [4500, 1800,6999,4000,3000]
}
df11 = pd.DataFrame(data1)
df22 = pd.DataFrame(data2)
df3 = pd.DataFrame(columns=['sd_date', 'sd_id', 'sd_seq', 'item_num', 'ch_date_new', 'ship_num_new'])
display(df11,df22)

#遍历‘sd_id’,保证每次取相同sd_id下数据
for sd in  df11['sd_id'].unique():
    df1 = df11[df11['sd_id'] == sd].reset_index(drop=True)
    df2 = df22[df22['sd_id'] == sd].reset_index(drop=True)
    residual = df2.loc[0, 'ship_num']
    i = 0 
    j = 0
    while i < len(df1) and j < len(df2) :
        if int(df1.loc[i, 'sd_date']) <= int(df2.loc[j, 'ch_date']) and df1.loc[i, 'item_num'] <= residual:
            new_row = {'sd_date':df1.loc[i, 'sd_date'],'sd_id':sd,'sd_seq':df1.loc[i, 'sd_seq'],'item_num':df1.loc[i, 'item_num'],'ch_date_new':df2.loc[j, 'ch_date'],'ship_num_new':df1.loc[i, 'item_num']}
            df3 = df3.append(new_row , ignore_index=True)
            residual -= df1.loc[i, 'item_num']
            i+=1
        elif int(df1.loc[i, 'sd_date']) <= int(df2.loc[j, 'ch_date']) and df1.loc[i, 'item_num'] > residual:
            new_row = {'sd_date':df1.loc[i, 'sd_date'],'sd_id':sd,'sd_seq':df1.loc[i, 'sd_seq'],'item_num':df1.loc[i, 'item_num'],'ch_date_new':df2.loc[j, 'ch_date'],'ship_num_new':residual}
            df3 = df3.append(new_row , ignore_index=True)
            df1.loc[i, 'item_num'] -= residual
            j+=1
            if j< len(df2):
                residual = df2.loc[j, 'ship_num']
        else:
            j+=1
            if j< len(df2):
                residual = df2.loc[j, 'ship_num']
print(df3[df3.ship_num_new!=0])