lttb_7.sql

-- 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
),