Можно ли этот процесс выполнить одним SQL запросом?

Question

[Mokhirjon Naimov]

Есть таблица со столбцами: id, channel_id, participants_count, created_at.
И есть модель ChannelDataHistory для работы с этой таблицей.

Мне нужно получить сумму participants_count максимальной значении канала (channel_id) по дням за определенное промежуток времени.

Сейчас я делаю так:

// ...

// Определяю промежуток времени
$datetime_begin = Carbon::today()->subDays(13)->toDateTimeString();
$datetime_end = Carbon::today()->subDay()->toDateTimeString();

// Получаю все данные за определенное промежуток времени
// и группирую их хелпером коллекции `groupBy()` по дням
$data_history_by_days = ChannelDataHistory::whereBetween('created_at', [$datetime_begin, $datetime_end])
    ->orderBy('created_at', 'ASC')
    ->get()
    ->groupBy(function ($query) {
        return $query->created_at->format('d');
    });

// Получаю метки для графика в виде дней (..., 28, 29, 30, 1, 2, 3, 4, ...)
$chart_labels = $data_history_by_days->keys();

// Получаю сумму максимальных значений каналов по дням
$chart_dataset = $data_history_by_days->map(function ($grouped_by_days) {
    return $grouped_by_days->groupBy('channel_id')
        ->map(function ($grouped_by_channel_id) {
            return $grouped_by_channel_id->max('participants_count');
        });
    })
    ->map(function ($item) {
        return $item->sum();
    })
    ->values();
// ...

Все было бы хорошо если все это работала шустро :)
Медлит все это конечно же обработка полученных данных с помощью SQL. Можно ли все это выполнить одним (или может бить двумя) только SQL запросом или хотя бы как-то значительно оптимизировать.

P.S. Конечно же я использую кэширование на 30 минут, но после каждого 1-го, 2-го запроса после 30 минут падает страница.

Answer 1

[Вячеслав Плиско]

А почему вы не группируете в рамках самого запроса? Мне кажется, должно быть вроде такого запроса

ChannelDataHistory::whereBetween('created_at', $datetimes)
    ->groupBy('date')
    ->get([
        DB::raw('Date(created_at) as date'),
        DB::raw('MAX(participants_count) as maxCount'),
        .....
     ]);

Comments

[Mokhirjon Naimov]

Потому что я в SQL не pro :(, не понимаю как это сделать.

После ->get( ничего не понял, это что laravel 5.3/5.4? Я такого еще не видел.
Можете дать более подробную информацию?

[Вячеслав Плиско]

Mokhirjon Naimov: под рукой нет фреймворка чтобы проверить есть ли ещё такой метод, но это тоже самое что select()

Answer 2

[mletov]

SELECT dt, 
            SUM(mx) AS sm
FROM
(
    -- Максимальные значения по каждому каналу за каждый день
   SELECT channel_id, 
                CAST(created_at AS DATE) AS dt, 
                MAX(participants_count) AS mx
   FROM table
   WHERE created_at BETWEEN @dateStart AND @dateFinish
   GROUP BY channel_id, CAST(created_at AS DATE)
) AS t1
GROUP BY dt