如何高效统计1月各部门每日正式状态员工数量

问题描述 #

需要统计2023年1月每个部门每天处于正式状态的员工数量，正式状态需同时满足：

• 员工入职日期 entry_date ≤ 统计日期
• 员工转正日期 regular_date ≤ 统计日期
• 员工离职日期 leave_date > 统计日期
  所有空日期已通过fillna('2100-01-01')填充。原循环实现在大数据量下性能极差，需优化方案。

高效解决方案思路 #

放弃逐日期逐部门的循环判断，改用向量化操作+区间重叠计数的方式，核心逻辑：

1. 将所有日期列转换为datetime类型，便于日期运算
2. 生成2023年1月的完整日期序列，结合所有部门生成部门-日期的全量组合
3. 对每个员工计算其正式状态的起始日期（取entry_date和regular_date的最大值）和结束日期（leave_date）
4. 通过向量化判断，统计每个部门-日期下符合条件的员工数
5. 最后按需整理成透视表格式

优化后代码 #

import pandas as pd

# 1. 数据初始化与日期类型转换
columns = ['entry_date', 'leave_date', 'employee_id', 'department_id', 'regular_date']
data = [
    ['2023-01-01', '2100-01-01', '1', 'a', '2023-01-05'],
    ['2023-01-03', '2023-01-15', '2', 'a', '2023-01-06'],
    ['2023-01-03', '2023-01-18', '3', 'b', '2023-01-08'],
    ['2023-01-08', '2023-01-20', '4', 'b', '2023-01-10']
]
df = pd.DataFrame(data, columns=columns)

# 批量转换日期列为datetime类型
date_cols = ['entry_date', 'leave_date', 'regular_date']
df[date_cols] = df[date_cols].apply(pd.to_datetime)

# 2. 生成目标日期范围与部门-日期全组合
date_range = pd.date_range(start='2023-01-01', end='2023-01-31', freq='D')
departments = df['department_id'].unique()
# 生成笛卡尔积：每个部门对应所有统计日期
full_dates = pd.MultiIndex.from_product([departments, date_range], names=['department_id', 'date']).to_frame(index=False)

# 3. 计算每个员工的正式状态有效区间
df['formal_start'] = df[['entry_date', 'regular_date']].max(axis=1)
df['formal_end'] = df['leave_date']

# 4. 向量化判断并统计有效员工数
# 关联全组合与员工数据
merged = full_dates.merge(df, on='department_id', how='left')
# 判断当前日期是否在员工的正式状态区间内
merged['is_valid'] = (merged['date'] >= merged['formal_start']) & (merged['date'] < merged['formal_end'])

# 5. 按部门和日期聚合，并转换为透视表格式
result = merged.groupby(['department_id', 'date'])['is_valid'].sum().reset_index(name='cnt_regular')

result['month'] = result['date'].dt.strftime('%Y-%m')
result['day'] = result['date'].dt.strftime('%d')
final_pivot = result.pivot_table(
    index=['department_id', 'month'],
    columns='day',
    values='cnt_regular',
    aggfunc='sum'
).reset_index()

print(final_pivot)

性能优势说明 #

• 彻底避免嵌套循环的O(N*M)时间复杂度（N为部门数，M为天数），改用向量化操作后时间复杂度接近O(K)（K为员工总数）
• 基于pandas内置的分组、合并等C语言实现的优化函数，执行效率远高于Python原生循环
• 可轻松支撑百万级以上的员工数据量场景

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