如何将MySQL临时表月度数据拆分为4段并计算每段日均时长？

嘿，这个需求我刚好折腾过类似的，给你两种靠谱方案，MySQL直接统计或者PHP处理数据，看你哪个顺手～

MySQL 直接统计方案 #

如果想直接在数据库层面搞定，核心思路是给每个月的每一天分配一个固定的组号（1-4），然后按「年月+组号」分组计算日均时长。

具体SQL可以这么写（替换成你的临时表和字段名就行）：

SELECT
    DATE_FORMAT(your_date_column, '%Y-%m') AS month,
    CEIL(DAYOFMONTH(your_date_column) / (DAY(LAST_DAY(your_date_column)) / 4)) AS group_num,
    AVG(your_duration_column) AS avg_daily_duration
FROM
    your_temp_table
GROUP BY
    month, group_num
ORDER BY
    month, group_num;

代码解释 #

• DATE_FORMAT(your_date_column, '%Y-%m')：把日期转成「年月」格式，用来按月份分组
• CEIL(DAYOFMONTH(...) / (DAY(LAST_DAY(...)) /4))：核心的分组逻辑——先算出当月总天数，再把天数分成4段，用CEIL向上取整给每一天分配组号，不管当月是28/30/31天，最终都会得到1-4这4个组
• 最后按「年月+组号」分组，计算每组的平均时长就是该组的日均时长

PHP 处理方案 #

如果觉得SQL逻辑绕，用PHP处理会更直观，尤其是你本来就要用PHP对接数据的话。假设你已经从数据库拿到了按日统计的时长数据（比如每天的日期和对应时长），可以这么做：

// 假设$dailyStats是从数据库获取的按日统计数组，结构示例：
// $dailyStats = [
//     ['date' => '2024-01-01', 'duration' => 120],
//     ['date' => '2024-01-02', 'duration' => 150],
//     // ... 更多日期数据
// ];

// 第一步：把每日数据按「年月」分组
$monthlyData = [];
foreach ($dailyStats as $day) {
    $monthKey = date('Y-m', strtotime($day['date']));
    if (!isset($monthlyData[$monthKey])) {
        $monthlyData[$monthKey] = [];
    }
    $monthlyData[$monthKey][] = $day;
}

// 第二步：每个月拆成4组，计算日均时长
$finalResult = [];
foreach ($monthlyData as $month => $days) {
    // 用array_chunk把当月天数分成4组，自动处理不均分的情况（比如31天会分成8/8/8/7）
    $fourGroups = array_chunk($days, ceil(count($days) / 4));
    
    foreach ($fourGroups as $groupIndex => $group) {
        // 计算组内总时长
        $totalDuration = array_sum(array_column($group, 'duration'));
        // 日均时长 = 总时长 / 组内天数
        $avgDuration = $totalDuration / count($group);
        
        $finalResult[] = [
            'month' => $month,
            'group_num' => $groupIndex + 1, // 组号从1开始
            'avg_daily_duration' => round($avgDuration, 2) // 保留两位小数，按需调整
        ];
    }
}

// 输出结果，或者传给前端/存储
print_r($finalResult);

方案对比 #

• MySQL方案：适合数据量较大的场景，数据库直接统计减少PHP的计算压力，效率更高
• PHP方案：逻辑更易懂，后续如果要调整分组规则（比如自定义每组的天数范围），改代码会更灵活，适合数据量不大或者需要额外自定义处理的情况

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