如何用MySQL查询两日期间活动的每日工时并生成人力直方图？

背景与需求 #

基于存储活动的workpacks表，每行对应一项活动，包含字段：

• start：活动开始日期
• finish：活动结束日期
• manhours：活动总工时

需要获取指定日期区间内每日的人力需求（计算方式：总工时/活动天数/8小时），用于构建动态人力直方图。

当前实现 #

目前已能通过以下SQL获取单活动的总工时与天数：

SELECT `manhours`, DATEDIFF(`finish`, `start`) 
FROM workpacks 
WHERE `project` = 9000 AND `wpid` = 45 AND `finish` > CURDATE();

示例返回：400工时、10天，经PHP计算得每日需5人（400/10/8）。

考虑过用PHP循环逐日期查询活动是否在区间内，但怀疑存在更优方案，询问是否有简便的MySQL查询方式，或PHP循环是否更合适。

参考Excel实现逻辑（对应单元格公式）：

=ROUND(IF(AND(J$1>$C2,J$1<$D2),$E2/($D2-$C2)/8,0),0)

逻辑：若日期在活动起止区间内，则按天数分摊总工时，除以8小时得到人力数，否则为0，最后取整。

解决方案 #

方案1：纯MySQL一次性查询（推荐） #

利用MySQL递归CTE生成目标日期区间的所有日期，关联活动表后直接计算每日人力总和，无需PHP循环。

代码示例

-- 替换start_date和end_date为你的目标区间
WITH RECURSIVE date_range AS (
    SELECT '2024-01-01' AS day_date  -- 区间起始日期
    UNION ALL
    SELECT DATE_ADD(day_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE day_date < '2024-01-31'  -- 区间结束日期
)
SELECT 
    dr.day_date,
    -- 累加所有符合条件的活动当日人力，取整
    ROUND(SUM(
        IF(
            dr.day_date BETWEEN wp.start AND wp.finish,
            wp.manhours / DATEDIFF(wp.finish, wp.start) / 8,
            0
        )
    ), 0) AS daily_headcount
FROM date_range dr
LEFT JOIN workpacks wp
    ON dr.day_date BETWEEN wp.start AND wp.finish
    AND wp.project = 9000 
    AND wp.wpid = 45 
    AND wp.finish > CURDATE()
GROUP BY dr.day_date
ORDER BY dr.day_date;

说明

• 递归CTEdate_range生成指定区间内的所有连续日期
• 左关联workpacks表，筛选符合项目、活动ID且未结束的活动
• 对每个日期，计算所有在运行中的活动当日分摊的人力，求和后取整
• 一次查询即可拿到所有日期的人力数据，直接用于生成直方图

方案2：PHP优化方案（避免逐日期查询） #

如果不想使用复杂的MySQL递归，可以先一次性获取所有符合条件的活动，再在PHP内存中计算每日人力，比逐日期查数据库效率高很多。

步骤

1. 批量查询活动数据

SELECT `start`, `finish`, `manhours` 
FROM workpacks 
WHERE `project` = 9000 AND `wpid` = 45 AND `finish` > CURDATE();

2. 在PHP中生成日期区间数组

// 示例：生成2024-01-01到2024-01-31的日期数组
$startDate = new DateTime('2024-01-01');
$endDate = new DateTime('2024-01-31');
$dateRange = [];
while ($startDate <= $endDate) {
    $dateRange[] = $startDate->format('Y-m-d');
    $startDate->add(new DateInterval('P1D'));
}

3. 计算每日人力

$dailyHeadcount = array_fill_keys($dateRange, 0);
foreach ($activities as $activity) {
    $actStart = new DateTime($activity['start']);
    $actFinish = new DateTime($activity['finish']);
    $days = $actStart->diff($actFinish)->days;
    $dailyHours = $activity['manhours'] / $days / 8;
    
    // 遍历活动覆盖的日期，累加人力
    $currentDate = clone $actStart;
    while ($currentDate <= $actFinish) {
        $dateStr = $currentDate->format('Y-m-d');
        if (isset($dailyHeadcount[$dateStr])) {
            $dailyHeadcount[$dateStr] += round($dailyHours, 0);
        }
        $currentDate->add(new DateInterval('P1D'));
    }
}

说明

• 仅需一次数据库查询，减少IO开销
• 内存运算速度快，且可灵活添加自定义逻辑（如排除节假日）

方案对比 #

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