Laravel Eloquent 如何按小时分组统计UptimeChecks事件数？

原查询仅拉取了原始明细行数据，缺少三个核心逻辑，因此无法输出聚合统计结果：

• 没有将checked_at按小时维度截断为统一分组键，无法匹配同小时的所有记录
• 没有搭配聚合计算逻辑，仅返回未加工的单条事件数据
• 没有设置分组规则，数据库会返回所有行而非按维度汇总的统计值

use Illuminate\Support\Facades\DB;

$hourlyEventStats = UptimeChecks::query()
    ->where('user_id', 1)
    ->where('monitor_id', 1)
    ->whereBetween('checked_at', [
        '2021-05-01 15:19:00',
        '2021-05-01 17:19:00'
    ])
    ->select(
        // 生成截断到小时的分组键，格式和预期的输出键完全对齐
        DB::raw("DATE_FORMAT(checked_at, '%Y-%m-%d %H:00:00') as hour_bucket"),
        // 条件统计：累加所有down事件的计数
        DB::raw("SUM(IF(event = 'down', 1, 0)) as down_events"),
        // 条件统计：累加所有up事件的计数
        DB::raw("SUM(IF(event = 'up', 1, 0)) as up_events")
    )
    ->groupBy('hour_bucket')
    ->orderBy('hour_bucket', 'asc')
    ->get()
    // 转换为预期的JSON结构
    ->keyBy('hour_bucket')
    ->map(fn($stat) => [
        'down_events' => (int) $stat->down_events,
        'up_events' => (int) $stat->up_events
    ]);

针对你给出的示例数据集，上述代码将直接输出和预期完全一致的结果：

{
  "2021-05-01 15:00:00": {
    "down_events": 3,
    "up_events": 1
  },
  "2021-05-01 16:00:00": {
    "down_events": 1,
    "up_events": 1
  }
}

• 所有聚合计算都在数据库层完成，无需将全量明细拉取到PHP内存中循环处理，大时间范围查询下性能远高于拉取明细后手动统计的方案
• 原查询中连续两个针对checked_at的范围判断，替换为whereBetween写法逻辑等价，代码更简洁
• 原查询绑定checked_at的排序逻辑在分组场景下会触发SQL错误，因为该字段不在select返回的列中，需替换为按分组后的hour_bucket字段排序
• 若使用PostgreSQL数据库，需将MySQL语法的DATE_FORMAT替换为TO_CHAR(checked_at, 'YYYY-MM-DD HH24:00:00')，其余聚合逻辑保持一致

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