Instantly share code, notes, and snippets.

# milesjordan/Circular Average SQL

forked from hegge/avg_cyclic()
Last active Feb 20, 2019
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 commented Feb 20, 2019

 Doesn't work in postgres.
to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.