Группировка с интервалом 5 минут в интервале времени

Ответ 1

Это работает с каждым интервалом.

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

Ответ 2

Вам лучше использовать GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300 вместо round (../300) из-за округления, я обнаружил, что некоторые записи подсчитываются на два сгруппированных набора результатов.

Ответ 3

Для postgres мне было проще и точнее использовать

date_trunc

например:

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

Вы можете предоставлять различные разрешения, такие как "минута", "час", "день" и т.д.... до даты_трассы.

Ответ 4

я столкнулся с той же проблемой.

Я обнаружил, что легко сгруппировать на любой минутный интервал  просто разделив эпоху на минуты в количестве секунд, а затем округляя или используя пол, чтобы проехать остаток. Поэтому, если вы хотите получить интервал в 5 минут, вы должны использовать 300 секунд.

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 

Это вернет правильную группу данных на выбранный промежуток минут; однако он не будет возвращать интервалы, которые не содержат никаких данных. Чтобы получить эти пустые интервалы, мы можем использовать функцию generate_series.

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

код > Результат:

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   

Теперь, чтобы получить результат с интервалом с нулевыми вхождениями, мы просто внешние соединяем оба набора результатов.

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

на series.minute = cnt.interval_alias

код > Конечный результат будет включать серию со всеми 5-минутными интервалами, даже те, которые не имеют значений.

interval             count
-------------------  ----  
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 

Интервал можно легко изменить, отредактировав последний параметр generate_series. В нашем случае мы используем '5m', но это может быть любой интервал, который мы хотим.

Ответ 5

Запрос будет выглядеть примерно так:

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

Ответ 6

Вероятно, вам придется разбить свою временную метку на ymd: HM и использовать DIV 5, чтобы разделить минуты на 5-минутные бункеры - что-то вроде

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

... и затем futz вывод в клиентском коде, чтобы он выглядел так, как вам нравится. Или вы можете создать целую строку даты, используя sql concat operator вместо получения отдельных столбцов, если хотите.

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'))

... и затем группируем на этом

Ответ 7

Как насчет этого:

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

Ответ 8

Я выяснил, что с MySQL, вероятно, правильный запрос выглядит следующим образом:

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

Сообщите мне, что вы думаете.

Ответ 9

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))

Ответ 10

Не уверен, что вам все еще нужно.

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 |