得到每5分钟一桶的时间戳数

Question

我有张时间戳表。我想把它们分成5分钟的水桶，然后在那个水桶里计算时间戳的数量。我有一些问题，使SQL相当正确。我在用Postgres。它告诉我最后一行中的时间戳列不存在，但它被定义为别名。

SELECT
    TIMESTAMP WITH TIME ZONE 'epoch' +
    INTERVAL '1 second' * round(extract('epoch' from my_timestamp) / 300) * 300
    as timestamp,
    count(my_timestamp)
FROM logs
GROUP BY
round(extract('epoch' from timestamp) / 300)

Answer 1

我想你的GROUP BY关机了。试试这个：

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + INTERVAL '1 second' * round(extract('epoch' from my_timestamp) / 300) * 300 as timestamp,
       count(*)
FROM (values (now())) logs(my_timestamp)
GROUP BY timestamp