gistfile1.sql

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 function adjustback_circ (
    float, float )
returns float
language sql
immutable
as 
$f$
select case when $1 > $2 
    then $1 - $2
else
    $1
end;
$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$;