Agrupando em intervalo de 5 minutos dentro de um intervalo de tempo

Eu tenho algumas dificuldades com os comandos mySQL que quero fazer.

SELECT a.timestamp, name, count(b.name) FROM time a, id b WHERE a.user = b.user AND a.id = b.id AND b.name = 'John' AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00' GROUP BY a.timestamp 

Esta é a minha declaração de saída atual.

 timestamp name count(b.name) ------------------- ---- ------------- 2010-11-16 10:32:22 John 2 2010-11-16 10:35:12 John 7 2010-11-16 10:36:34 John 1 2010-11-16 10:37:45 John 2 2010-11-16 10:48:26 John 8 2010-11-16 10:55:00 John 9 2010-11-16 10:58:08 John 2 

Como posso agrupá-los em resultados de intervalos de 5 minutos?

Eu quero que minha saída seja como

 timestamp name count(b.name) ------------------- ---- ------------- 2010-11-16 10:30:00 John 2 2010-11-16 10:35:00 John 10 2010-11-16 10:40:00 John 0 2010-11-16 10:45:00 John 8 2010-11-16 10:50:00 John 0 2010-11-16 10:55:00 John 11 

Isso funciona com todos os intervalos.

PostgreSQL

 SELECT TIMESTAMP WITH TIME ZONE 'epoch' + INTERVAL '1 second' * round(extract('epoch' from timestamp) / 300) * 300, as timestamp, name, count(b.name) FROM time a, id WHERE … GROUP BY round(extract('epoch' from timestamp) / 300), name 

MySQL

 SELECT timestamp, -- not sure about that name, count(b.name) FROM time a, id WHERE … GROUP BY UNIX_TIMESTAMP(timestamp) DIV 300, name 

Você deve preferir utilizar GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300 vez de round (../300) por causa do arredondamento Descobri que alguns registros são contados em dois conjuntos de resultados agrupados.

Para postgres , achei mais fácil e mais preciso usar o

date_trunc

function, como:

 select name, sum(count), date_trunc('minute',timestamp) as timestamp FROM table WHERE xxx GROUP BY name,date_trunc('minute',timestamp) ORDER BY timestamp 

Você pode fornecer várias resoluções como ‘minute’, ‘hour’, ‘day’ etc … para date_trunc.

Eu me deparei com o mesmo problema.

Descobri que é fácil agrupar por qualquer intervalo de minutos é apenas dividir a época por minutos em quantidade de segundos e, em seguida, arredondar ou usar o chão para pegar o restante. Então, se você quiser obter intervalo em 5 minutos, você usaria 300 segundos .

 SELECT COUNT(*) cnt, to_timestamp(floor((extract('epoch' from timestamp_column) / 300 )) * 300) AT TIME ZONE 'UTC' as interval_alias FROM TABLE_NAME GROUP BY interval_alias 

 interval_alias cnt
 ------------------- ----  
 2010-11-16 10:30:00 2
 2010-11-16 10:35:00 10
 2010-11-16 10:45:00 8
 2010-11-16 10:55:00 11 

Isso retornará o grupo de dados corretamente pelo intervalo de minutos selecionado; no entanto, ele não retornará os intervalos que não contêm dados. Para obter esses intervalos vazios, podemos usar a function generate_series .

 SELECT generate_series(MIN(date_trunc('hour',timestamp_column)), max(date_trunc('minute',timestamp_column)),'5m') as interval_alias FROM TABLE_NAME 

Resultado:

 interval_alias       
 -------------------    
 2010-11-16 10:30:00  
 2010-11-16 10:35:00
 2010-11-16 10:40:00   
 2010-11-16 10:45:00
 2010-11-16 10:50:00   
 2010-11-16 10:55:00   

Agora, para obter o resultado com intervalo com zero ocorrências, apenas associamos os dois conjuntos de resultados .

 SELECT series.minute as interval, coalesce(cnt.amnt,0) as count from ( SELECT count(*) amnt, to_timestamp(floor((extract('epoch' from timestamp_column) / 300 )) * 300) AT TIME ZONE 'UTC' as interval_alias from TABLE_NAME group by interval_alias ) cnt RIGHT JOIN ( SELECT generate_series(min(date_trunc('hour',timestamp_column)), max(date_trunc('minute',timestamp_column)),'5m') as minute from TABLE_NAME ) series 

em series.minute = cnt.interval_alias

O resultado final includeá a série com todos os intervalos de 5 minutos, mesmo aqueles que não têm valores.

 contagem de intervalos
 ------------------- ----  
 2010-11-16 10:30:00 2
 2010-11-16 10:35:00 10
 2010-11-16 10:40:00 0
 2010-11-16 10:45:00 8
 2010-11-16 10:50:00 0 
 2010-11-16 10:55:00 11 

O intervalo pode ser facilmente alterado ajustando o último parâmetro de generate_series. No nosso caso, usamos ‘5m’, mas pode ser qualquer intervalo que quisermos.

A consulta será algo como:

 SELECT DATE_FORMAT( MIN(timestamp), '%d/%m/%Y %H:%i:00' ) AS tmstamp, name, COUNT(id) AS cnt FROM table GROUP BY ROUND(UNIX_TIMESTAMP(timestamp) / 300), name 

Você provavelmente terá que dividir seu timestamp em ymd: HM e usar DIV 5 para dividir os minutos em checkboxs de 5 minutos – algo como

 select year(a.timestamp), month(a.timestamp), hour(a.timestamp), minute(a.timestamp) DIV 5, name, count(b.name) FROM time a, id b WHERE a.user = b.user AND a.id = b.id AND b.name = 'John' AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00' GROUP BY year(a.timestamp), month(a.timestamp), hour(a.timestamp), minute(a.timestamp) DIV 12 

… e depois futz a saída no código do cliente para aparecer do jeito que você gosta. Ou você pode construir toda a string de data usando o operador de concatencia sql em vez de obter colunas separadas, se quiser.

 select concat(year(a.timestamp), "-", month(a.timestamp), "-" ,day(a.timestamp), " " , lpad(hour(a.timestamp),2,'0'), ":", lpad((minute(a.timestamp) DIV 5) * 5, 2, '0')) 

… e depois agrupar nessa

Que tal este:

 select from_unixtime(unix_timestamp(timestamp) - unix_timestamp(timestamp) mod 300) as ts, sum(value) from group_interval group by ts order by ts ; 

Não tenho certeza se você ainda precisa.

 SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(timestamp))/300)*300) AS t,timestamp,count(1) as c from users GROUP BY t ORDER BY t; 

2016-10-29 19:35:00 | 2016-10-29 19:35:50 | 4 |

2016-10-29 19:40:00 | 2016-10-29 19:40:37 | 5 |

2016-10-29 19:45:00 | 2016-10-29 19:45:09 | 6 |

2016-10-29 19:50:00 | 2016-10-29 19:51:14 | 4 |

2016-10-29 19:55:00 | 2016-10-29 19:56:17 | 1 |

Descobri que com o MySQL provavelmente a consulta correta é a seguinte:

 SELECT SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300, '%Y-%m-%d %H:%i:%S' ) , 1, 19 ) AS ts_CEILING, SUM(value) FROM group_interval GROUP BY SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300, '%Y-%m-%d %H:%i:%S' ) , 1, 19 ) ORDER BY SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300, '%Y-%m-%d %H:%i:%S' ) , 1, 19 ) DESC 

Diz-me o que pensas.

 select CONCAT(CAST(CREATEDATE AS DATE),' ',datepart(hour,createdate),':',ROUNd(CAST((CAST((CAST(DATEPART(MINUTE,CREATEDATE) AS DECIMAL (18,4)))/5 AS INT)) AS DECIMAL (18,4))/12*60,2)) AS '5MINDATE' ,count(something) from TABLE group by CONCAT(CAST(CREATEDATE AS DATE),' ',datepart(hour,createdate),':',ROUNd(CAST((CAST((CAST(DATEPART(MINUTE,CREATEDATE) AS DECIMAL (18,4)))/5 AS INT)) AS DECIMAL (18,4))/12*60,2))