Skip to content

Instantly share code, notes, and snippets.

@pgp44
Created April 14, 2019 05:06
Show Gist options
  • Save pgp44/6f47357e375c8612e962db374edf07fe to your computer and use it in GitHub Desktop.
Save pgp44/6f47357e375c8612e962db374edf07fe to your computer and use it in GitHub Desktop.
-- First and last (timestamp,value) of the timeseries
tsrange as (
select
(select (extract(epoch from timestamp), value)::point_t
from timeseries order by timestamp asc limit 1) as frst,
(select (extract(epoch from timestamp), value)::point_t
from timeseries order by timestamp desc limit 1) as lst
),
-- Add bucket number (grp) for all but the last bucket
withgrptmp as (
select
1 as grp, (tsr.frst::point_t).x, (tsr.frst::point_t).y
from tsrange tsr
union
select
1+dense_rank() over
(order by i.BucketSize*cast(extract(epoch from timestamp)/i.BucketSize as int)) as grp
,extract(epoch from timeseries.timestamp)
,value as val
from timeseries, tsrange tsr, inputparams i
where timestamp > to_timestamp((tsr.frst::point_t).x) at time zone 'utc'
and timestamp < to_timestamp((tsr.lst::point_t).x) at time zone 'utc'
),
-- Add bucket number for last bucket
withgrp as (
select * from withgrptmp
union
select
1 + (select max(grp) from withgrptmp) as grp,
(tsr.lst::point_t).x,
(tsr.lst::point_t).y
from tsrange tsr
),
-- Average timestamp,value per bucket
withgrpavgtmp as (
select
grp,
avg(x) as xavg,
avg(y) as yavg
from withgrp group by grp
),
-- Join time series timestamp,value with average values of following bucket
withgrpavg as (
select withgrp.grp as grp,
withgrp.x,
withgrp.y,
withgrpavgtmp.xavg as xavg3,
withgrpavgtmp.yavg as yavg3
from withgrp left outer join withgrpavgtmp on
withgrp.grp=withgrpavgtmp.grp-1
),
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment