Peter 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

pgp44 / lttb_1.sql

Created April 14, 2019 04:49

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

pgp44 / lttb_3.sql

Created April 14, 2019 04:54

	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

pgp44 / lttb_2.sql

Created April 14, 2019 04:56

	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
	$$;

pgp44 / lttb_4.sql

Created April 14, 2019 04:57

	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)

pgp44 / lttb_5.sql

Created April 14, 2019 05:00

	with recursive inputparams as (
	select 3600 as BucketSize
	),

pgp44 / lttb_6.sql

Created April 14, 2019 05:03

	timeseries as (
	select … as timestamp, … as value from … where …
	),

pgp44 / lttb_7.sql

Created April 14, 2019 05:06

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

pgp44 / lttb_8_csv

Last active April 14, 2019 05:13

	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

pgp44 / lttb_8.sql

Created April 14, 2019 05:15