在SQL结果中（在mysql或perl结束时）填充空日期的最直接方法是什么？

我正在使用以下查询从mysql表中构建快速csv：
br />

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

数据中有日期间隔，但是：缺少零计数条目的天数最终以：

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

我拍了一个非常尴尬（几乎可以肯定是有问题的）的解决方法，每天数天一个月的时间和一些数学运算，但是在mysql或perl方面必须更简单一些。

有什么天才的想法/为什么对我这么傻呢？
有问题的日期范围有两个原因：

我知道每次都会寻找的日期范围
不幸的是，有问题的服务器不是我的服务器可以在atm上安装perl模块，并且它的状态已经足够衰弱，以至于它没有远程进行任何操作。Date ::-y安装
希望我可以选择多个答案！

#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

在这种特殊情况下，最好放一些在客户端检查，如果当前日期不是previos + 1，请添加一些附加字符串。

#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。也使我们不必担心转义特殊字符。

#3 楼

不傻，这不是MySQL要做的事情，插入空日期值。我在Perl中分两个步骤进行操作。首先，将查询中的所有数据加载到按日期组织的哈希中。然后，我创建一个Date :: EzDate对象并将其按天递增，所以...您的日期范围。

EzDate目前不在CPAN上，但是您可能会找到另一个perl mod，它将进行日期比较并提供日期增量器。

#4 楼

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

这样，您就不需要多余的表，也不需要提前获取所有
行。

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

您可以通过限制动态日历来进行调整，例如，从必须的min（date）到now（）。

KryItsov-Plz您能解释为什么我们需要max（n3）* 100吗？因为我们没有任何3位数字的日期，所以我想知道如何使用它。

–Pinal Patel
19年8月2日在11:05

#6 楼

由于您不知道差距在哪里，但是您想要从列表中的第一个日期到最后一个日期的所有值（大概），请执行以下操作：

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

嗯，事实证明，这比我想的要复杂得多。我希望这是有道理的！

#7 楼

我认为最简单的解决方案是创建一个Ordinal表，该表具有所需的最大行数（在您的情况下为31 * 3 = 93）。 br />接下来，对数据执行LEFT JOIN中的Ordinal。这是一个简单的案例，它在上周的每一天都得到了：为了清楚起见，我使用了SET @var = 'value'语法。三个月：

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

提示和评论：

查询中最困难的部分可能是确定在限制Ordinal时使用。相比之下，将整数序列转换为日期很容易。
您可以使用Ordinal满足所有不间断序列的需求。只需确保它包含的行数超过最长序列即可。
您可以在Ordinal上使用多个查询来查询多个序列，例如，在过去七（1-7）周的每个工作日（1-5）列出。 />您可以通过将日期存储在Ordinal表中来加快速度，但是灵活性较差。这样，无论您使用多少次，都只需要一个Ordinal表。不过，如果速度值得，请尝试使用INSERT INTO ... SELECT语法。

#8 楼

使用一些Perl模块进行日期计算，例如推荐的DateTime或Time :: Piece（5.10版的核心）。只需增加日期和打印日期，直到日期与当前日期相匹配就为0。

#9 楼

我不知道这是否行得通，但是如果您创建了一个包含所有可能日期的新表（如果日期范围发生不可预测的变化，这可能是这个主意……），然后然后在两个表上进行左联接？我想这是一个疯狂的解决方案，如果存在大量可能的日期，或者无法预测第一个和最后一个日期，但是如果日期范围是固定的或易于计算，那么这可能会起作用。 >

编程黑洞网