Преобразование сложного запроса из Laravel в SQL
Я пытаюсь полностью перенести некоторую статистическую логику в SQL, чтобы значительно повысить производительность, однако это довольно сложно, и я не уверен, как этого добиться с помощью процедур / функций в SQL — или вообще возможно ли это.
Таблица статистики выглядит следующим образом:
Строка в таблице выглядит следующим образом:
Это код, который я пытаюсь преобразовать. $this->stats является красноречивой коллекцией всех строк в таблице:
return $this->stats
->groupBy(function ($stat) {
return $stat->created_at->format('Y-m-d');
})
->map(function ($stats) {
$times = [];
for ($hour = 0; $hour < 24; $hour++) {
$thisHour = $stats->filter(function ($stat) use ($hour) {
return (int) $stat->created_at->format('H') === $hour;
});
$times[$hour] = $thisHour->isNotEmpty()
? $thisHour->sum(function ($stat) {
return $stat->data->count;
}) : 0;
}
return $times;
});
Это выводит что-то вроде этого:
{
"2018-12-20": {
0: 54,
1: 87,
2: 18,
3: 44,
4: 35,
...
}
}
Таким образом, он сгруппирован по дате, и каждая дата содержит 0-23 (часы дня) с соответствующим значением (в данном случае суммирование свойства data-> count строки).
В другом запросе я смог получить суммирование свойства data-> count, используя это:
SELECT SUM(data->>\"$.count\") AS value
Так это вообще возможно сделать в SQL? Я представляю, что там будут столбцы даты, плюс столбец часов, то есть hour_0, hour_1 и т. Д. Со значениями внизу.
Любая помощь будет принята с благодарностью!
Решение
Первый шаг, который я хотел бы предпринять, чтобы понять, как это делает Laravel, — это включить ведение журнала запросов и выгрузку запроса;
DB::connection()->enableQueryLog();
// ... do your query
$queries = DB::getQueryLog();
Это позволит вам воссоздать запрос Eloquent в простом SQL. Оттуда вы можете работать над оптимизацией и конденсацией.
Другие решения
UDPATED
Поскольку вы сейчас включили структуру таблицы, следующий запрос должен дать вам необходимые результаты в табличном формате. Вам нужно разобрать результаты, чтобы конвертировать в json.
Обновленный SQL
SELECT DATE_FORMAT(stats.updated_at, "%Y-%m-%d") AS `date`,
HOUR(stats.updated_at) AS `hour`,
COUNT(stats.id) AS record_count
FROM stats
GROUP BY `date`, `hour`
ORDER BY 1 ASC, 2 ASC
Полученные табличные данные будут выглядеть примерно так.
detector