sql - Eloquent：从所有具有最小时间戳的行中获取 AVG

Question

我想从每个类别的每个最小时间戳中获取用户 ID 及其平均分数。这是表结构

技能表

id | user_id | category | score | timestamp**
0....10............a................11........12
1....10............a................10........9
2....10............b................12........10
3....10............c................11........8
4....11............a................8........9
5....11............b................9........10
6....11............c................10........8
7....11............c................15........14

我想得到这样的结果：

user_id | AVG(score)
10........11 (average id: 1,2,3)
11........9 (average id: 4,5,6)

现在我为每个用户使用循环查询

foreach ($userIds as $id){
    // in some case I need to get from only specified Ids not all of them

    foreach ($category as $cat) {

        // get the minimum timestamp's score for each category
        $rawCategory = Skill::where("user_id", $id)->where("timestamp", "<=", $start)->where("category",$cat->id)->orderBy("timestamp", "desc")->first();

        if($rawCategory){
            $skillCategory[$cat->cat_name] = $rawCategory->score;
        }
    }

    //get the average score
    $average = array_sum($skillCategory) / count($skillCategory);
}

我想创建更好的 Eloquent 查询来以良好的性能（< 60 秒）获得这样的数据。有没有人遇到过类似的问题并解决了？如果是这样，你能把链接给我吗？谢谢

标签： sqleloquent