sql - 如何计算每秒的记录数？

我在DB postgresql的字段trace（timestamp without time zone）中有以下一组值：

"2017-02-22 18:46:43.394"
"2017-02-22 18:46:43.316"
"2017-02-22 18:46:43.237"
"2017-02-22 18:46:42.011"
"2017-02-22 18:46:41.927"
"2017-02-22 18:46:41.728"

我想以秒为单位得到事件的平均值。在这个例子中是：

18:46:43 > 3 occurrences
18:46:42 > 1 occurrences
18:46:41 > 2 occurrences

AVERAGE = 2 occurrences per second

建议：

SELECT AVG(COUNT(trace???))
FROM log_data

最佳答案

显示此代码：

select
count(date_trunc('sec',exe.x)),
date_trunc('sec',exe.x)
from
(
    select
    unnest(
        array
        [
        '2017-02-22 18:46:43.394'::timestamp,
        '2017-02-22 18:46:43.316'::timestamp,
        '2017-02-22 18:46:43.237'::timestamp,
        '2017-02-22 18:46:42.011'::timestamp,
        '2017-02-22 18:46:41.927'::timestamp,
        '2017-02-22 18:46:41.728'::timestamp
        ]
    ) as x
) exe
group by date_trunc('sec',exe.x)

关于sql - 如何计算每秒的记录数？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42412515/