Grouping into interval of 5 minutes within a time range

小开

您可能需要将时间戳分解为 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

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

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

然后就这个问题进行讨论

小开

这对每个间隔都有效。

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

小开

这个查询类似于:

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

小开

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

告诉我你的想法。

小开

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

小开

对于 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

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

小开

我也遇到了同样的问题。

我发现按任何分钟间隔分组都很容易只要把 新纪元除以几分钟的秒数，然后四舍五入或者用地板乘以剩下的。所以如果你想在 五分钟中得到间隔，你可以使用 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

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

    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

现在为了得到零出现间隔的结果，我们只需要 两个结果集的外部连接。

    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

最终结果将包括所有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 任何间隔 we want.

小开

这个怎么样:

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

小开

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

小开

不知道你还需不需要。

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-2919:40:00 | 2016-10-2919:40:37 | 5 |

2016-10-2919:45:00 | 2016-10-2919:45:09 | 6 |

2016-10-2919:50:00 | 2016-10-2919:51:14 | 4 |

2016-10-2919:55:00 | 2016-10-2919:56:17 | 1 |

小开

这正是你想要的。

替换

你的约会时间
C-call 字段
Astro _ transit1-您的表
每增加300秒的时间间隔

SELECT
FROM_UNIXTIME(300 * ROUND(UNIX_TIMESTAMP(r.dt) / 300)) AS 5datetime,
(SELECT
r.c
FROM
astro_transit1 ra
WHERE
ra.dt = r.dt
ORDER BY ra.dt DESC
LIMIT 1) AS first_val
FROM
astro_transit1 r
GROUP BY UNIX_TIMESTAMP(r.dt) DIV 300
LIMIT 0 , 30

小开

Based on @boecko answer for MySQL, I used a CTE (Common Table Expression) to accelerate the query execution time :

所以这个:

SELECT
`timestamp`,
`name`,
count(b.`name`)
FROM `time` a, `id` b
WHERE …
GROUP BY
UNIX_TIMESTAMP(`timestamp`) DIV 300, name

变成:

WITH cte AS (
SELECT
`timestamp`,
`name`,
count(b.`name`),
UNIX_TIMESTAMP(`timestamp`) DIV 300 AS `intervals`
FROM `time` a, `id` b
WHERE …
)
SELECT * FROM cte GROUP BY `intervals`

在大量的数据中，速度加快了10倍以上！

由于 timestamp和 time在 MySQL 中是保留的，所以不要忘记在每个表和列名上使用“ ...”！

希望对你们有所帮助。