Skip to content

Instantly share code, notes, and snippets.

View pgp44's full-sized avatar

Peter pgp44

  • Project 44
  • Antwerp
View GitHub Profile
@pgp44
pgp44 / lttp.sql
Last active March 21, 2023 21:47
SQL implementation of Largest Triangle Three Bucket time series sampling algoritm
create type point_t as (x float, y float);
create type triangle_t as (p1 point_t, p2 point_t, p3 point_t, surface float);
create function largest_triangle_accum (maxsurfacetriangle triangle_t, p1 point_t, p2 point_t, p3 point_t)
returns triangle_t
language SQL
as $$
select
case
when maxsurfacetriangle is null or triangle_surface(p1,p2,p3) > maxsurfacetriangle.surface
create type point_t as (x float, y float);
create type triangle_t as (p1 point_t, p2 point_t, p3 point_t, surface float);
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
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
$$;
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)
with recursive inputparams as (
select 3600 as BucketSize
),
timeseries as (
select … as timestamp, … as value from … where …
),
-- 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
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
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