Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to get the circular average of a dataset in different databases
These files show how to get the circular average (cyclic mean) of a dataset using SQL, for different databases.
For MySQL an example select statement is shown, and for Oracle and PostgreSQL the way to create a user aggregate function that computes the circular average is shown.
Props to Hegge for kicking this off: https://gist.github.com/hegge/476950
-- MySQL
SELECT UNIX_TIMESTAMP(date), avg_cyclic, frequency_start , frequency_stop, intensity, source
FROM (SELECT MAX(id) AS max_id, CONCAT(echo.frequency_start , '-', echo.frequency_stop) AS frequency
FROM echo
WHERE channel = {0}
GROUP BY frequency) AS id,
echo,
(SELECT
IF (avg(sin(direction*PI()/180))>0 AND avg(cos(direction*PI()/180))>0,
atan(avg(sin(direction*PI()/180))/avg(cos(direction*PI()/180))),
IF (avg(cos(direction*PI()/180))<0,
atan(avg(sin(direction*PI()/180))/avg(cos(direction*PI()/180)))+PI(),
IF (avg(sin(direction*PI()/180))<0 AND avg(cos(direction*PI()/180))>0,
atan(avg(sin(direction*PI()/180))/avg(cos(direction*PI()/180)))+2*PI(),
1000
)
)
)*180/PI() AS avg_cyclic, frequency
FROM (SELECT direction, CONCAT(echo.frequency_start , '-', echo.frequency_stop) AS frequency
FROM echo WHERE channel = {0} AND date > now() - interval {1} second) AS echo_last
GROUP BY frequency) AS echo_avg
WHERE id.max_id = echo.id
AND echo_avg.frequency = id.frequency;
CREATE OR REPLACE TYPE U_CIRCULAR_AVG AS OBJECT
(
running_sum_cos_n NUMBER, -- a running sum of the cosine of the numbers passed
running_sum_sin_n NUMBER, -- a running sum of the sine of the numbers passed
running_count NUMBER, -- a count of the numbers passed
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT U_CIRCULAR_AVG) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT U_CIRCULAR_AVG, value IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN U_CIRCULAR_AVG, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT U_CIRCULAR_AVG, ctx2 IN U_CIRCULAR_AVG) RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY U_CIRCULAR_AVG IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT U_CIRCULAR_AVG) RETURN NUMBER IS
BEGIN
SCTX := U_CIRCULAR_AVG(0, 0, 0);
RETURN ODCIConst.Success;
EXCEPTION WHEN ZERO_DIVIDE THEN
SCTX := NULL
RETURN ODCIConst.Success;
END;
-- Iterate over the input values.
-- The input is accepted in degrees and converted to radians for the SIN() and COS() functions.
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT U_CIRCULAR_AVG, value IN NUMBER) RETURN NUMBER IS
BEGIN
SELF.running_sum_cos_n := SELF.running_sum_cos_n + COS(value*3.14159265359/180);
SELF.running_sum_sin_n := SELF.running_sum_sin_n + SIN(value*3.14159265359/180);
SELF.running_count := SELF.running_count + 1;
RETURN ODCIConst.Success;
END;
-- When all values have been processed, we just calculate the averages and pass to ATAN2().
-- The result is normalised to within range 0 to 359.999999 and converted back to degrees.
MEMBER FUNCTION ODCIAggregateTerminate(self IN U_CIRCULAR_AVG, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS
avg_c number;
avg_s number;
n number;
BEGIN
avg_c := SELF.running_sum_cos_n / SELF.running_count;
avg_s := SELF.running_sum_sin_n / SELF.running_count;
n := ATAN2(avg_s, avg_c) * 180 / 3.14159265359;
IF n >= 0 THEN
returnValue := n;
ELSE
returnValue := n + 360;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT U_CIRCULAR_AVG, ctx2 IN U_CIRCULAR_AVG) RETURN NUMBER IS
BEGIN
SELF.running_sum_cos_n := SELF.running_sum_cos_n + ctx2.running_sum_cos_n;
SELF.running_sum_sin_n := SELF.running_sum_sin_n + ctx2.running_sum_sin_n;
SELF.running_count := SELF.running_count + ctx2.running_count;
RETURN ODCIConst.Success;
END;
END;
CREATE FUNCTION CIRCULAR_AVG (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING U_CIRCULAR_AVG;
-- PostgreSQL
CREATE OR REPLACE FUNCTION avg_cyclic_sfunc(float8[], float8) RETURNS float8[]
AS 'select ARRAY[$1[1]+sin($2*pi()/180), $1[2]+cos($2*pi()/180), $1[3]+1];'
LANGUAGE SQL
RETURNS NULL ON NULL INPUT;
CREATE OR REPLACE FUNCTION avg_cyclic_finalfunc(float8[]) RETURNS float8 AS
$$
DECLARE
s float8;
c float8;
result float8;
BEGIN
s := $1[1]/$1[3];
c := $1[2]/$1[3];
result := atan2(s, c)*180/pi();
IF s > 0 AND c > 0 THEN
RETURN result;
ELSIF c < 0 THEN
result := result + 180;
ELSIF s < 0 AND c > 0 THEN
result := result + 360;
END IF;
RETURN 1000;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT;
CREATE AGGREGATE avg_cyclic(float8)
(
sfunc = avg_cyclic_sfunc,
stype = float8[],
finalfunc = avg_cyclic_finalfunc,
initcond = '{0,0,0}'
);
@riyadparvez

This comment has been minimized.

Copy link

commented Feb 20, 2019

Doesn't work in postgres.

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.