¿Cuál es la forma más sencilla de rellenar las fechas vacías en los resultados sql (en mysql o perl end)?

Cuando tuve que lidiar con este problema, para completar las fechas faltantes, en realidad creé una tabla de referencia que solo contenía todas las fechas que me interesan y se unió a la tabla de datos en el campo de fecha. Es crudo, pero funciona.

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;

En cuanto a la salida, solo usaría SELECCIONAR EN OUTFILE en lugar de generar el CSV a mano. Nos deja libres de preocuparnos por escapar de caracteres especiales también.

No es tonto, esto no es algo que MySQL hace, insertando los valores de fecha vacíos. Hago esto en perl con un proceso de dos pasos. Primero, cargue todos los datos de la consulta en un hash organizado por fecha. Luego, creo un objeto Date:: EzDate y lo incremento por día, así que...

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

Donde la fecha final es otro objeto EzDate o una cadena que contiene el final de su rango de fechas.

EzDate no está en CPAN en este momento, pero es probable que pueda encontrar otro mod de perl que hará la fecha compara y proporciona un incremento de fecha.

Podrías usar un objeto 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";
}

Lo que el código anterior hace es mantener la última fecha impresa almacenada en un DateTime object $dt, y cuando la fecha actual es más de un día en el futuro, aumenta $dt en un día (y lo imprime una línea a CSV) hasta que sea la misma que la fecha actual.

De esta manera no necesita mesas adicionales, y no necesita buscar todas sus filas por adelantado.

Dado que no sabe dónde están los huecos, y sin embargo desea todos los valores (presumiblemente) desde la primera fecha de su lista hasta la última, haga algo como:

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, eso resultó ser más complicado de lo que pensé que sería.. Espero que tenga sentido!

Creo que la solución general más simple al problema sería crear una tabla Ordinal con el mayor número de filas que necesite (en su caso 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

A continuación, haga un LEFT JOIN desde Ordinal en sus datos. Aquí hay un caso simple, cada día en la última semana:

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

Las dos cosas que tendría que cambiar sobre esto son el punto de partida y el intervalo. He utilizado la sintaxis SET @var = 'value' para mayor claridad.

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;

Así que el código final se vería algo así, si te unías para obtener el número de mensajes por día durante los últimos tres meses:

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`

Consejos y comentarios:

• Probablemente la parte más difícil de su consulta fue determinar el número de días a usar al limitar Ordinal. En comparación, transformar esa secuencia entera en fechas fue fácil.
• Puede usar Ordinal para todas sus necesidades de secuencia ininterrumpida. Solo asegúrese de que contiene más filas que su secuencia más larga.
• puede utilizar múltiples consultas en Ordinal para múltiples secuencias, por ejemplo enumerando cada día de la semana (1-5) durante las últimas siete (1-7) semanas.
• Podría hacerlo más rápido almacenando fechas en su tabla Ordinal, pero sería menos flexible. De esta manera, solo necesita una tabla Ordinal, sin importar cuántas veces la use. Aún así, si la velocidad vale la pena, pruebe la sintaxis INSERT INTO ... SELECT.

Espero que averigües el resto.

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

Con

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

Obtendrá una columna con números de 0 a max(n3)*100+max(n2)*10+max(n1)

Ya que aquí tenemos max n3 como 3, SELECT devolverá 399, más 0 -> 400 registros (fechas en el calendario).

Puedes ajustar tu calendario dinámico limitándolo, por ejemplo, desde min(date) que tienes hasta now().

Use algún módulo de Perl para hacer cálculos de fechas, como DateTime recomendado o Time::Piece (core de 5.10). Solo incremente la fecha e imprima la fecha y 0 hasta la fecha coincidirá con la actual.

No se si esto funcionaría, pero que tal si crearas una nueva tabla que contuviera todas las fechas posibles (ese podría ser el problema con esta idea, si el rango de fechas va a cambiar impredeciblemente...) y luego hacer una unión a la izquierda en las dos mesas? Supongo que es una solución loca si hay un gran número de fechas posibles, o no hay forma de predecir la primera y la última fecha, pero si el rango de fechas es fijo o fácil de resolver, entonces esto podría funcionar.