Was ist der einfachste Weg, um leere Daten in SQL-Ergebnissen (entweder auf mysql oder perl Ende) aufzufüllen?

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

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

und sie einfach in eine Datei in Perl über eine:

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

Es gibt jedoch Datenlücken in den Daten:

 | 2008-08-05 | 4 | | 2008-08-07 | 23 | 

Ich möchte die Daten auffüllen, um die fehlenden Tage mit Null-Zählung-Einträgen zu füllen, um mit zu enden:

 | 2008-08-05 | 4 | | 2008-08-06 | 0 | | 2008-08-07 | 23 | 

Ich schlug einen wirklich peinlichen (und fast sicher errorshaften) Workaround mit einer Reihe von Tagen pro Monat und etwas Mathe zusammen, aber es muss etwas auf der mysql– oder perl-Seite einfacher sein.

Irgendwelche genialen Ideen / Ohrfeigen dafür, warum ich so dumm bin?


Ich ging am Ende mit einer gespeicherten Prozedur, die aus verschiedenen Gründen eine temporäre Tabelle für den fraglichen Datumsbereich generiert:

  • Ich kenne den Datumsbereich, nach dem ich jedes Mal suche
  • Der Server in Frage war leider nicht, dass ich Perl-Module auf atm installieren kann, und der Zustand davon war so heruntergekommen, dass es nichts entfernt von Date :: – y installiert hatte

Die perl Datum / DateTime-iterierenden Antworten waren auch sehr gut, ich wünschte ich könnte mehrere Antworten auswählen!

Wenn Sie so etwas auf der Serverseite benötigen, erstellen Sie normalerweise eine Tabelle, die alle möglichen Daten zwischen zwei Zeitpunkten enthält, und dann verlassen Sie diese Tabelle mit Abfrageergebnissen. Etwas wie das:

 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 

In diesem speziellen Fall wäre es besser, ein wenig auf die Client-Seite zu überprüfen, wenn das aktuelle Datum nicht previos + 1 ist, fügen Sie einige zusätzliche Zeichenfolgen hinzu.

Als ich mich mit diesem Problem beschäftigen musste, um fehlende Daten zu ergänzen, habe ich tatsächlich eine Referenztabelle erstellt, die nur alle Daten enthielt, an denen ich interessiert bin, und der Datentabelle im Datumsfeld beigetreten war. Es ist grob, aber es funktioniert.

 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; 

Was die Ausgabe betrifft , würde ich SELECT INTO OUTFILE verwenden, anstatt die CSV manuell zu generieren. Lässt uns keine Sorgen mehr über Sonderzeichen.

nicht dumm, das ist nicht etwas, was MySQL tut, und fügt die leeren Datumswerte ein. Ich mache das in Perl mit einem zweistufigen process. Laden Sie zuerst alle Daten aus der Abfrage in einen nach Datum sortierten Hash. Dann erstelle ich ein Date :: EzDate-Objekt und inkrementiere es bis zum Tag, also …

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

Das Enddatum ist ein anderes EzDate-Objekt oder eine Zeichenfolge, die das Ende des Datumsbereichs enthält.

EzDate ist momentan nicht in CPAN, aber Sie können wahrscheinlich eine andere Perl-Mod finden, die Datumsvergleiche durchführt und einen Datumsinkrementierer bereitstellt.

Sie könnten ein DateTime- Objekt verwenden:

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

Der obige Code bewirkt, dass das letzte gedruckte Datum in einem DateTime Objekt $dt gespeichert wird. Wenn das aktuelle Datum mehr als einen Tag in der Zukunft liegt, erhöht es $dt um einen Tag (und druckt eine Zeile in CSV ). bis es das gleiche wie das aktuelle Datum ist.

Auf diese Weise benötigen Sie keine zusätzlichen Tabellen und müssen nicht alle Zeilen im Voraus abrufen.

Da Sie nicht wissen, wo die Lücken sind, und doch wollen Sie alle Werte (vermutlich) vom ersten Datum in Ihrer Liste bis zum letzten, machen Sie etwas wie:

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

Hmm, das stellte sich als komplizierter heraus, als ich dachte. Ich hoffe, es macht Sinn!

Ich denke, die einfachste allgemeine Lösung für das Problem wäre, eine Ordinal Tabelle mit der höchsten Anzahl von Zeilen zu erstellen, die Sie benötigen (in Ihrem Fall 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 

Als nächstes machen Sie einen LEFT JOIN von Ordinal auf Ihre Daten. Hier ist ein einfacher Fall, der jeden Tag in der letzten Woche kommt:

 SELECT CURDATE() - INTERVAL `n` DAY AS `day` FROM `Ordinal` WHERE `n` < = 7 ORDER BY `n` ASC 

Die zwei Dinge, die Sie dazu ändern müssen, sind der Startpunkt und das Intervall. Ich habe SET @var = 'value' Syntax für Klarheit verwendet.

 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; 

Der endgültige Code würde also ungefähr so ​​aussehen, wenn Sie sich anmelden würden, um die Anzahl der Nachrichten pro Tag in den letzten drei Monaten zu erhalten:

 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` 

Tipps und Kommentare:

  • Der wahrscheinlich schwierigste Teil Ihrer Abfrage war die Bestimmung der Anzahl der Tage, die bei der Begrenzung der Ordinal . Im Vergleich dazu war es einfach, diese ganzzahlige Sequenz in Daten umzuwandeln.
  • Sie können Ordinal für alle Anforderungen Ihrer ununterbrochenen Sequenz verwenden. Stellen Sie nur sicher, dass es mehr Zeilen als Ihre längste Sequenz enthält.
  • Sie können mehrere Abfragen auf Ordinal für mehrere Folgen verwenden, z. B. für jeden Wochentag (1-5) für die letzten sieben (1-7) Wochen.
  • Sie könnten es schneller machen, indem Sie Daten in Ihrer Ordinal speichern, aber es wäre weniger flexibel. Auf diese Weise brauchen Sie nur einen Ordinal , egal wie oft Sie ihn benutzen. Wenn die Geschwindigkeit es wert ist, versuchen Sie die Syntax INSERT INTO ... SELECT .

Ich hoffe, Sie werden den Rest herausfinden.

 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 

Mit

 select n3.num*100+n2.num*10+n1.num as date 

Sie erhalten eine Spalte mit Zahlen von 0 bis max (n3) * 100 + max (n2) * 10 + max (n1)

Da hier n3 maximal 3 ist, gibt SELECT 399 zurück, plus 0 -> 400 Datensätze (Datum im Kalender).

Sie können Ihren dynamischen Kalender optimieren, indem Sie ihn zum Beispiel von min (date) bis jetzt () begrenzen.

Verwenden Sie ein Perl-Modul, um Datumsberechnungen durchzuführen, wie empfohlene DateTime oder Time :: Piece (Core von 5.10). Erhöhen Sie einfach das Datum und das Druckdatum und 0 bis das Datum dem aktuellen Wert entspricht.

Ich weiß nicht, ob das funktionieren würde, aber wie wäre es, wenn Sie eine neue Tabelle erstellen würden, die alle möglichen Daten enthält (das könnte das Problem bei dieser Idee sein, wenn sich der Datenbereich unvorhersehbar ändert …) und Machst du dann einen Link auf den beiden Tischen? Ich denke, es ist eine verrückte Lösung, wenn es eine große Anzahl von möglichen Daten gibt, oder keine Möglichkeit, das erste und das letzte Datum vorherzusagen, aber wenn der Bereich der Daten entweder fest oder einfach zu erarbeiten ist, könnte dies funktionieren.