如何在SQL結果中填入空白日期，無論是在MySQL或Perl端，最直接的方法是什麼？

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,$sumn"
}

然而，資料中存在日期間隔：

| 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中的日期/時間迭代答案也非常好，希望我可以選擇多個答案！

Answer 1

當我需要處理這個問題時，為了填補缺失的日期，我實際上創建了一個參考表，只包含我感興趣的所有日期，並在日期欄位上連接資料表。這很簡單粗暴，但它有效。

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。這樣我們就不必擔心轉義特殊字元的問題。

Answer 2

當您在伺服器端需要類似的東西時，通常會建立一個包含兩個時間點之間所有可能日期的表，然後將此表與查詢結果進行左連接。類似以下內容：

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

在這種特定情況下，最好在客戶端進行一些檢查，如果當前日期不是前一天的後一天，則添加一些附加字串。