Как проверить, есть ли в базе данных MySQL каждая минута дня?
У меня сейчас
SELECT *, COUNT(*) as `numrows`
FROM `data`
WHERE `datetime`
BETWEEN '2014-11-04 00:00:00' AND '2014-11-04 23:59:59'
AND `project_id` = 1
GROUP BY
DATE(`datetime`),
HOUR(`datetime`),
MINUTE(`datetime`)
Теперь это вернет все строки, причем некоторые из них имеют дубликаты, поэтому число будет равно 2. Но я хочу проверить, есть ли в каждой минуте строка, поэтому я могу убедиться, что измерения все еще поступают правильно.
Теперь я подумал о циклическом просмотре результатов, добавлении каждой возвращенной даты и времени в массив:
while($s = $select->fetch_assoc()) {
$availableTimes[] = $s['datetime'];
}
Затем перебираем массив, заполненный минутами дня, но я полагаю, это очень интенсивно, особенно если у вас есть 500, 1000, 5000 или даже 30000 идентификаторов. Каков будет лучший способ сделать это?
Решение
Если вы получаете только одну строку за каждую минуту дня (используя group by), вы можете просто использовать количество полученных строк.
у вас должно быть 24 * 60 результатов для вашего запроса каждый день (насколько я понимаю, это все, что вам нужно ..?)
Другие решения
Вы можете создать временную таблицу с часами и минутами, как это. Вы удаляете любые дубликаты, используя DISTINCT.
SELECT DISTINCT HOUR(`datetime`) AS HOUR, MINUTE(`datetime`) AS MINUTE
FROM `data`
WHERE `datetime`
BETWEEN '2014-11-04 00:00:00' AND '2014-11-04 23:59:59'
AND `project_id` = 1
затем вы можете посчитать количество строк во временной таблице, добавив select count (*) снаружи, как это
SELECT COUNT(*) FROM (
SELECT DISTINCT HOUR(`datetime`) AS HOUR, MINUTE(`datetime`) AS MINUTE
FROM `data`
WHERE `datetime`
BETWEEN '2014-11-04 00:00:00' AND '2014-11-04 23:59:59'
AND `project_id` = 1) AS TEMP
Если есть строки для всех часов и минут, результат должен быть 1440 (24 * 60).
Вы можете использовать такую процедуру, чтобы создать таблицу для каждой минуты.
CREATE PROCEDURE `make_intervals`(startdate timestamp, enddate timestamp, intval integer, unitval varchar(12))
BEGINdeclare thisDate timestamp;
declare nextDate timestamp;
set thisDate = startdate;drop temporary table if exists time_intervals;
create temporary table if not exists time_intervals
(
interval_start timestamp,
interval_end timestamp
);repeat
select
case unitval
when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
when 'SECOND' then timestampadd(SECOND, intval, thisDate)
when 'MINUTE' then timestampadd(MINUTE, intval, thisDate)
when 'HOUR' then timestampadd(HOUR, intval, thisDate)
when 'DAY' then timestampadd(DAY, intval, thisDate)
when 'WEEK' then timestampadd(WEEK, intval, thisDate)
when 'MONTH' then timestampadd(MONTH, intval, thisDate)
when 'QUARTER' then timestampadd(QUARTER, intval, thisDate)
when 'YEAR' then timestampadd(YEAR, intval, thisDate)
end into nextDate;
insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
set thisDate = nextDate;
until thisDate > enddate
end repeat;
endcall make_intervals('2014-11-04 00:00:00' ,'2014-11-04 23:59:59',1,'MINUTE');
чем осталось присоединить вашу таблицу к таблице timer_intervals и найти пропуски
Один из способов сделать это — создать диапазон временных строк, по одной на каждую минуту интересующего вас дня (1440 строк). Затем вы ВНЕШНЕЕ ВНЕШНИЙ СОЕДИНИТЕ свои данные к этому и посчитаете количество строк, найденных за это время в таблице данных.
SELECT aDateTime, COUNT(data.datetime)
FROM
(
SELECT DATE_ADD('2014-11-04 00:00:00', INTERVAL a.Mnt + b.Mnt * 10 + c.Mnt * 100 + d.Mnt * 1000 MINUTE) AS aDateTime
FROM (SELECT 0 AS Mnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
CROSS JOIN (SELECT 0 AS Mnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
CROSS JOIN (SELECT 0 AS Mnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
CROSS JOIN (SELECT 0 AS Mnt UNION SELECT 1) d
WHERE (a.Mnt + b.Mnt * 10 + c.Mnt * 100 + d.Mnt * 1000) < 1440
) sub0
LEFT OUTER JOIN `data`
ON sub0.aDateTime = data.datetime
AND data.project_id = 1
GROUP BY aDateTime