Laravel代码优化：单查询实现7日内玩家每日等级差值计算

1. 单SQL查询替代循环查询 #

原循环会执行7次SQL查询，效率低下，我们可以通过一次分组查询获取7天内所有玩家的每日统计数据，再整理成预期格式：

use Carbon\Carbon;

$startDate = now()->subDays(7)->startOfDay();
$endDate = now()->subDays(1)->endOfDay();

$players = $game->players()
    ->joinSub(
        function ($query) use ($startDate, $endDate) {
            $query->from('player_statistics')
                ->select(
                    'player_id',
                    DB::raw('DATE(created_at) as stat_date'),
                    DB::raw('MIN(level) as minLevel'),
                    DB::raw('MAX(level) as maxLevel'),
                    DB::raw('MAX(level) - MIN(level) as levelDifference')
                )
                ->whereBetween('created_at', [$startDate, $endDate])
                ->groupBy('player_id', 'stat_date');
        },
        'daily_stats',
        'players.id',
        '=',
        'daily_stats.player_id'
    )
    ->select('players.*', 'daily_stats.stat_date', 'daily_stats.minLevel', 'daily_stats.maxLevel', 'daily_stats.levelDifference')
    ->get()
    ->groupBy('id')
    ->map(function ($playerGroup) {
        $player = $playerGroup->first()->only(array_keys($playerGroup->first()->getAttributes()));
        unset($player['stat_date'], $player['minLevel'], $player['maxLevel'], $player['levelDifference']);
        
        $player['statistics'] = $playerGroup->reduce(function ($carry, $item) {
            $daysAgo = Carbon::parse($item->stat_date)->diffInDays(now(), false);
            $carry[$daysAgo] = [
                'minLevel' => $item->minLevel,
                'maxLevel' => $item->maxLevel,
                'levelDifference' => $item->levelDifference
            ];
            return $carry;
        }, []);
        
        return $player;
    })
    ->values();

return $players;

这段代码通过子查询一次性统计出7天内每个玩家每日的等级极值与差值，再通过分组整理成预期的嵌套结构，全程仅执行1次主SQL查询。

2. 能否省略withMin/withMax直接获取差值 #

完全可以！withMin和withMax是Eloquent提供的快捷封装，但我们可以直接在SQL层面计算差值，无需先查询极值再在PHP中运算：

比如单独查询某一天的玩家等级差值，可直接写：

$dayAgo = now()->subDays(1);
$players = $game->players()
    ->joinSub(
        function ($query) use ($dayAgo) {
            $query->from('player_statistics')
                ->select(
                    'player_id',
                    DB::raw('MAX(level) - MIN(level) as levelDifference')
                )
                ->whereDate('created_at', $dayAgo)
                ->groupBy('player_id');
        },
        'stats',
        'players.id',
        '=',
        'stats.player_id'
    )
    ->select('players.*', 'stats.levelDifference')
    ->get();

直接从数据库拿到计算好的差值，省去了withMin/withMax的额外查询与PHP层面的减法运算，效率更高。

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