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

Я строю быстрый 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-повторяющиеся ответы также были очень хорошими, я хотел бы выбрать несколько ответов!

9 ответов

Решение

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

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

В этом конкретном случае было бы лучше поставить небольшую проверку на стороне клиента, если текущая дата не равна previos+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 с двухэтапным процессом. Сначала загрузите все данные из запроса в хэш, упорядоченный по дате. Затем я создаю объект 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), пока она не станет такой же, как текущая дата.

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

Я надеюсь, что вы разберетесь в остальном.

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 до max(n3)*100+max(n2)*10+max(n1)

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

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

Я думаю, что самым простым общим решением проблемы было бы создать 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 синтаксис.

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

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 );
}

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

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

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

Другие вопросы по тегам