Pandas如何按日期重叠规则校验员工项目分配占比是否超100%

示例CSV数据 #

ID,Project,From,To,Percentage
1,APPLE,01-01-2022,31-03-2022,50
1,MICROSOFT,01-01-2022,15-01-2022,50
1,MICROSOFT,01-02-2022,28-02-2022,50
1,MICROSOFT,01-03-2022,31-03-2022,50
2,ORACLE,01-02-2022,23-06-2022,50
3,APPLE,23-04-2022,23-06-2022,100
1,MICROSOFT,16-01-2022,31-01-2022,50
2,DELL,01-12-2021,01-04-2022,50

需求说明 #

校验是否存在任意日期下，任意员工的项目总分配占比超过100%的情况。

例：员工ID为1的记录中，2022年1月1日至3月31日分配到APPLE项目的占比为50%，同期在MICROSOFT项目的多个子时间段分配占比均为50%，不存在任意日期总分配占比超100%的情况。

现存问题 #

直接使用区间重叠判断条件 (StartA <= EndB) and (EndA >= StartB) 匹配记录后执行sum(percentage)时，第一条记录会匹配ID=1的所有其他记录，得到250%的错误计算结果，原因是第一条记录的时间区间包含了所有更小的子区间，直接匹配求和会重复计算。

实现方案 #

核心逻辑是放弃直接对重叠区间求和的思路，用线扫描法按时间节点累加占比，避免包含关系的区间重复计算，步骤如下：

1. 按员工ID分组处理，不同员工的分配记录互不干扰
2. 对每个员工的每条分配记录生成两个事件：(记录开始日期, +当前记录占比)、(记录结束日期+1天, -当前记录占比)
3. 把当前员工的所有事件按日期升序排序，同一日期下优先处理扣减占比的事件，避免端点日期计算错误
4. 按顺序遍历事件累加总占比，遍历过程中只要出现总占比>100%的情况，就说明该员工存在超量分配

参考Python实现代码 #

import pandas as pd

# 读取数据并统一日期格式
df = pd.read_csv("allocation.csv")
df["From"] = pd.to_datetime(df["From"], format="%d-%m-%Y")
df["To"] = pd.to_datetime(df["To"], format="%d-%m-%Y")

overload_result = []

# 按员工分组校验
for emp_id, emp_records in df.groupby("ID"):
    events = []
    # 生成所有增减事件
    for _, row in emp_records.iterrows():
        events.append((row["From"], row["Percentage"], row["Project"]))
        events.append((row["To"] + pd.Timedelta(days=1), -row["Percentage"], row["Project"]))
    
    # 排序：先按日期升序，同日期下扣减事件排在前面
    events.sort(key=lambda x: (x[0], x[1]))
    
    current_total = 0
    current_period_start = None
    current_projects = set()
    # 线扫描累加
    for date, delta, project in events:
        if current_period_start is not None and current_period_start < date and current_total > 100:
            overload_result.append({
                "ID": emp_id,
                "OverloadStart": current_period_start.strftime("%d-%m-%Y"),
                "OverloadEnd": (date - pd.Timedelta(days=1)).strftime("%d-%m-%Y"),
                "TotalPercentage": current_total,
                "RelatedProjects": ",".join(current_projects)
            })
        # 更新当前占比和关联项目
        current_total += delta
        if delta > 0:
            current_projects.add(project)
        else:
            current_projects.discard(project)
        current_period_start = date

# 输出超量分配结果
print(pd.DataFrame(overload_result))

方案说明 #

• 该方法时间复杂度为O(nlogn)，即使是十万级以上的分配记录也能快速计算，不会出现区间包含导致的重复求和问题
• 针对提供的示例数据运行代码，员工2在2022-02-01至2022-04-01期间ORACLE+DELL占比刚好100%，员工1、3所有时间段占比均不超过100%，和预期结果完全一致
• 如果需要用SQL实现，可以通过生成日期维度表、关联统计每个员工每日占比总和的方式实现，核心判断逻辑和上述方案一致

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