Блог

Я выполняю запрос mysql для подсчета количества записей каждую минуту и построения графика результата. Выводимый результат показывает только временные метки, в которых сумма не равна нулю. Это приводит к тому, что мой график выдает неправильные результаты в виде пробелов во времени, когда он рисует линию между двумя точками. Я хотел бы возвращать каждую минуту и количество значений.

Есть ли способ построить этот запрос без необходимости выполнять цикл и несколько запросов? Или, если мне нужен цикл, есть ли простой способ перебирать каждую минуту между двумя временными метками?

Это мой запрос:

 SELECT UNIX_TIMESTAMP(timestamp) AS timestamp, count(*) AS total  
FROM table  
WHERE timestamp <= '2011-04-22 11:00' AND timestamp >= '2011-04-20 10:00'  
GROUP BY MINUTE(timestamp)
  

То, что вы хотите сделать, это использовать таблицу «tally» или «numbers». По сути, вы создаете таблицу, содержащую один столбец целых чисел с числами, увеличивающимися от 1 до очень большого числа. Итак, если таблица подсчета содержит 1000 записей, то в таблице указаны числа от 1 до 1000. Вы захотите добавить в нее больше чисел, чем 1000, но, надеюсь, вы поняли идею.

Как только у вас будет таблица подсчета (нажмите здесь, чтобы просмотреть скрипт), вы должны выбрать ее в своем запросе, где numtablevalue значение находится между 1 и enddate - startdate (в минутах). И затем группируйте по startdate numtablevalue .

Итак, что-то вроде этого (псевдо SQL) должно работать:

 SELECT 'startdate'   INTERVAL tt.id MINUTE, COUNT(t1.timestampfield)
FROM tallytable AS tt
INNER JOIN
table AS t1
ON 'startdate'   INTERVAL tt.id MINUTE = t1.timestampfield
WHERE
tt.id BETWEEN 1 AND GETMINUTES('enddate' - 'begindate')
GROUP BY 'startdate'   INTERVAL tt.id MINUTE
  

В основном мы выбираем числа от 1 до количества секунд между двумя датами. Затем мы используем startdate tt.id , чтобы получить каждую секунду между датами. Затем мы объединяем и группируем по этому, чтобы найти количество записей из t1 , которые попадают в каждую минуту, как выбрано с помощью таблицы чисел. Я надеюсь, что это имеет смысл.

Есть несколько вещей, на которые вы, возможно, захотите обратить внимание:

• Это приведет к получению times > startdate (в отличие от >= startdate)
• Будьте осторожны, если с вашими временными метками связаны секунды, поскольку 5:12 PM они могут быть не равны 5:12:30 PM

 select i.`minute`,count(minute(timestamp)) as total from (
select tmp.digit   tmp2.digit * 10 as `minute`
from (
select 0 as digit union all 
select 1 union all 
select 2 union all 
select 3 union all 
select 4 union all 
select 5 union all 
select 6 union all 
select 7 union all 
select 8 union all 
select 9
) as tmp
cross join (
select 0 as digit union all 
select 1 union all 
select 2 union all 
select 3 union all 
select 4 union all 
select 5
) as tmp2 ) as i
left join table
on minute(tb.timestamp) = i.`minute`
and timestamp between '2011-04-20 10:00' and '2011-04-22 11:00' 
group by i.`minute`
  

Этот запрос выдаст вам 60 записей (минут от 0 до 59) с общим числом за каждую минуту без создания другой таблицы для хранения целочисленных значений.