Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL circular quartiles function
create type numeric_quartiles_plus as (
min float,
q05 float,
q10 float,
q25 float,
q50 float,
q75 float,
q90 float,
q95 float,
max float,
stddev float,
mean float
);
create function adjust_circ (
float, float, float )
returns float
language sql
immutable
as
$f$
select case when $1 <= $2
then $1 + $3
else
$1
end;
$f$;
create or replace function adjustback_circ (
float, float )
returns float
language sql
immutable
as
$f$
select ($1::numeric % $2::numeric)::float;
$f$;
create or replace function circ_qt_sql (
vectors float[], coord float default 360 )
returns numeric_quartiles_plus
language sql
immutable
as $f$
-- unnest the array into rows
with vec1 as (
select * from unnest(vectors)
as meas(vec)
),
-- add the degree max to the lowest value
-- and tack it on to the end
vec2 as (
select vec
from vec1
union all
select min(vec) + coord
from vec1
),
-- calculate the gaps between each pair of points
gaps as (
select vec, - vec + lead(vec,1,0::FLOAT) over ( order by vec ) as gap
from vec2
order by gap desc, vec limit 1
),
-- add the degree max to each value below the gap
adjust as (
select adjust_circ(vec1.vec,gaps.vec,coord) as vec
from vec1, gaps
),
-- calculate percentiles and aggregates
pctiles as (
select percentile_cont(array[0, 0.05, 0.1, 0.25, 0.5, 0.75, 0.9, 0.95, 1]::float[])
within group (order by vec) as pct,
avg(vec) as mean,
stddev(vec) as stddev
from adjust
)
-- subtract out the degree max again
-- and return as a 13-column set
select adjustback_circ(pct[1], coord),
adjustback_circ(pct[2], coord),
adjustback_circ(pct[3], coord),
adjustback_circ(pct[4], coord),
adjustback_circ(pct[5], coord),
adjustback_circ(pct[6], coord),
adjustback_circ(pct[7], coord),
adjustback_circ(pct[8], coord),
adjustback_circ(pct[9], coord),
adjustback_circ(stddev, coord),
adjustback_circ(mean, coord)
from pctiles;
$f$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.