如何用Laravel Eloquent实现按年/季度/月的访问量分组求和查询

问题描述 #

现有记录班级访问量的数据库表statistics，表结构如下：

+-------------+------------------+------+-----+-------------------+-------------------+
| Field       | Type             | Null | Key | Default           | Extra             |
+-------------+------------------+------+-----+-------------------+-------------------+
| id          | bigint unsigned  | NO   | PRI | NULL              | auto_increment    |
| class_id    | bigint unsigned  | NO   | MUL | NULL              |                   |
| hits        | bigint unsigned  | NO   |     | 0                 |                   |
| hit_date    | timestamp        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| year        | year             | NO   |     | NULL              |                   |
| quarter     | tinyint unsigned | NO   |     | NULL              |                   |
| month       | tinyint unsigned | NO   |     | NULL              |                   |
| week        | tinyint unsigned | NO   |     | NULL              |                   |
| created_at  | timestamp        | YES  |     | NULL              |                   |
| updated_at  | timestamp        | YES  |     | NULL              |                   |
| deleted_at  | timestamp        | YES  |     | NULL              |                   |
+-------------+------------------+------+-----+-------------------+-------------------+

需要通过Laravel Eloquent实现以下统计查询：

1. 按年份分组的总访问量（SUM(hits) AS total_hits）
2. 按季度（含年份）分组的总访问量
3. 最近12个月按月分组的总访问量
4. 当前月份按日分组的总访问量（已实现，代码参考）：

Statistics::select(DB::raw("SUM(`hits`) as total_hits"))
    ->where('class_id', 1)
    ->where('year', now()->year)
    ->where('month', now()->month)
    ->get();

解决方案 #

1. 按年份分组的总访问量 #

利用表中已有的year字段分组，同时统计对应年份的总访问量，添加排序保证年份顺序：

$yearlyStats = Statistics::select(
        'year',
        DB::raw('SUM(hits) as total_hits')
    )
    ->where('class_id', 1) // 指定目标班级，可根据需求调整
    ->groupBy('year')
    ->orderBy('year', 'asc') // 按年份升序排列，适配图表展示
    ->get();

2. 按季度（含年份）分组的总访问量 #

同时按year和quarter分组，避免跨年度季度数据混淆：

$quarterlyStats = Statistics::select(
        'year',
        'quarter',
        DB::raw('SUM(hits) as total_hits')
    )
    ->where('class_id', 1)
    ->groupBy('year', 'quarter')
    ->orderBy('year', 'asc')
    ->orderBy('quarter', 'asc') // 先按年份排序，再按季度排序
    ->get();

返回结果包含year、quarter和total_hits字段，可直接生成"YYYY年第X季度"的图表标签。

3. 最近12个月按月分组的总访问量 #

先筛选最近12个月的数据，再按year和month分组保证月份连续性：

$last12MonthsStats = Statistics::select(
        'year',
        'month',
        DB::raw('SUM(hits) as total_hits')
    )
    ->where('class_id', 1)
    ->where('hit_date', '>=', now()->subMonths(12)) // 筛选最近12个月的数据
    ->groupBy('year', 'month')
    ->orderBy('year', 'asc')
    ->orderBy('month', 'asc')
    ->get();

如果需要标准格式的月份标签，可在select中添加DB::raw("CONCAT(year, '-', LPAD(month, 2, '0')) as month_label")，生成类似"2023-10"的格式。

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