mysql - obtener - generar rango de fechas sql

MySQL cómo completar las fechas faltantes en el rango?

Question

Tengo una tabla con 2 columnas, fecha y puntaje. Tiene como máximo 30 entradas, para cada uno de los últimos 30 días.

date      score
-----------------
1.8.2010  19
2.8.2010  21
4.8.2010  14
7.8.2010  10
10.8.2010 14

Mi problema es que faltan algunas fechas, quiero ver:

date      score
-----------------
1.8.2010  19
2.8.2010  21
3.8.2010  0
4.8.2010  14
5.8.2010  0
6.8.2010  0
7.8.2010  10
...

Lo que necesito de la consulta única es obtener: 19,21,9,14,0,0,10,0,0,14 ... Eso significa que las fechas faltantes se llenan con 0.

Sé cómo obtener todos los valores y en el lenguaje del lado del servidor repetir las fechas y perder los espacios en blanco. Pero, ¿es posible hacerlo en mysql, para que ordene el resultado por fecha y obtenga las piezas que faltan?

EDIT: en esta tabla hay otra columna llamada UserID, entonces tengo 30,000 usuarios y algunos de ellos tienen el puntaje en esta tabla. Borro las fechas todos los días si la fecha es <hace 30 días porque necesito los últimos 30 días para cada usuario. La razón es que estoy haciendo un gráfico de la actividad del usuario en los últimos 30 días y para trazar un gráfico necesito los 30 valores separados por coma. Así que puedo decir en la consulta consígame la actividad USERID = 10203 y la consulta me daría los 30 puntajes, uno para cada uno de los últimos 30 días. Espero ser más claro ahora.

Answer 1

MySQL no tiene funcionalidad recursiva, por lo que te queda utilizar el truco de la tabla NUMBERS:

1. Crea una tabla que solo contenga números incrementales, fácil de usar usando un auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Complete la tabla usando:

   INSERT INTO `example`.`numbers`
     ( `id` )
   VALUES
     ( NULL )

   ... para todos los valores que necesite.

3. Use DATE_ADD para construir una lista de fechas, aumentando los días según el valor NUMBERS.id. Reemplace "2010-06-06" y "2010-06-14" con sus respectivas fechas de inicio y finalización (pero use el mismo formato, YYYY-MM-DD) -

   SELECT `x`.*
     FROM (SELECT DATE_ADD(''2010-06-06'', INTERVAL `n`.`id` - 1 DAY)
             FROM `numbers` `n`
            WHERE DATE_ADD(''2010-06-06'', INTERVAL `n`.`id` -1 DAY) <= ''2010-06-14'' ) x

4. IZQUIERDA ÚNETE en tu tabla de datos según la porción de tiempo:

      SELECT `x`.`ts` AS `timestamp`,
             COALESCE(`y`.`score`, 0) AS `cnt`
        FROM (SELECT DATE_FORMAT(DATE_ADD(''2010-06-06'', INTERVAL `n`.`id` - 1 DAY), ''%m/%d/%Y'') AS `ts`
                FROM `numbers` `n`
               WHERE DATE_ADD(''2010-06-06'', INTERVAL `n`.`id` - 1 DAY) <= ''2010-06-14'') x
   LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, ''%d.%m.%Y'') = `x`.`ts`

Si desea mantener el formato de fecha, use la función DATE_FORMAT :

DATE_FORMAT(`x`.`ts`, ''%d.%m.%Y'') AS `timestamp`

Answer 2

No soy partidario de las otras respuestas, que requieren que se creen tablas y cosas por el estilo. Esta consulta lo hace de manera eficiente sin tablas auxiliares.

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date
FROM 
    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a 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) AS a
        CROSS JOIN (SELECT 0 AS a 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) AS b
        CROSS JOIN (SELECT 0 AS a 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) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

Así que vamos a diseccionar esto.

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date

El if detectará días que no tuvieron puntaje y los estableció en 0. b.Days es la cantidad configurada de días que eligió para obtener de la fecha actual, hasta 1000.

    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a 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) AS a
        CROSS JOIN (SELECT 0 AS a 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) AS b
        CROSS JOIN (SELECT 0 AS a 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) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b

Esta subconsulta es algo que vi en . Genera de manera eficiente una lista de los últimos 1000 días a partir de la fecha actual. El intervalo (actualmente 30) en la cláusula WHERE al final determina qué días se devuelven; el máximo es 1000. Esta consulta podría modificarse fácilmente para devolver cientos de años de fechas, pero 1000 debería ser bueno para la mayoría de las cosas.

LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

Esta es la parte que trae su tabla que contiene el puntaje en ella. Se compara con el intervalo de fechas seleccionado de la consulta del generador de fechas para poder completar los 0 cuando sea necesario (el puntaje se establecerá en NULL inicialmente, porque es un LEFT JOIN , esto se corrige en la instrucción select). También lo ordeno por las fechas, solo porque. Esta es la preferencia, también puede ordenar por puntaje.

Antes de ORDER BY , puede unirse fácilmente a su tabla sobre la información del usuario que mencionó con su edición, para agregar ese último requisito.

Espero que esta versión de la consulta ayude a alguien. Gracias por leer.

Answer 3

Puede lograr esto usando una Tabla de Calendario . Esa es una tabla que creas una vez y llenas con un rango de fechas (por ejemplo, un conjunto de datos para cada día 2000-2050, eso depende de tus datos). Luego puede hacer una combinación externa de su tabla con la tabla de calendario. Si falta una fecha en su tabla, devuelve 0 para el puntaje.