PHP Lang
  1. PHP Lang
  3. MySQL GROUP BY и заполняют пустые строки
Intereting Posts
Поле Option в HTML с PHP несколько строк в форме для одного и того же объекта в symfony2 Создание PDF с использованием TCPDF при вызове ajax Codeigniter: невозможно подключиться к серверу базы данных, используя предоставленные настройки PHP: доступ к многомерному массиву с помощью foreach и вывод результатов в виде таблицы Регулярное выражение извлекает переменную JavaScript в PHP Вставка значения в несколько таблиц ОТЧЕТ НАЗАД в PHP или JAVASCRIPT? Журнальный журнал запросов Laravel Eloquent Symfony2: получение списка пользовательских ролей в FormBuilder Эхо очень большое число Лучший способ хранения списка идентификаторов пользователей Расширяется ли расширение mysqli в этой конфигурации php? Как написать PHP-код для вывода JSON из сканирования каталога? MySQL 5.5 до 5.7 прекратил использование индексов

MySQL GROUP BY и заполняют пустые строки

Я уверен, что на это уже был дан ответ, но я не могу найти его в деталях, которые мне нужны.

Для аналитической системы мне нужно иметь возможность группировать строки и возвращать их на диаграмму, сгруппированные по минутам , часам, день, месяц или год. Я правильно работаю (пример кода ниже). 

SELECT COUNT( DISTINCT user_id ) , `hour` , `timestamp` FROM tracking_request WHERE site_id = '3' AND `timestamp` < '2011-08-31 04:05:45' AND `timestamp` > '2011-08-29 22:00:00' GROUP BY `hour` , `day` , `month` , `year` ORDER BY `timestamp` ASC

Проблема в том, что, как и большинство диаграмм, мне нужно заполнить пробелы, где нет данных (например, никаких строк за последние 3 минуты). Я читал о создании «таблицы календаря» и присоединении к этим данным, но как я могу сделать это эффективно для каждой шкалы (например, год будет намного проще, чем минута, так как в таблице потребуется много строк)? Если это помогает, есть столбец для каждого в таблице (например, вы видите, что есть «час», «день» и т. Д.).

РЕДАКТИРОВАТЬ:

Я закончил тем, что использовал PHP для этого, используя пустой массив, а затем заполнил его. Если бы кто-нибудь мог подумать обо всем (или в основном) SQL-решении для этого, это было бы более удивительным.

В этом ответе я расскажу, как создавать таблицы календаря.

Создайте три таблицы в течение нескольких дней, часов и минут: 

 CREATE TABLE days ( day DATE, PRIMARY KEY (day) ) CREATE TABLE hours ( hour INT, PRIMARY KEY (hour) ) CREATE TABLE minutes ( minute INT, PRIMARY KEY (minute) )

Заполните таблицу часов цифрами от 0 до 23 и таблицей минут с цифрами от 0 до 59. Чтобы заполнить таблицу дней, вы можете создать следующую процедуру: 

 CREATE PROCEDURE make_days(IN start_date DATE, IN end_date DATE) BEGIN DECLARE curr_date DATE; SET curr_date = start_date; WHILE curr_date <= end_date DO INSERT IGNORE INTO days(day) VALUES(curr_date); SET curr_date = DATE_ADD(curr_date, INTERVAL 1 DAY); END WHILE; END

Вы можете вызвать эту процедуру для создания таких дней: 

 CALL make_days('2011-01-01','2012-12-31');

Теперь вы можете создавать значения за каждую минуту в заданный интервал времени, используя запрос, подобный следующему: 

 SELECT YEAR(day) AS year, MONTH(day) AS month, DAYOFMONTH(day) AS day, hour, minute FROM days, hours, minutes WHERE CAST(CONCAT(day,' ',hour,':',minute) AS DATETIME) BETWEEN '2011-08-31 22:00' AND '2011-09-01 10:00' ORDER BY year, month, day, hour, minute

Обычно это делается в хранилищах данных, имея размер даты, который содержит список всех возможных дат. Вы выполняете ВНЕШНЮЮ СОБЛЮДЕНИЕ к размеру даты, а COALESCE – к 0.