Create a gist now

Instantly share code, notes, and snippets.

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}'
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment