在MySQL或Perl中，如何最简单地填充SQL结果中的空日期？

Question

我正在使用像这样的查询语句从mysql表中快速构建csv：

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模块，而且其状态已经越来越糟糕，没有任何类似Date::-y的东西

Perl Date/DateTime-迭代答案也非常好，我希望我能选择多个答案！

- user13196

Answer 1

当你需要在服务器端执行这样的操作时，通常会创建一个包含两个时间点之间所有可能日期的表格，然后将此表格左连接到查询结果中。类似于以下内容：

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，则添加一些附加字符串。

- GSerg

Answer 2

当我需要处理这个问题时，为了填补缺失的日期，我实际上创建了一个参考表，其中只包含我感兴趣的所有日期，并在日期字段上将数据表连接起来。这很简单，但却很有效。

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文件。这样我们就不用担心特殊字符的转义问题了。

- Aeon

Answer 3

这并不是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++;
}

其中final date是另一个EzDate对象或包含您日期范围结束的字符串。

EzDate目前不在CPAN上，但您可以找到其他Perl模块来进行日期比较并提供日期增量器。

- coffeepac

Answer 4

您可以使用DateTime对象：

您可以使用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行中），直到它与当前日期相同为止。

这样你就不需要额外的表格，也不需要提前获取所有的行。

- 8jean

Answer 5

我希望您可以解决其余的问题。

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条记录（日历中的日期）。

您可以通过限制最小日期为now()来调整动态日历。

- Igor Kryltsov

---

KryItsov - 请问您能解释一下为什么我们需要max(n3)*100吗？因为我们没有任何三位数的日期，所以我想知道如何使用它。 - Pinal Patel

Answer 6

我认为解决这个问题最简单的通用方法是创建一个“序数”表，该表具有您需要的最高行数（在您的情况下为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-7）的每个工作日（1-5）。
• 如果在Ordinal表中存储日期，则可以使其更快，但灵活性会降低。这样，您只需要一个Ordinal表，无论使用多少次都可以。但是，如果速度值得，可以尝试使用INSERT INTO ... SELECT语法。

- theazureshadow

Answer 7

由于您不知道空缺在哪里，但是您想要获取列表中从第一个日期到最后一个日期的所有值（可能是这样），请尝试执行以下操作：

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

嗯，这比我想象的要复杂得多...希望它有意义！

- castaway

Answer 8

使用一些Perl模块进行日期计算，如建议的DateTime或Time::Piece（自5.10版核心）。只需递增日期并打印日期和0，直到日期匹配当前日期。

- Alexandr Ciornii

Answer 9

我不确定这是否可行，但是如果您创建一个包含所有可能日期的新表（如果日期范围将不可预测地更改，则可能是该想法的问题...），然后在两个表上执行左连接，这样做如何？如果有大量可能的日期或者无法预测第一个和最后一个日期，那么我想这是一个疯狂的解决方案，但是如果日期范围是固定的或易于计算，那么这可能有效。

- Ben