sql - PostgreSQL 分组滚动平均值

Question

我正在尝试生成按项目 ID 列分组的设定时间段内的滚动平均值。

这是表格的基本布局和一些虚拟数据，去除了绒毛:

----------------------------------------------------
| id   | itemid   | isup   | logged                |
----------------------------------------------------
| 1    | 1        | true   | 2017-03-23 12:55:00   |
| 2    | 1        | false  | 2017-03-23 12:57:00   |
| 3    | 1        | true   | 2017-03-23 13:07:00   |
| 4    | 1        | false  | 2017-03-23 13:09:00   |
| 5    | 1        | true   | 2017-03-23 13:50:00   |
| 6    | 2        | false  | 2017-03-23 12:55:00   |
| 7    | 2        | true   | 2017-03-23 14:00:00   |
| 8    | 2        | false  | 2017-03-23 14:03:00   |
----------------------------------------------------

我找到了一个 answer to a previous question on rolling averages但我似乎不太明白如何按项目 ID 对平均值进行分组；我走过的几乎所有途径都以统计数据完全错误而告终。

这是我的出发点 - 我觉得我对 ROW_NUMBER() OVER 缺乏理解并没有帮助解决问题。

SELECT id, itemid, AVG(isup) 
    OVER (PARTITION BY groupnr ORDER BY logged) AS averagehour
FROM (       
    SELECT id, itemid, isup, logged, intervalgroup, 
        itemid - ROW_NUMBER() OVER (
            partition by intervalgroup ORDER BY logged) AS groupnr
    FROM (
        SELECT id, itemid, logged,
            CASE WHEN isup = TRUE THEN 1 ELSE 0 END AS isup,
           'epoch'::TIMESTAMP + '3600 seconds'::INTERVAL * 
                (EXTRACT(EPOCH FROM logged)::INT4 / 3600) AS intervalgroup
        FROM uplog
  ) alias_inner
) alias_outer
ORDER BY logged;

如有任何帮助，我们将不胜感激。

Answer 1

链接的答案几乎包含您需要的一切。如果您想进一步“分组”(例如按 itemid)，您只需将这些“分组”添加到窗口的 PARTITION BY 子句中功能:

select   *, avg(isup::int) over (partition by itemid, group_nr order by logged) as rolling_avg
from     (
    select *, id - row_number() over (partition by itemid, interval_group order by logged) as group_nr
    from   (
        select *, 'epoch'::timestamp + '3600 seconds'::interval * (extract(epoch from logged)::int4 / 3600) as interval_group
        from   dummy
    ) t1
) t2
order by itemid, logged

请注意 然而，这个(和链接的答案)之所以有效，只是因为 id 没有间隙并且与其表的时间戳字段是有序的。如果不是这种情况，您将需要

row_number() over (partition by itemid order by logged) - row_number() over (partition by itemid, interval_group order by logged) as group_nr

而不是 id - row_number() ...。

http://rextester.com/YBSC43615

此外，如果您打算只使用小时组，您可以使用:

date_trunc('hour', logged) as interval_group

而不是更一般的算术(正如@LaurenzAlbe 已经注意到的那样)。