Laravel按天/小时过滤Timestamp并统计日均/时均数据方案咨询

针对你的需求，核心是利用数据库日期分组+聚合函数实现按小时/天的平均值统计，Laravel的Eloquent查询构建器可直接支持这类操作，以下分两种场景给出具体实现：

1. 按小时统计（每小时保留1条平均值记录） #

假设你的模型名为DataRecord，时间字段为created_at，需要计算平均值的字段为value（可根据实际字段修改）。

MySQL 版本 #

use Carbon\Carbon;
use App\Models\DataRecord;

// 获取近一月的每小时平均值数据（替换为subYear()可获取一年数据）
$hourlyData = DataRecord::query()
    ->whereBetween('created_at', [Carbon::now()->subMonth(), Carbon::now()])
    // 格式化时间到小时级别，作为分组依据
    ->selectRaw('DATE_FORMAT(created_at, "%Y-%m-%d %H:00:00") as hour_segment, AVG(value) as avg_value')
    ->groupBy('hour_segment')
    ->orderBy('hour_segment')
    ->get();

PostgreSQL 版本 #

PostgreSQL 使用DATE_TRUNC函数更简洁：

$hourlyData = DataRecord::query()
    ->whereBetween('created_at', [Carbon::now()->subMonth(), Carbon::now()])
    ->selectRaw('DATE_TRUNC(\'hour\', created_at) as hour_segment, AVG(value) as avg_value')
    ->groupBy('hour_segment')
    ->orderBy('hour_segment')
    ->get();

2. 按天统计（每天保留1条平均值记录） #

逻辑与按小时统计一致，仅分组粒度改为天：

MySQL 版本 #

// 获取近一年的每天平均值数据（替换为subMonth()可获取一月数据）
$dailyData = DataRecord::query()
    ->whereBetween('created_at', [Carbon::now()->subYear(), Carbon::now()])
    ->selectRaw('DATE(created_at) as day_segment, AVG(value) as avg_value')
    ->groupBy('day_segment')
    ->orderBy('day_segment')
    ->get();

PostgreSQL 版本 #

$dailyData = DataRecord::query()
    ->whereBetween('created_at', [Carbon::now()->subYear(), Carbon::now()])
    ->selectRaw('DATE_TRUNC(\'day\', created_at) as day_segment, AVG(value) as avg_value')
    ->groupBy('day_segment')
    ->orderBy('day_segment')
    ->get();

额外说明 #

• 若需统计多个字段的平均值，直接在selectRaw中追加AVG(another_field) as avg_another_field即可。
• 注意时区一致性：确保Laravel应用时区（config/app.php中的timezone）与数据库时区匹配，避免分组时间偏差。若存在时区差异，可在查询中使用CONVERT_TZ（MySQL）或AT TIME ZONE（PostgreSQL）转换时间。
• 性能优化：若数据量极大，建议给created_at字段添加索引，提升分组查询的效率。

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