SQL для группировки по таймфрейму с пустыми кадрами
Моя цель состоит в том, чтобы создать SQL-запрос, который подсчитывает все элементы за определенный период времени (например, 5 минут).
Это мой код до сих пор:
SELECT FROM_UNIXTIME(FLOOR(timestamp_stop/5*60)*(5*60), '%h:%i') AS timekey, timestamp_stop, count(item) AS performance
FROM task
WHERE done = 1
GROUP BY timekey
ORDER BY timestamp_stop ASC
Это прекрасно работает, но не включает временные рамки, в которых нет записей в базе данных.
Я хотел бы также получить эти 0-count-единицы, до текущего времени.
В настоящее время у меня нет простого / элегантного решения в моей голове. Есть идеи?
Небольшая постобработка в php также возможна.
Решение
Как упоминал Гордон, вам, вероятно, понадобится вторичная таблица в качестве основы для ВСЕХ 5-минутных интервалов. Я сделал то же самое с запросом для самостоятельной сборки с использованием переменных MySQL.
select
YourTable.WhateverFields
from
( select
@startTime RangeStart,
@startTime := date_add( @startTime, interval 5 MINUTE ) RangeEnd
from
( select @startTime := '2014-10-20' ) sqlvars,
AnyTableThatHasAsManyDaysYouExpectToReport
limit
12 * numberOfHoursYouNeed * numberOfDaysYouNeed ) DynamicTimeRange
LEFT JOIN YourTable
on YourTable.DateTimeField >= DynamicTimeRange.RangeStart
AND YourTable.DateTimeField < DynamicTimeRange.RangeEnd
Так, в этом примере самый внутренний объявляет переменную «startTime» до 20 октября 2014, которая по умолчанию равна 12:00:00. Затем один из них создает результирующий набор из двух столбцов для RangeStart и RangeEnd и может выглядеть примерно так …
RangeStart RangeEnd
2014-10-20 00:00 2014-10-20 00:05
2014-10-20 05:00 2014-10-20 00:10
2014-10-20 10:00 2014-10-20 00:15
2014-10-20 05:00 2014-10-20 00:20
2014-10-20 20:00 2014-10-20 00:25
Ссылка на таблицу «AnyTableThatHasAsManyDaysYouExpectToReport» — это просто … любая таблица в вашей базе данных, в которой есть как минимум столько записей, сколько вам потребуется для создания 5-минутных интервалов в течение скольких часов и дней. Если вам нужен 1 день = 12 записей * 5 минут = 1 час * 24 часа = 24 * 12 = необходимо 288 записей. Если вам нужна неделя, пусть будет так … умножьте это на 7, чтобы в моем образце были только заполнители, чтобы прояснить цель …
Но с LEFT JOIN вы получаете все интервалы …
Другие решения
Если есть такие временные рамки, но where Предложение отфильтровывает записи, вы можете сделать:
SELECT FROM_UNIXTIME(FLOOR(timestamp_stop/5*60)*(5*60), '%h:%i') AS timekey,
timestamp_stop,
sum(item is not null and done = 1) AS performance
FROM task
GROUP BY timekey
ORDER BY timestamp_stop ASC;
Если у вас все еще есть пробелы, то вам нужно сгенерировать таблицу (или подзапрос), содержащую список временных периодов, которые вы хотите и использовать left join,
РЕДАКТИРОВАТЬ:
Подзапрос не нравится. Вы должны перечислить все значения времени. Что-то вроде:
SELECT q.timekey, t.timestamp_stop, coalesce(t.performance, 0) as performance
FROM (SELECT '00:00' as timekey UNION ALL
SELECT '00:05' UNION ALL
. . .
) q LEFT JOIN
(SELECT FROM_UNIXTIME(FLOOR(timestamp_stop/5*60)*(5*60), '%h:%i') AS timekey,
timestamp_stop,
COUNT(item) AS performance
FROM task
WHERE done = 1
GROUP BY timekey
) t
ON t.timekey = q.timekey
ORDER BY timestamp_stop ASC;