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

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



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


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



while(my($date,$sum) = $sth->fetchrow) {

    print CSV "$date,$sumn"

}


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



| 2008-08-05 |           4 | 

| 2008-08-07 |          23 |


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



| 2008-08-05 |           4 | 

| 2008-08-06 |           0 | 

| 2008-08-07 |          23 |


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



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




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




  • я знаю диапазон дат, который буду искать каждый раз

  • сервер, о котором идет речь, к сожалению, не был тем, что я могу установить модули perl на atm, и состояние его было достаточно ветхим, чтобы он не делал этого. есть что-нибудь удаленно дата:: - y установлен


Perl Date / DateTime-итерационные ответы также были очень хороши, я хотел бы выбрать несколько ответов!

680   9  

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

20  
2011-06-05 05:02:22

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

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 вручную. Оставляет нас свободными от беспокойства о побеге специальных символов, а также.

7  
2008-09-16 22:13:36

Не глупо, это не то, что делает 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 мод, который будет делать дату сравнивает и предоставляет инкрементор даты.

4  
2008-09-16 22:11:47

Можно использовать объект 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) до тех пор, пока она не будет совпадать с текущей датой.

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

4  
2008-09-16 22:37:10

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

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 );
}
Хм, это оказалось сложнее, чем я думал.. Надеюсь, это имеет смысл!
1  
2008-09-16 22:43:41

Я думаю, что самым простым общим решением проблемы было бы создать таблицу 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.
1  
2011-05-27 20:57:50

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

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)

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

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

1  
2013-05-01 15:23:52

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

0  
2008-09-16 22:15:06

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

-1  
2008-09-16 22:08:57

Comments

    Ничего не найдено.
Click here to cancel reply.