sql - 根据时间间隔分组计算平均值

在postgres表中，我以10秒的间隔存储一个对象的速度。在一天中，这些值不是每10秒可用一次；因此可能今天16:39:40没有行
如果假设非现有行的平均速度为0，那么查询将得到一个关系，它包含一个给定的一天中平均速度为1分钟（或两个或两个字节）的间隔的关系。
速度表
id（整数，主键）
ts（时间戳）
速度（数字）
我已经构建了这个查询，但在一些重要的部分上遇到了问题：

SELECT
    date_trunc('minute', ts) AS truncated,
    avg(speed)
FROM speed_table AS t
WHERE ts >= '2014-06-21 00:00:00'
AND ts <= '2014-06-21 23:59:59'
AND condition2 = 'something'
GROUP BY date_trunc('minute', ts)
ORDER BY truncated

我怎样才能改变时间间隔，而不是日期函数的结果，如5分30秒？
如何为一天的剩余时间添加不可用的行？

最佳答案

此特定示例的简单快速解决方案：

SELECT date_trunc('minute', ts) AS minute
     , sum(speed)/6 AS avg_speed
FROM   speed_table AS t
WHERE  ts >= '2014-06-21 0:0'
AND    ts <  '2014-06-20 0:0'  -- exclude dangling corner case
AND    condition2 = 'something'
GROUP  BY 1
ORDER  BY 1;

您需要将缺少的行视为“0速度”。因为一分钟有6个样本，只要求和除以6。缺少的行的计算结果隐式为0。
这将在分钟内不返回任何行，而完全不返回行。avg_speed对于缺少的结果行是0。
任意间隔的一般查询
适用于the manual for date_trunc()中列出的所有间隔：

SELECT date_trunc('minute', g.ts) AS ts_start
     , avg(COALESCE(speed, 0))    AS avg_speed
FROM  (SELECT generate_series('2014-06-21 0:0'::timestamp
                            , '2014-06-22 0:0'::timestamp
                            , '10 sec'::interval) AS ts) g
LEFT   JOIN speed_table t USING (ts)
WHERE (t.condition2 = 'something' OR
       t.condition2 IS NULL)                -- depends on actual condition!
AND    g.ts <> '2014-06-22 0:0'::timestamp  -- exclude dangling corner case
GROUP  BY 1
ORDER  BY 1;

有问题的部分是附加的未知条件。你得给它下个定义。并决定generate_series提供的丢失行是否应该通过测试（这可能很棘手！）.
我让它们传入我的示例（以及所有其他带有空值的行）。
比较：
PostgreSQL: running count of rows for a query 'by minute'
任意间隔：
Truncate timestamp to arbitrary intervals
对于完全任意的间隔，考虑@Clodoaldo's math based on epoch values或使用经常被忽略的函数width_bucket()。例子：
Aggregating (x,y) coordinate point clouds in PostgreSQL
Aggregating (x,y) coordinate point clouds in PostgreSQL

关于sql - 根据时间间隔分组计算平均值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24342698/