Skip to content

Instantly share code, notes, and snippets.

View lttb_10.sql
with recursive inputparams as (
select 260 as BucketSize
),
...
timeseries as (
select * from (
select '2019–04–01 23:59:00'::timestamp, 3504
union select '2019–04–01 23:58:00'::timestamp, 3488
...
) tab(timestamp,value)
View lttb_9.sql
select
to_timestamp(((t.p).p2::point_t).x) at time zone 'utc',
((t.p).p2::point_t).y
from largesttriangle t
where ((t.p).p2::point_t).y != 0
order by 1
View lttb_8.sql
largesttriangle(grp,p) as (
select
wga.grp,
((0,0),(wga.x,wga.y),(0,0),0.0)::triangle_t
from withgrpavg wga
where grp = 1
union all
View lttb_8_csv
1, timestamp,value,avg timestamp bucket 2,avg value bucket 2
...
2, timestamp,value,avg timestamp bucket 3,avg value bucket 3
2, timestamp,value,avg timestamp bucket 3,avg value bucket 3
...
3, timestamp,value,avg timestamp bucket 4,avg value bucket 4
3, timestamp,value,avg timestamp bucket 4,avg value bucket 4
3, timestamp,value,avg timestamp bucket 4,avg value bucket 4
...
n, timestamp,value,null,null
View 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
View lttb_6.sql
timeseries as (
selectas timestamp, … as value fromwhere
),
View lttb_5.sql
with recursive inputparams as (
select 3600 as BucketSize
),
View lttb_4.sql
select largest_triangle(p1,p2,p3)
from ( select (0,0)::point_t, (1,1)::point_t, (0,1)::point_t
union select (0,0)::point_t, (2,2)::point_t, (0,2)::point_t
union select (0,0)::point_t, (4,4)::point_t, (0,4)::point_t
union select (0,0)::point_t, (3,3)::point_t, (0,3)::point_t
) triangles(p1,p2,p3)
⇒ ((0,0),(4,4),(0,4),8)
View lttb_2.sql
create function triangle_surface(p1 point_t,p2 point_t,p3 point_t)
returns float
language SQL
as $$
select abs(p1.x*(p2.y-p3.y)+p2.x*(p3.y-p1.y)+p3.x*(p1.y-p2.y))/2
$$;
View lttb_3.sql
create aggregate largest_triangle (point_t,point_t,point_t) (
stype = triangle_t,
sfunc = largest_triangle_accum
);
create function largest_triangle_accum (maxsurfacetriangle triangle_t, p1 point_t, p2 point_t, p3 point_t)
returns triangle_t
language SQL
as $$
select