Wie fülle ich leere Datumsangaben in SQL-Ergebnissen ein, entweder auf MySQL- oder Perl-Seite, und was ist die einfachste Methode?

Question

Ich erstelle eine schnelle CSV-Datei aus einer MySQL-Tabelle mit einer Abfrage wie:

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

Speichern Sie sie dann direkt in einer Datei in Perl:

while(my($date,$sum) = $sth->fetchrow) {
    CSV „$date,$sumn“ drucken
}

Allerdings gibt es Datumslücken in den Daten:

| 2008-08-05 |
|. 2008-08-07 |. 23 |

Ich möchte die Daten auffüllen, fehlende Daten mit Null-Zählungseinträgen ergänzen und am Ende Folgendes erhalten:

| 2008-08-05 |
|. 06.08.2008 |
|. 2008-08-07 |. 23 |

Ich habe einen sehr umständlichen (mit ziemlicher Sicherheit fehlerhaften) Workaround zusammengeschustert, indem ich eine Reihe von Tagen im Monat und etwas Mathematik verwendet habe, aber es gibt definitiv einen direkteren Weg, entweder in MySQL oder Perl.

Irgendwelche genialen Ideen/Vorschläge, warum ich so dumm bin?

---

Am Ende habe ich mich aus mehreren Gründen für die Verwendung einer gespeicherten Prozedur zum Generieren einer temporären Tabelle zur Verarbeitung des erforderlichen Datumsbereichs entschieden:

• Ich kenne den Datumsbereich, nach dem ich jedes Mal suchen muss
• Leider kann dieser Server derzeit keine Perl-Module installieren und er ist in einem so schlechten Zustand, dass er nichts wie Date::-y installiert hat

Die Datums-/Uhrzeititeration in der Perl-Antwort ist auch sehr gut, ich wünschte, ich könnte mehrere Antworten auswählen!

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

在这种特定情况下，最好在客户端进行一些检查，如果当前日期不是前一天的后一天，则添加一些附加字符串。