Какой самый простой способ заполнить пустые даты в sql-результатах (на mysql или perl-конце)?

Я создаю быстрый csv из таблицы mysql с запросом типа:

select DATE(date),count(date) from table group by DATE(date) order by date asc; 

и просто сбрасывая их в файл в perl через:

 while(my($date,$sum) = $sth->fetchrow) { print CSV "$date,$sum\n" } 

Однако в данных есть пробелы в дате:

 | 2008-08-05 | 4 | | 2008-08-07 | 23 | 

Я хотел бы заполнить данные, чтобы заполнить недостающие дни нулевым количеством записей, чтобы в итоге:

 | 2008-08-05 | 4 | | 2008-08-06 | 0 | | 2008-08-07 | 23 | 

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

Любые идеи гения / пощечины в лице, почему я так глуп?


Я закончил с хранимой процедурой, которая создала временную таблицу для диапазона дат, о котором идет речь по нескольким причинам:

  • Я знаю диапазон дат, который я буду искать каждый раз
  • Сервер, о котором идет речь, к сожалению, не был тем, что я могу установить perl-модули на atm, и состояние его было достаточно ветхим, чтобы у него не было ничего удаленного Date ::-y

Ответы perl Date / DateTime-iterating также были очень хорошими, мне хотелось бы выбрать несколько ответов!

Когда вам нужно что-то подобное на стороне сервера, вы обычно создаете таблицу, которая содержит все возможные даты между двумя точками во времени, а затем левая присоединяется к этой таблице с результатами запроса. Что-то вроде этого:

 create procedure sp1(d1 date, d2 date) declare d datetime; create temporary table foo (d date not null); set d = d1 while d <= d2 do insert into foo (d) values (d) set d = date_add(d, interval 1 day) end while select foo.d, count(date) from foo left join table on foo.d = table.date group by foo.d order by foo.d asc; drop temporary table foo; end procedure 

В этом конкретном случае было бы лучше поставить небольшую проверку на стороне клиента, если текущая дата не предшествует + 1, добавьте некоторые строки добавления.

Когда мне приходилось решать эту проблему, чтобы заполнить отсутствующие даты, я на самом деле создал ссылочную таблицу, содержащую только все даты, которые мне интересны, и присоединился к таблице данных в поле даты. Это грубо, но это работает.

 SELECT DATE(r.date),count(d.date) FROM dates AS r LEFT JOIN table AS d ON d.date = r.date GROUP BY DATE(r.date) ORDER BY r.date ASC; 

Что касается вывода, я бы просто использовал SELECT INTO OUTFILE вместо создания CSV вручную. Не оставляет нам возможности беспокоиться о том, чтобы ускользнуть от специальных персонажей.

не глупый, это не то, что делает MySQL, вставляя пустые значения даты. Я делаю это в perl с двухэтапным процессом. Сначала загрузите все данные из запроса в hash, организованный по дате. Затем я создаю объект Date :: EzDate и увеличиваю его на день, поэтому …

 my $current_date = Date::EzDate->new(); $current_date->{'default'} = '{YEAR}-{MONTH NUMBER BASE 1}-{DAY OF MONTH}'; while ($current_date <= $final_date) { print "$current_date\t|\t%hash_o_data{$current_date}"; # EzDate provides for automatic stringification in the format specfied in 'default' $current_date++; } 

где конечная дата - другой объект EzDate или строка, содержащая конец диапазона дат.

EzDate сейчас не работает в CPAN, но вы, вероятно, можете найти еще один мотив perl, который будет сравнивать дату и предоставить инкремент даты.

Вы можете использовать объект DateTime :

 use DateTime; my $dt; while ( my ($date, $sum) = $sth->fetchrow ) { if (defined $dt) { print CSV $dt->ymd . ",0\n" while $dt->add(days => 1)->ymd lt $date; } else { my ($y, $m, $d) = split /-/, $date; $dt = DateTime->new(year => $y, month => $m, day => $d); } print CSV, "$date,$sum\n"; } 

Что делает вышеприведенный код, так это сохранение последней печатной даты, хранящейся в объекте DateTime $dt , и когда текущая дата больше чем на один день в будущем, она увеличивает на $dt на один день (и печатает его в CSV ) пока он не станет таким же, как текущая дата.

Таким образом, вам не нужны дополнительные таблицы, и вам не нужно забирать все ваши строки заранее.

Поскольку вы не знаете, где пробелы, и все же вы хотите, чтобы все значения (предположительно) с первой даты в вашем списке до последнего, сделайте что-то вроде:

 use DateTime; use DateTime::Format::Strptime; my @row = $sth->fetchrow; my $countdate = strptime("%Y-%m-%d", $firstrow[0]); my $thisdate = strptime("%Y-%m-%d", $firstrow[0]); while ($countdate) { # keep looping countdate until it hits the next db row date if(DateTime->compare($countdate, $thisdate) == -1) { # counter not reached next date yet print CSV $countdate->ymd . ",0\n"; $countdate = $countdate->add( days => 1 ); $next; } # countdate is equal to next row's date, so print that instead print CSV $thisdate->ymd . ",$row[1]\n"; # increase both @row = $sth->fetchrow; $thisdate = strptime("%Y-%m-%d", $firstrow[0]); $countdate = $countdate->add( days => 1 ); } 

Хм, это оказалось сложнее, чем я думал, что это будет .. Надеюсь, это имеет смысл!

Я думаю, что самым простым общим решением проблемы было бы создание таблицы Ordinal с наибольшим количеством строк, которые вам нужны (в вашем случае 31 * 3 = 93).

 CREATE TABLE IF NOT EXISTS `Ordinal` ( `n` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`n`) ); INSERT INTO `Ordinal` (`n`) VALUES (NULL), (NULL), (NULL); #etc 

Затем сделайте LEFT JOIN из Ordinal на свои данные. Вот простой случай, получающий каждый день на прошлой неделе:

 SELECT CURDATE() - INTERVAL `n` DAY AS `day` FROM `Ordinal` WHERE `n` <= 7 ORDER BY `n` ASC 

Две вещи, которые вам нужно изменить, это начальная точка и интервал. Я использую SET @var = 'value' для ясности.

 SET @end = CURDATE() - INTERVAL DAY(CURDATE()) DAY; SET @begin = @end - INTERVAL 3 MONTH; SET @period = DATEDIFF(@end, @begin); SELECT @begin + INTERVAL (`n` + 1) DAY AS `date` FROM `Ordinal` WHERE `n` < @period ORDER BY `n` ASC; 

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

 SELECT COUNT(`msg`.`id`) AS `message_count`, `ord`.`date` FROM ( SELECT ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH) + INTERVAL (`n` + 1) DAY AS `date` FROM `Ordinal` WHERE `n` < (DATEDIFF((CURDATE() - INTERVAL DAY(CURDATE()) DAY), ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH))) ORDER BY `n` ASC ) AS `ord` LEFT JOIN `Message` AS `msg` ON `ord`.`date` = `msg`.`date` GROUP BY `ord`.`date` 

Советы и комментарии:

  • Вероятно, самая сложная часть вашего запроса заключалась в определении количества дней использования при ограничении Ordinal . Для сравнения, преобразование этой целочисленной последовательности в даты было простым.
  • Вы можете использовать Ordinal для всех ваших потребностей в непрерывной последовательности. Просто убедитесь, что он содержит больше строк, чем ваша самая длинная последовательность.
  • Вы можете использовать несколько запросов в Ordinal для нескольких последовательностей, например, перечисляя каждый будний день (1-5) за последние семь (1-7) недель.
  • Вы могли бы сделать это быстрее, сохранив даты в таблице Ordinal , но это было бы менее гибким. Таким образом, вам нужен только один стол для Ordinal , независимо от того, сколько раз вы его используете. Тем не менее, если скорость стоит того, попробуйте синтаксис INSERT INTO ... SELECT .

Надеюсь, вы поймете остальное.

 select * from ( select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from (select 0 as num 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) n1, (select 0 as num 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) n2, (select 0 as num 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) n3, (select 0 as num 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) n4, (select 0 as num 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) n5 ) a where date >'2011-01-02 00:00:00.000' and date < NOW() order by date 

С

 select n3.num*100+n2.num*10+n1.num as date 

вы получите столбец с номерами от 0 до макс (n3) * 100 + max (n2) * 10 + max (n1)

Поскольку здесь max n3 равно 3, SELECT вернет 399, плюс 0 -> 400 записей (даты в календаре).

Вы можете настроить свой динамический календарь, ограничив его, например, с min (date), который вы должны сейчас ().

Используйте некоторый модуль Perl для выполнения расчетов по дате, например, рекомендуется использовать DateTime или Time :: Piece (kernel из 5.10). Просто добавьте дату и дату печати, а 0 до даты будет соответствовать текущему.

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

  • Выключение прямой косой черты в регулярном выражении
  • Как использовать переменную на стороне замены оператора замены Perl?
  • Почему фильтры источника Perl плохие, и когда их использовать?
  • Когда это полезное приложение \ G в регулярном выражении?
  • Как я могу подключиться к печати в Perl?
  • В чем разница между итерацией по файлу с помощью foreach или в Perl?
  • Powershell перегружает Perl binmode?
  • Как я могу анализировать даты и преобразовывать часовые пояса в Perl?
  • Какое регулярное выражение может соответствовать последовательностям одного и того же символа?
  • Как определить, имеет ли переменная число в Perl?
  • Как получить полный путь к скрипту Perl, который выполняется?
  • Давайте будем гением компьютера.