He escrito una consulta que count los loggings hora por hora:
select TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'),count(*) from req group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'); el resultado es:
2012-02-22 13 2280 2012-02-22 15 1250 2012-02-22 16 1245 2012-02-22 19 1258
Pero necesito un resultado como este:
2012-02-22 13 2280 2012-02-22 14 0 2012-02-22 15 1250 2012-02-22 16 1245 2012-02-22 17 0 2012-02-22 18 0 2012-02-22 19 1258
¡También tengo estas consultas que se agrupan por día y mes también!
select TO_CHAR(copied_timestamp, 'YYYY-MM-DD'),count(*) from req group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD'); select TO_CHAR(copied_timestamp, 'YYYY-MM'),count(*) from req group by TO_CHAR(copied_timestamp, 'YYYY-MM');
Necesito que sus vacíos se llenen con cero o nulo también. Cualquier ayuda es muy apreciada.
Nota: Hay una respuesta para esta pregunta en Oracle usando CONNECT BY pero necesito la respuesta en Mysql porque Mysql no admite CONNECT BY . Aqui esta el link
Genere una tabla con una sola columna de dates y horas que contenga todas las dates y horas dentro de un range razonable (por ejemplo, de 1900 a 2200). Luego, haga una LEFT JOIN desde esta tabla hasta su consulta actual.
Para que esta técnica funcione correctamente, es probable que deba agregar una columna indexada a su tabla que contenga una timestamp convertida (su date de copied_timestamp convertida a DATETIME , networkingondeada a la hora)
SELECT date_hour, count(req.converted_timestamp) FROM dates_hours LEFT JOIN req ON req.converted_timestamp = dates_hours.date_hour WHERE date_hour BETWEEN (SELECT MIN(req.converted_timestamp) FROM req) AND (SELECT MAX(req.converted_timestamp) FROM req) GROUP BY date_hour
Para generar la tabla dates_hours :
CREATE TABLE dates_hours (date_hour DATETIME PRIMARY KEY); DELIMITER $$$ CREATE PROCEDURE generate_dates_hours (to_date DATETIME) BEGIN DECLARE start_date DATETIME; DECLARE inc INT; SELECT MAX(date_hour) INTO start_date FROM dates_hours; IF start_date IS NULL THEN SET start_date = '1900-01-01'; END IF; SET inc = 1; WHILE start_date + INTERVAL inc HOUR <= to_date DO INSERT INTO dates_hours VALUE (start_date + INTERVAL inc HOUR); SET inc = inc +1; END WHILE; END $$$ DELIMITER ; CALL generate_dates_hours('2200-01-01');
De acuerdo, ahora que estoy revisando a mí mismo, me doy count de que esta es una solución bastante descabellada. Espero que a alguien se le ocurra uno más elegante.
Creé una tabla llamada TBL_NUMBERS
CREATE TABLE `TBL_NUMBER` (`n` int(11) NOT NULL)
y loggings insertados del 1 al 1000. Ahora puedo generar cualquier tipo de range de date usando esta consulta:
SELECT '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] or as dateRange FROM TBL_NUMBER WHERE '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] <= '2012-06-23';
Entonces puedo unirme a esta tabla con mis resultados para llenar el intervalo de dates. Si necesito más de 1000 dates, puedo insert más loggings en TBL_NUMBER
Si tienes una idea mejor, estoy ansioso por saber eso;)