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

Question

我在数据库 postgresql 的字段跟踪(不带时区的时间戳)中有以下一组值:

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

我想按秒计算出现次数 (COUNT) 的平均值。在这个例子中是:

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

Answer 1

显示这段代码:

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)