multiple - mysql syntax insert

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

Question

Estoy construyendo un csv rápido de una tabla mysql con una consulta como:

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

y simplemente dejándolos en un archivo en perl sobre a:

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

Sin embargo, hay diferencias de fecha en los datos:

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

Me gustaría rellenar los datos para completar los días perdidos con entradas de conteo cero para terminar con:

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

Junté una solución muy incómoda (y casi con seguridad con errores) con una variedad de días por mes y algunas matemáticas, pero tiene que haber algo más directo en el lado de mysql o perl.

¿Alguna idea de genio / bofetadas en la cara de por qué soy tan tonto?

Terminé yendo con un procedimiento almacenado que generó una tabla temporal para el rango de fechas en cuestión por un par de razones:

• Sé el rango de fechas que buscaré cada vez
• Desafortunadamente, el servidor en cuestión no fue uno de los que pude instalar módulos perl en atm, y el estado del mismo era tan decrépito que no tenía nada remotamente Fecha :: - y installed

Las respuestas de Perl Date / DateTime-iterating también fueron muy buenas. ¡Ojalá pudiera seleccionar varias respuestas!

Answer 1

Como no sabe dónde están las lagunas, y aún 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!

Answer 2

Creo que la solución general más simple para el 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, haz una LEFT JOIN desde Ordinal a tus 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 la 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;

Por lo tanto, el código final sería algo como esto, si te unieras 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 que se utilizará al limitar Ordinal . En comparación, la transformación de esa secuencia de números enteros en fechas fue fácil.
• Puede usar Ordinal para todas sus necesidades de secuencia ininterrumpida. Solo asegúrate de que contenga más filas que tu secuencia más larga.
• Puede usar múltiples consultas en Ordinal para múltiples secuencias, por ejemplo enumerando 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 , sin importar cuántas veces la use. Aún así, si la velocidad vale la pena, pruebe la sintaxis INSERT INTO ... SELECT .

Answer 3

Cuando necesita algo así en el lado del servidor, normalmente crea una tabla que contiene todas las fechas posibles entre dos puntos en el tiempo, y luego la izquierda se une a esta tabla con los resultados de la consulta. Algo como esto:

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

En este caso particular, sería mejor poner un poco de control en el lado del cliente, si la fecha actual no es previos + 1, ponga algunas cadenas adicionales.

Answer 4

Cuando tuve que lidiar con este problema, para completar las fechas faltantes, en realidad creé una tabla de referencia que contenía todas las fechas que me interesaban y me uní 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, usaría SELECT INTO OUTFILE en lugar de generar el CSV a mano. Nos deja libres de preocuparnos por escapar de los personajes especiales también.

Answer 5

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)

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

Puede sintonizar su calendario dinámico limitándolo, por ejemplo, desde el mínimo (fecha) hasta ahora ().

Answer 6

No es tonto, esto no es algo que hace MySQL, 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 probablemente pueda encontrar otro mod de Perl que compare la fecha y proporcione un incremento de fecha.

Answer 7

No sé si esto funcionaría, pero qué tal si creaste 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, haz una combinación izquierda en las dos tablas? 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.

Answer 8

Puede 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 hace el código anterior es que mantiene la última fecha impresa almacenada en un objeto DateTime $dt , y cuando la fecha actual es más de un día en el futuro, incrementa $dt por un día (y lo imprime una línea en CSV ) hasta que sea igual a la fecha actual.

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

Answer 9

Use algún módulo Perl para hacer cálculos de fechas, como DateTime o Time :: Piece recomendados (núcleo desde 5.10). Simplemente incremente la fecha e imprima y 0 hasta que la fecha coincida con la actual.