Mean of circular quantities in SQL

avg_cyclic()

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

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

I wanted to thank you for providing this. In my fork, I added an Oracle aggregate function. Your PL/SQL code helped me with the arithmetic, however simply averaging the linear coordinates and passing to atan2 was enough, without the extra checking. Cheers.