检索并绘制一段时间内的数据

Question

我一次又一次地遇到这个问题；

情况 有一个（大）数据集，其中的记录与日期（甚至时间）相关，我需要随时间绘制这些数据。

问题 数据集仅包含有可用记录的日期（显然），但我还需要“空”日期才能随着时间的推移均匀地绘制多个系列。

工具 我通常使用 MySQL 和 PHP，最常将数据粘贴到 Excel 中以绘制图表等。

解决方法 到目前为止，我通常会创建一个 Excel 工作表并手动输入日期，并使用数据集上的 SumIf 确定适当的结果数量。 不过，这对我来说似乎有点愚蠢，并且在处理大量记录时并不真正可行（Excel 很快就会变得非常慢）。

我真的很好奇是否没有一个“真正的”更好的解决方案来做到这一点，也许已经在查询的构建中？

Answer 1

您的 Excel 数据文本中的日期是否看起来像日期，或者日期序列号是否格式化为显示为日期？

如果日期数据点是日期序列号，则绘图将随时间均匀地间隔 X 轴，而不管各个数据点之间的间隔如何

附图演示：第一列和绘图是存储为文本的“日期”，第二个日期序列号格式化为日期

Answer 2

一个明显的解决方案是创建一个包含该范围内每个日期的表，并选择与此相连的数据：

  CREATE TABLE days ( day DATE NOT NULL, PRIMARY KEY day );
  INSERT INTO days (day) VALUES (20110101);
  INSERT INTO days (day) VALUES (20110102);
  ....
  SELECT days.day, yourtable.*
  FROM days LEFT JOIN yourtable
     ON days.day=yourtable.day;

或者，您可以使用 PHP 注入缺失的日期（但我认为 jupaju 的代码相当有缺陷）：

  // ensure your qry returns DATE_FORMAT(day, '%Y-%m-%d') AS day
  $lastday='';
  while ($r=mysql_fetch_assoc($qry_result)) {
      if (!$lastday) $lastday=$r['day'];
      while ($r['day']>$lastday) {
          print $lastday . "\n";
          $lastday=add_day($lastday);
      }
      unset($r['day']);
      print $lastday . ',' . implode(',', $r) . "\n";
  }

  function add_day($day)
  {
     list($yr, $mo, $da)=explode('-',$day);
     $t=mktime(4,0,0,$mo, $da, $yr)+24*60*60; // NB not all days are 24 hours
     return date('Y-m-d',$t);
  }

Answer 3

使用 PHP 我会做这样的事情（假设 db-results 位于 $res 中，日期为 Ymd 作为键）：

$year = date('Y');
for ($month = 1; $month <= 12; $month++) {
    $last_day = date('t', mktime(0, 0, 0, 1, $month, $year));
    for ($day = 1; $day <= $last_day; $day++) {
        $key = sprintf('%4d-%02d-%02d', $year, $month, $day);
        if (!empty($res[$key])) {
            $output[$key] = $res[$key];
        } else {
            $output[$key] = 0;
        }
    }
}

这样你会得到一个数组 $output 具有值为 0 的空日期。