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

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

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;

Что касается вывода, я бы просто использовал ВЫБРАТЬ В 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, который будет сравнивать даты и обеспечивать инкремент даты.

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

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

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, добавьте несколько дополнительных строк.

Вы можете использовать объект 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 до max (n3) * 100 + max (n2) * 10 + max (n1)

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

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