¿Cuál es la forma más directa de almohorar las fechas vacías en los resultados de SQL (en MySQL o PERL FIN?

Cuando tuve que lidiar con este problema, para completar las fechas faltantes, creé una tabla de referencia que solo contenía todas las fechas en que me interesa y se unió a la tabla de datos en el campo 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 seleccione en el aire libre en lugar de generar el CSV a mano. Nos deja libre de preocuparse por escapar de personajes especiales también.

Podría 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";
}

¿Cuál es el código anterior? Mantiene la última fecha impresa almacenada en A DateTime objeto $dt, y cuando la fecha actual es más de un día en el futuro, incrementa $dt por un día (e imprime una línea a CSV) hasta que sea lo mismo que la fecha actual.

De esta manera, no necesita mesas extra, y no necesita buscar todas sus filas con anticipación.

no es tonto, esto no es algo que lo haga MySQL, insertando los valores de la fecha vacía. 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 una fecha :: Ezdate Object e incrementa el 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 intervalo de fecha.

EzDate no está en CPAN en este momento, pero probablemente puede encontrar otro mod PERL que hará la fecha se compara y proporcione un incremental de fecha.

Espero que descubras 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)

Dado que aquí tenemos max N3 como 3, seleccione Devolverá 399, más 0 -> 400 registros (fechas en el calendario).

Puede ajustar su calendario dinámico al limitarlo, por ejemplo, desde MIN (FECHA) que tiene hasta ahora ().

Ya que no sabe dónde están las brechas y, sin embargo, usted desea todos los valores (presumiblemente) de la primera fecha en su lista hasta la última, haga algo así:

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, que 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 necesita (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, haz una LEFT JOIN de Ordinal a sus datos. Aquí hay un caso simple, obteniendo todos los días en la última semana:

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

Las dos cosas que necesitaría cambiar sobre esto son el punto de partida y el intervalo. He usado SET @var = 'value' Sintaxis 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;

Por lo tanto, el código final se vería como esto, si se estaba uniendo para obtener la cantidad de mensajes por día en 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 de usar al limitar Ordinal. En comparación, transformando esa secuencia entera en fechas fue fácil.
• Puede usar Ordinal para todas sus necesidades de secuencia ininterrumpida. Solo asegúrese de que contenga más filas que su secuencia más larga.
• Puede usar múltiples consultas en Ordinal para múltiples secuencias, por ejemplo, listar todos los días de la semana (1-5) durante las últimas siete (1-7) semanas.
• Puede hacerlo más rápido almacenando fechas en su tabla Ordinal, pero sería menos flexible. De esta manera, solo necesita una tabla Ordinal, no importa cuántas veces la use. Aún así, si la velocidad vale la pena, prueba el INSERT INTO ... SELECT Sintaxis.

Use un módulo PERL para realizar cálculos de fecha, como DateTime o Time DateTime o Time: Piece (núcleo de 5.10). Simplemente incremento Fecha e impresión Fecha y 0 hasta la fecha coincidirá con la actual.