PostgreSQL Grouped Rolling Averages, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

create table dummy (
  id     serial    primary key,
  itemid int       not null,
  isup   boolean   not null,
  logged timestamp not null
);

insert into dummy(itemid, isup, logged)
values (1, true,  '2017-03-23 12:55:00'),
       (1, false, '2017-03-23 12:57:00'),
       (1, true,  '2017-03-23 13:07:00'),
       (1, false, '2017-03-23 13:09:00'),
       (1, true,  '2017-03-23 13:50:00'),
       (2, false, '2017-03-23 12:55:00'),
       (2, true,  '2017-03-23 14:00:00'),
       (2, false, '2017-03-23 14:03:00');

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;

select   *, avg(isup::int) over (partition by itemid, group_nr order by logged) as rolling_avg
from     (
    select *, row_number() over (partition by itemid order by logged)
            - 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;


absolute service time: 0,44 sec