分组到5分钟的时间范围内

I have some difficulties with mySQL commands that I want to do.

对于我想做的mySQL命令，我有一些困难。

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

This is my current output statement.

这是我当前的输出语句。

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

How do I group them into 5 minutes interval results?

我如何将它们分成5分钟的间隔结果?

I want my output to be like

我希望输出是这样的

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

10 个解决方案

#1

102

This works with every interval.

这适用于每一个间隔。

PostgreSQL

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

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

You should rather use GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300 instead of round(../300) because of the rounding I found that some records are counted into two grouped result sets.

您应该使用GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300而不是round(../300)，因为四舍五入我发现有些记录被算作两个分组结果集。

#3

For postgres, I found it easier and more accurate to use the

对于postgres，我发现它更容易和更准确地使用

date_trunc

function, like:

功能,如:

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

You can provide various resolutions like 'minute','hour','day' etc... to date_trunc.

你可以列出各种不同的决心，比如“分钟”、“小时”、“一天”等等。date_trunc。

#4

i came across the same issue.

我遇到了同样的问题。

I found that it is easy to group by any minute interval is just dividing epoch by minutes in amount of seconds and then either rounding or using floor to get ride of the remainder. So if you want to get interval in 5 minutes you would use 300 seconds.

我发现，任何一分钟的间隔都是很容易分组的，只是在数秒内分几分钟，然后四舍五入或使用地板来得到剩余的时间。如果你想在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

This will return the data correctly group by the selected minutes interval; however, it will not return the intervals that don't contains any data. In order to get those empty intervals we can use the function generate_series.

这将按选定的分钟间隔返回正确的数据组;但是，它不会返回不包含任何数据的间隔。为了得到那些空间隔，我们可以使用函数generate_series。

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

Result:

结果:

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

Now to get the result with interval with zero occurrences we just outer join both result sets.

现在，为了得到区间为零的结果，我们将两个结果集外部连接。

 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
 on series.minute = cnt.interval_alias
在系列。分钟= cnt.interval_alias

The end result will include the series with all 5 minute intervals even those that have no values.

最终的结果将包括所有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

The interval can be easily changed by adjusting the last parameter of generate_series. In our case we use '5m' but it could be any interval we want.

通过调整generate_series的最后一个参数，可以很容易地更改间隔。在我们的例子中，我们用5m，但是它可以是我们想要的任何区间。

#5

The query will be something like:

查询将如下所示:

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

You're probably going to have to break up your timestamp into ymd:HM and use DIV 5 to split the minutes up into 5-minute bins -- something like

你可能需要把你的时间戳分解成ymd:嗯，然后用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

...and then futz the output in client code to appear the way you like it. Or, you can build up the whole date string using the sql concat operatorinstead of getting separate columns, if you like.

…然后在客户端代码中输入输出，以您喜欢的方式显示。或者，您可以使用sql concat操作符构建整个日期字符串，而不是获得单独的列(如果您愿意的话)。

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

...and then group on that

…然后分组讨论

#7

How about this one:

这个呢:

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

Not sure if you still need it.

不确定你是否还需要它。

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: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: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: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:50:00 | 2016-10-29 19:51:14 | 4 |

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

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

#9

I found out that with MySQL probably the correct query is the following:

我发现使用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

Let me know what you think.

让我知道你的想法。

#10

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

#1

102