How to get the circular average of a dataset in different databases

Circular Average SQL

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_circular_average.sql

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

oracle_create_function_circular_average.sql

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;

pgsql_create_function_circular_average.sql

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

Doesn't work in postgres.