PHP如何将MySQL存储的访问时间戳按月份分组统计并生成指定结构数组

你现有SQL存在2个明显问题：

1. 直接用MONTH(aufruf_time)分组，仅返回1-12的数字，无法直接拿到你需要的Jan/Mar这类英文月份缩写；如果数据跨多年，还会把不同年份的同月数据合并统计，不符合业务逻辑。
2. 分组后直接ORDER BY aufruf_time排序逻辑不严谨，分组后非聚合、非分组字段的排序结果不可控。

第一步：优化SQL查询 #

场景1：不需要区分年份，统计所有时间的同月访问总和 #

SELECT 
  DATE_FORMAT(aufruf_time, '%b') as month_abbr,
  MONTH(aufruf_time) as month_num,
  count(*) as call_count
FROM profile_analytics 
WHERE PID = :pid
GROUP BY month_num, month_abbr
ORDER BY month_num ASC

场景2：需要区分年份，按「年-月」维度统计 #

SELECT 
  DATE_FORMAT(aufruf_time, '%b') as month_abbr,
  YEAR(aufruf_time) as year_num,
  MONTH(aufruf_time) as month_num,
  count(*) as call_count
FROM profile_analytics 
WHERE PID = :pid
GROUP BY year_num, month_num, month_abbr
ORDER BY year_num ASC, month_num ASC

第二步：组装为目标PHP数组 #

查询拿到结果集后，直接循环组装即可，下面的示例默认补全了全年12个月份，无访问的月份访问量默认记为0：

$db->bind("pid",$user['PID']);
$callData = $db->query("SELECT 
  DATE_FORMAT(aufruf_time, '%b') as month_abbr,
  MONTH(aufruf_time) as month_num,
  count(*) as call_count
FROM profile_analytics 
WHERE PID = :pid
GROUP BY month_num, month_abbr
ORDER BY month_num ASC");

// 初始化目标数组，预填全月份默认值
$calls = [];
$allMonths = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
foreach ($allMonths as $month) {
    $calls[$month] = ['calls' => 0];
}

// 填充查询到的真实访问数据
foreach ($callData as $item) {
    $calls[$item['month_abbr']] = [
        'calls' => $item['call_count']
    ];
}

最终得到的$calls数组完全符合你需要的结构。

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