含跨月周期的工时DataFrame如何按年月维度统计总耗时

核心逻辑：先将每条跨月记录按覆盖的月份拆分，计算每个月的实际投入天数，乘以单日工时后按年月维度汇总即可得到结果。以下提供两种常用场景的实现方案：

1. Python Pandas 实现 #

普通小数据量方案（直接拆分到天，逻辑直观） #

import pandas as pd

# 构造样例数据
df = pd.DataFrame({
    'employee_id': [1234, 1234, 1236, 1237, 1237],
    'start_date': pd.to_datetime(['2019-01-15', '2019-01-15', '2019-01-29', '2019-01-19', '2019-12-01']),
    'end_date': pd.to_datetime(['2019-01-15', '2019-01-17', '2019-02-28', '2019-03-05', '2020-01-10']),
    'hours_spent_per_day': [2, 3, 4, 2, 2]
})

# 生成每条记录覆盖的所有日期
df['date'] = df.apply(lambda x: pd.date_range(x['start_date'], x['end_date']), axis=1)
df_expand = df.explode('date', ignore_index=True)

# 提取年月后汇总
df_expand['Year'] = df_expand['date'].dt.year
df_expand['Month'] = df_expand['date'].dt.month
result = df_expand.groupby(['Year', 'Month'], as_index=False)['hours_spent_per_day'].sum()
result.rename(columns={'hours_spent_per_day': 'Hours Spent'}, inplace=True)

大数据量优化方案（无需拆分到天，直接按月份计算） #

def calculate_monthly_hours(row):
    # 生成记录覆盖的所有月份首日
    months = pd.date_range(row['start_date'].to_period('M').to_timestamp(), 
                           row['end_date'].to_period('M').to_timestamp(), freq='MS')
    res = []
    for month_start in months:
        month_end = month_start + pd.offsets.MonthEnd(0)
        # 计算当月实际覆盖天数
        actual_start = max(row['start_date'], month_start)
        actual_end = min(row['end_date'], month_end)
        days = (actual_end - actual_start).days + 1
        res.append((month_start.year, month_start.month, days * row['hours_spent_per_day']))
    return res

# 展开计算结果并汇总
monthly_data = df.apply(calculate_monthly_hours, axis=1).explode().apply(pd.Series)
monthly_data.columns = ['Year', 'Month', 'Hours Spent']
result = monthly_data.groupby(['Year', 'Month'], as_index=False)['Hours Spent'].sum()

2. SQL 实现 #

MySQL 8.0+ 版本（递归CTE生成日期序列） #

WITH RECURSIVE date_range AS (
    SELECT MIN(start_date) AS date FROM employee_hours
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM date_range
    WHERE date < (SELECT MAX(end_date) FROM employee_hours)
)
SELECT 
    YEAR(d.date) AS Year,
    MONTH(d.date) AS Month,
    SUM(h.hours_spent_per_day) AS `Hours Spent`
FROM employee_hours h
JOIN date_range d ON d.date BETWEEN h.start_date AND h.end_date
GROUP BY YEAR(d.date), MONTH(d.date)
ORDER BY Year, Month;

Hive/Spark SQL 版本（内置函数直接生成日期序列） #

SELECT 
    YEAR(date) AS Year,
    MONTH(date) AS Month,
    SUM(hours_spent_per_day) AS `Hours Spent`
FROM employee_hours
LATERAL VIEW EXPLODE(SEQUENCE(start_date, end_date, INTERVAL 1 DAY)) t AS date
GROUP BY YEAR(date), MONTH(date)
ORDER BY Year, Month;

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