如何使用sql查询优化代码

$collection->filter(function ($item) use ($i) {
  return $item->created_at->month == $i->month;
})->count();

我想用sql来代替集合上的filter函数，这样可以更快
函数如下：

$data = [];
    switch ($range) {
        //monthly
        case self::$timeRanges[0]:
            for ($i = Carbon::now()->copy()->subYear()->endOfMonth(); $i <= Carbon::now()->subMonth()->endOfMonth(); $i->addMonths(1)) {
                $data[$i->month] = $collection->filter(function ($item) use ($i) {
                    return $item->created_at->month == $i->month;
                })->count();
            }
            break;
        //weekly
        case self::$timeRanges[1]:
            $collection = $collection->where('created_at', '>=', Carbon::now()->subWeek()->endOfWeek()->subWeeks(5))->where('created_at', '<=', Carbon::now()->subWeek()->endOfWeek());
            for ($i = Carbon::now()->copy()->subWeek()->endOfWeek()->subWeeks(5); $i <= Carbon::now()->copy()->subWeek()->endOfWeek(); $i->addWeeks(1)) {
                $data[$i->weekOfMonth] = $collection->filter(function ($item) use ($i) {
                    return $item->created_at->weekOfYear == $i->weekOfYear;
                })->count();
            }
            break;
    }
    return ($data);

谢谢你的帮助，祝你有愉快的一天！