WITH ROLLUP no me suma una columna

[deniz]

Hola gente del foro! estoy estrenando el hilo de base de datos . Les cuento, pues me tope con un problemita usando WITH ROLLUP. Les muestro la consulta:

Código MySQL:

SELECT
    r.fecha AS fecha, 
    r.idUsuario AS idUsuario, 
    IF( leads1 IS NULL , 0, leads1 ) AS leads, 
    COUNT( DISTINCT r.idRecomendacion ) AS recomendaciones
FROM 
    RECOMENDACION r
LEFT JOIN (
        SELECT 
            r.fechaLead, 
            SUM( r.lead ) AS leads1
        FROM 
            RECOMENDACION r
        GROUP BY 
            r.fecha
            ) aux ON ( aux.fechaLead = r.fecha )
LEFT JOIN 
    USUARIO u ON ( u.email = r.idUsuario )
GROUP BY 
    r.fecha
WITH ROLLUP
 

El resultado es ok, pero no me suma la columna "leads", me retona 0. A alguien le ha pasado esto? alguna solucion?

Desde ya muchas gracias!!

[gnzsoloyo]

Es muy posible que los registros en cuestión realmente tengan sólo NULL.
Además, en el contexto de la consulta, la tabla Usuarios es superflua. No agrega nada porque no se invoca en ninguna parte un campo de la misma, al menos en este ejemplo.

Sería bueno que nos postearas un volcado de la base para poder hacer pruebas, porque sin tablas con datos es difícil hacer pruebas. ¿Puedes?

[deniz]

Hola gnzsoloyo si por supuesto te copio el volcado

Código MySQL:

CREATE TABLE IF NOT EXISTS `RECOMENDACION` (
  `idRecomendacion` int(11) NOT NULL auto_increment,
  `idUsuario` varchar(255) default NULL,
  `amigo` varchar(255) default NULL,
  `lead` tinyint(1) NOT NULL default '0',
  `click` tinyint(1) NOT NULL default '0',
  `fecha` varchar(14) NOT NULL,
  `fechaClick` varchar(14) NOT NULL,
  `fechaLead` varchar(14) default NULL,
  `puntos` int(11) NOT NULL default '50',
  PRIMARY KEY  (`idRecomendacion`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Representa las recomendaciones hechas por los usuarios' AUTO_INCREMENT=31 ;
 
 
 
INSERT INTO `RECOMENDACION` (`idRecomendacion`, `idUsuario`, `amigo`, `lead`, `click`, `fecha`, `fechaClick`, `fechaLead`, `puntos`) VALUES
(1, '[email protected]', 'asdfasdf4343434343434', 0, 0, '20100101000000', '', '', 50),
(4, '[email protected]', 'adsfatraertatrggdf', 0, 0, '20100103000000', '', '', 0),
(6, '[email protected]', 'erferer4rer4r45455', 1, 0, '20100105000000', '', '20100107000000', 500),
(21, '[email protected]', 'sdlkfjmwoeiruq09ewrn', 0, 0, '20100107000000', '', '', 0),
(26, '[email protected]', 'asdmfvnweoriuqewr9iu', 0, 0, '20100105000000', '', '', 0),
(27, '[email protected]', 'adsfcweripeo489r8', 0, 0, '20100109000000', '', '', 0),
(28, '[email protected]', 'sdjfmoeitu495845r45r454', 0, 0, '20100111000000', '', '', 0),
(30, '[email protected]', 'asdfkjaeworiewadsf', 0, 0, '20100212000000', '', '', 0);
 

Lo de la tabla USUARIO efectivamente en ese ejemplo esta demas ;) puedes quitarlo (es que luego lo uso, en este caso no influye).

Con

Código MySQL:

IF( leads1 IS NULL , 0, leads1 ) AS leads,
 

no alcanza para poner en 0 los valores NULL ? Ya no se que hacer para que me sume esa columna....

Si sacas algo en limpio nos comentas!

Muchas gracias

[gnzsoloyo]

A mí me parece que lo que están mal planteados son los agrupamientos.
Además, en la miestra sólo tienes un lead ingresado, por lo que es propenso a errores de interpretación.
¿Cuáles son los subtotales y totales que se quieren obtener?

¿Podrías ser algo así:

Código MySQL:

SELECT
  IF(fecha is null, '', idUsuario) IDUSUARIO,
  IF(fecha IS NULL,IF(IDUSUARIO IS NULL,'TOTAL','Subtotal'), fecha) FECHA,
  lead
FROM
  (SELECT
    IF(fecha IS NULL, null, idUsuario) idUsuario,
    fecha,
    SUM(lead) lead
  FROM recomendacion r
  GROUP BY idUsuario, fecha
  WITH ROLLUP) T1;
 

[deniz]

Hola, he probado tu consulta, y ha ido muy bien, pero no me interesa calcular sub.totales, solo los totales, y es justamente lo que no me sale Que vuelta le podriamos dar ?

Por otra parte, tienes razon tengo 1 solo lead ingresado y puede que me traiga problemas pero en realidad es un escenario posible..


Sigo probando tu idea, muchas gracias por tu atencion, si tienes alguna idea mas te agredezco

[gnzsoloyo]

Hola, he probado tu consulta, y ha ido muy bien, pero no me interesa calcular sub.totales, solo los totales, y es justamente lo que no me sale Que vuelta le podriamos dar ?

Bien, un poco más claro, por favor: ¿Totales de qué y agrupados en base a qué criterios...?
Es decir:
- Totales por fecha (se eliminan los usuarios)
- Totales por usuario (se eliminan las fechas)
- Totales por usuario y fecha (sólo quedan los campos acumulables juto a la fecha y los usuarios)
- Totales por usuario y subtotales por fecha (sólo quedan los campos acumulables junto a la fecha y los usuarios)
- Subtotales por usuario y totales por fecha (sólo quedan los campos acumulables junto a la fecha y los usuarios)

Porque eso es lo que debes definir primero. La sintaxis variará conforme sea lo que necesitas...

[deniz]

Disculpas por mi ambigüedad...

Necesito totales por fechas, es decir, para cada fecha calcular la cantidad de leads y recomendaciones (ya esta hecho). Luego agregar una fila mas con los totales, que corresponden a los valores acumulados de las columnas numericas ( leads y recomendaciones ). Tengo algo mal hecho en la query?

Gracias

[gnzsoloyo]

Que la has escrito innecesariamente complicada:

Código MySQL:

SELECT
  IFNULL(fecha, 'Totales') FECHA,
  COUNT(idRecomendacion) recomendaciones,
  SUM(lead) leads
FROM recomendacion r
GROUP BY FECHA WITH ROLLUP;
 

Estabas haciendo que la subconsulta, que ya devolvía lo que necesitabas, perdiera las ´líneas que hacían posible funcionar al ROLLUP.

[deniz]

Lo que me dices no me sirve, tengo que cruzar datos entre las fechas y las fechasLead por eso hago un left join. La mejoro pero sigue sin calcularme el total para la columna Leads.

Código MySQL:

SELECT
  IFNULL(r.fecha, 'Totales') fecha,
  COUNT(r.idRecomendacion) recomendaciones,
  IFNULL(leads, 0) leads
FROM 
    RECOMENDACION r 
LEFT JOIN 
    (
        SELECT 
            r.fechaLead,
            SUM(r.lead) leads
        FROM 
            RECOMENDACION r 
        WHERE 
            r.fechaLead IS NOT NULL
        GROUP BY 
            substr(r.fechaLead, 1, 8)
    ) aux ON (substr(aux.fechaLead, 1, 8) = substr(r.fecha, 1, 8)) 
GROUP BY 
    substr(r.fecha, 1, 8) WITH ROLLUP
 

Gracias

[gnzsoloyo]

Explicame la lógica de tu consulta, porque yo la veo innecesaria...
Estás primero sumando los leads de la tabla y luego, cruzando ese resultado con la misma tabla para combinarlo con la cuenta de recomendaciones que coinciden en la misma fecha.

En esencia, es lo mismo que yo te propongo: Las funciones SUM() y COUNT() aplicadas sobre campos sólo suman y cuentan campos no nulos, por lo que el resultado debería ser ése.
Al aplicarlo con agrupamiento por fecha, devuelve los valores acumulados en cada día, mientras que el ROLLUP exterior permite tomar la suma y cuenta total.

[gnzsoloyo]

Tu resultado:

Código MySQL:

+----------------+-----------------+-------+
| fecha          | recomendaciones | leads |
+----------------+-----------------+-------+
| 20100101000000 |               1 |     0 |
| 20100103000000 |               1 |     0 |
| 20100105000000 |               2 |     0 |
| 20100107000000 |               1 |     1 |
| 20100109000000 |               1 |     0 |
| 20100111000000 |               1 |     0 |
| 20100212000000 |               1 |     0 |
| 20100212000000 |               8 |     0 |
+----------------+-----------------+-------+
8 rows in set (0.00 sec)
 

Mi resultado:

Código MySQL:

+----------------+-----------------+-------+
| FECHA          | recomendaciones | leads |
+----------------+-----------------+-------+
| 20100101000000 |               1 |     0 |
| 20100103000000 |               1 |     0 |
| 20100105000000 |               2 |     1 |
| 20100107000000 |               1 |     0 |
| 20100109000000 |               1 |     0 |
| 20100111000000 |               1 |     0 |
| 20100212000000 |               1 |     0 |
| Totales        |               8 |     1 |
+----------------+-----------------+-------+
 

¿Alguna duda?

[deniz]

jeje, si es que tu resultado me cuenta 1 lead en la fecha 20100105 cuando el lead en realidad es en la fecha 20100107...entendes? Son dos conjuntos de fechas las que tengo que cruzar.

Me he confundido en la query, en realidad para contar los leads me alcanza con un count(*)

Código MySQL:

SELECT
  IFNULL(r.fecha, 'Totales') fecha,
  COUNT(r.idRecomendacion) recomendaciones,
  IFNULL(leads, 0) leads
FROM 
    RECOMENDACION r 
LEFT JOIN 
    (
        SELECT 
            r.fechaLead,
             count( * )  leads
        FROM 
            RECOMENDACION r 
        WHERE 
            r.fechaLead IS NOT NULL
        GROUP BY 
            substr(r.fechaLead, 1, 8)
    ) aux ON (substr(aux.fechaLead, 1, 8) = substr(r.fecha, 1, 8)) 
GROUP BY 
    substr(r.fecha, 1, 8) WITH ROLLUP
 

Si te fijas las salidas tuya y mia, no coinciden en las fechas que te indico.

Gracias!!

[gnzsoloyo]

Eso se soluciona con un ORDER BY en la subselect...

Lo que sí facilitaría evitar este tipo de problemas hubiese sido poner la fecha con el tipo de dato correcto (DATE) y no como VARCHAR. Además, ahorrarías espacio en disco (8 bytes contra 14).

Ahora estoy saliendo de la empresa. Luego te mando la propuesta.

[deniz]

Genial si me podes orientar porque ya estoy algo embotada

Buen finde!

[gnzsoloyo]

Bien, habiendo tenido ya oportunidad de analizar el contenido de la tabla y viendo la estructura de datos, el error parece provenir de un defecto de diseño, sumado a un un desaprovechamiento de las propiedades de los campos.
El tema es así: Hay cierta inconsistencia en la estructura de datos porque tienes dos columnas de fecha distintas y no hay forma que cumplan con una consulta óptima en esas condiciones. Además una de ellas hace que el otro campo se vuelva innecesario si sólo variará su valor de 1 a 0.
Tu estructura es esta:

Código MySQL:

CREATE TABLE  `recomendacion` (
  `idRecomendacion` int(11) NOT NULL auto_increment,
  `idUsuario` varchar(255) default NULL,
  `amigo` varchar(255) default NULL,
  `lead` tinyint(1) NOT NULL default '0',
  `click` tinyint(1) NOT NULL default '0',
  `fecha` varchar(14) NOT NULL,
  `fechaClick` varchar(14) NOT NULL,
  `fechaLead` varchar(14) default NULL,
  `puntos` int(11) NOT NULL default '50',
  PRIMARY KEY  (`idRecomendacion`)
) ENGINE=InnoDB
DEFAULT CHARSET=latin1
COMMENT='Representa las recomendaciones hechas por los usuarios';
 

Todos los campos están declarados como NOT NULL, pero el caso es que de esos al menos fechaClick y fechaLead no deberían serlo porque sólo cuando se producen los eventos que los alimentan.
Al cargarlos con un vacío no permites que exista un valor NULL, con lo que si quisieras contar los campos, los va a contar como si fuesen fechas. Por eso es que no sirve ese esquema.
Esto es lo que hay:

Código MySQL:

mysql> SELECT idUsuario, lead, fecha, fechaLead, puntos, idRecomendacion
    -> FROM recomendacion r;
+--------------------------+------+----------------+----------------+--------+-----------------+
| idUsuario                | lead | fecha          | fechaLead      | puntos | idRecomendacion |
+--------------------------+------+----------------+----------------+--------+-----------------+
| [email protected]      |    0 | 20100101000000 |                |     50 |               1 |
| [email protected]      |    0 | 20100103000000 |                |      0 |               4 |
| [email protected]      |    1 | 20100105000000 | 20100107000000 |    500 |               6 |
| [email protected]      |    0 | 20100107000000 |                |      0 |              21 |
| [email protected] |    0 | 20100105000000 |                |      0 |              26 |
| [email protected]   |    0 | 20100109000000 |                |      0 |              27 |
| [email protected]   |    0 | 20100111000000 |                |      0 |              28 |
| [email protected]  |    0 | 20100212000000 |                |      0 |              30 |
+--------------------------+------+----------------+----------------+--------+-----------------+
8 rows in set (0.00 sec)
 

La tabla, para tener una estructura coherente debería ser:

Código MySQL:

CREATE TABLE  `recomendacion` (
  `idRecomendacion` int(11) UNSIGNED NOT NULL auto_increment,
  `idUsuario` varchar(255) default NULL,
  `amigo` varchar(255) default NULL,
  `lead` tinyint(1) NOT NULL default '0',
  `click` tinyint(1) NOT NULL default '0',
  `fecha` DATE NOT NULL,
  `fechaClick` DATE NULL,
  `fechaLead` DATE NULL,
  `puntos` int(11) UNSIGNED NOT NULL default '50',
  PRIMARY KEY  (`idRecomendacion`)
) ENGINE=InnoDB
DEFAULT CHARSET=latin1
COMMENT='Representa las recomendaciones hechas por los usuarios';
 

Los datos deberían ir así:

Código MySQL:

INSERT INTO `RECOMENDACION`
(`idRecomendacion`, `idUsuario`, `amigo`, `lead`, `click`, `fecha`, `fechaClick`, `fechaLead`, `puntos`)
VALUES
(1,   '[email protected]',       'asdfasdf4343434343434', 0, 0,  '2010-01-01', NULL, NULL, 50),
(4,   '[email protected]',       'adsfatraertatrggdf', 0, 0,     '2010-01-03', NULL, NULL, 0),
(6,   '[email protected]',       'erferer4rer4r45455', 1, 0,     '2010-01-05', NULL, '2010-01-07', 500),
(21,   '[email protected]',      'sdlkfjmwoeiruq09ewrn', 0, 0,   '2010-01-07', NULL, NULL, 0),
(26,   '[email protected]', 'asdmfvnweoriuqewr9iu', 0, 0,   '2010-01-05', NULL, NULL, 0),
(27,   '[email protected]',   'adsfcweripeo489r8', 0, 0,      '2010-01-09', NULL, NULL, 0),
(28,   '[email protected]',   'sdjfmoeitu495845r45r454', 0, 0,'2010-01-11', NULL, NULL, 0),
(30,   '[email protected]',  'asdfkjaeworiewadsf', 0, 0,     '2010-02-12', NULL, NULL, 0);
 

Y la consulta sería:

Código MySQL:

SELECT IFNULL(FECHA_LEAD,  'Totales') FECHA, RECOMENDACIONES, LEADS
FROM
  (SELECT
    IF(fechaLead IS NOT NULL, fechaLead, fecha) FECHA_LEAD,
    COUNT(idRecomendacion) recomendaciones,
    COUNT(fechaLead) leads
  FROM recomendacion r
  GROUP BY FECHA_lead WITH ROLLUP) T1
ORDER BY FECHA;
 
+------------+-----------------+-------+
| FECHA      | RECOMENDACIONES | LEADS |
+------------+-----------------+-------+
| 2010-01-01 |               1 |     0 |
| 2010-01-03 |               1 |     0 |
| 2010-01-05 |               1 |     0 |
| 2010-01-07 |               2 |     1 |
| 2010-01-09 |               1 |     0 |
| 2010-01-11 |               1 |     0 |
| 2010-02-12 |               1 |     0 |
| Totales    |               8 |     1 |
+------------+-----------------+-------+
8 rows in set (0.00 sec)
 

Nota: es muchísmo más eficiente guardar una fecha como DATE o DATETIME que como VARCHAR. ¿Por qué las pusiste en ese tipo de campo?

[deniz]

Hola gnzsoloyo, estuve analizando todo lo que comentas y la verdad eres un grande! Voy a tener muy en cuenta tus comentarios para mis proximos diseños de tablas, tus consejos me han sido de gran utilidad, muchas gracias!!

Todo perfecto! tema solucionado

[deniz]

Hola gnzsoloyo, encontre un bug en la ultima consulta que me sugeristes y es que me cuenta dos recomendaciones en la fecha 2010-01-07, cuando deberia ser en la fecha 2010-01-05. Tienes idea como puedo hacer para discriminar ese caso?

Código MySQL:

SELECT IFNULL(FECHA_LEAD,  'Totales') FECHA, RECOMENDACIONES, LEADS
FROM
  (SELECT
    IF(fechaLead IS NOT NULL, fechaLead, fecha) FECHA_LEAD,
    COUNT(idRecomendacion) recomendaciones,
    COUNT(fechaLead) leads
  FROM recomendacion r
  GROUP BY FECHA_lead WITH ROLLUP) T1
ORDER BY FECHA;
 
+------------+-----------------+-------+
| FECHA      | RECOMENDACIONES | LEADS |
+------------+-----------------+-------+
| 2010-01-01 |               1 |     0 |
| 2010-01-03 |               1 |     0 |
| 2010-01-05 |               1 |     0 |
| 2010-01-07 |               2 |     1 |
| 2010-01-09 |               1 |     0 |
| 2010-01-11 |               1 |     0 |
| 2010-02-12 |               1 |     0 |
| Totales    |               8 |     1 |
+------------+-----------------+-------+
8 rows IN SET (0.00 sec)
 

Saludos!