Tengo una base de datos que recopila algunos datos con una frecuencia bastante alta y registra la marca de tiempo de cada entrada (almacenada como hora de época, no como mysql datetime). P.ej
timestamp rssi sender ------------------------------------------- 1592353967.171600 -67 9EDA3DBFFE15 1592353967.228000 -67 9EDA3DBFFE15 1592353967.282900 -62 E2ED2569BE2 1592353971.892600 -67 9EDA3DBFFE15 1592353973.962900 -61 2ADE2E4597B2 ... Mi objetivo es poder obtener un recuento de todas las filas en intervalos de tiempo de 15 minutos, que después de la investigación se puede obtener con GROUP BY . Idealmente, el resultado final se vería así
timestamp count ------------------------------ 1592352000 (8:00pm EST) 38 1592352900 (8:15pm EST) 22 1592353800 (8:30pm EST) 0 <----- Important, must include periods with 0 entries 1592354700 (8:45pm EST) 61 ...Principalmente tengo problemas con 2 cosas aquí: 1. Poder mostrar los intervalos de marca de tiempo en los resultados 2. Mostrar intervalos con 0 filas dentro de un período de tiempo
Mi intento actual es el siguiente, y está en el camino correcto porque los datos en esos períodos de tiempo son realmente correctos
Showing rows 0 - 23 (24 total, Query took 0.0264 seconds.) SELECT count(*) AS total, MINUTE(FROM_UNIXTIME(timestamp)) AS minute FROM requests WHERE timestamp >= 1592352000 AND timestamp < (1592352000 + 3600) GROUP BY MINUTE(FROM_UNIXTIME(timestamp)) total minute 55 32 89 33 64 34 55 35 87 36 82 37 90 38 69 39 74 40 47 41 89 42 53 43 71 44 87 45 72 46 83 47 86 48 83 49 113 50 76 51 77 52 88 53 81 54 28 55 Este dato es correcto, sin embargo hay periodos que no se muestran aquí (los primeros 30 minutos de esta hora no tienen datos y por lo tanto la entrada del primer minute empieza en 32). También intentando obtener cada 15 minutos usando
SELECT count(*) AS total, MINUTE(FROM_UNIXTIME(timestamp)) AS minute FROM requests WHERE timestamp >= 1592352000 AND timestamp < (1592352000 + 3600) GROUP BY MINUTE(FROM_UNIXTIME(timestamp)) DIV 15 #1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wave_master.requests.timestamp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byCualquier ayuda sería muy apreciada.
Puede usar una tabla de "números" para generar los períodos de 15 minutos dentro de la hora (0-3), y luego LEFT JOIN eso a sus conteos, agrupados por períodos de 15 minutos, usando COALESCE para reemplazar los valores NULL con 0:
SELECT periods.period * 900 + 1592352000 AS timestamp, FROM_UNIXTIME(periods.period * 900 + 1592352000) AS time, COALESCE(counts.total, 0) AS total FROM ( SELECT 0 period UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) periods LEFT JOIN ( SELECT (timestamp - 1592352000) DIV 900 AS period, COUNT(*) AS total FROM requests WHERE timestamp >= 1592352000 AND timestamp < 1592352000 + 3600 GROUP BY period ) counts ON counts.period = periods.periodSalida (para los datos en su pregunta más un par de otros valores):
timestamp time total 1592352000 2020-06-17 00:00:00 1 1592352900 2020-06-17 00:15:00 1 1592353800 2020-06-17 00:30:00 5 1592354700 2020-06-17 00:45:00 0