I've been wanting a reason to try out CREATE AGGREGATE, and now have one: Root Mean Square/Quadratic Mean. I posted some broken code, that I've corrected, based on helpful suggestions from jjanes. Here's the working setup, with my custom tools and types schemas...you could use your own.
Now that it's working, I'm finding that the custom aggregate is dramatically slower than raw SQL. The grouping field is indexed, the aggregated field is not. Is this speed difference to be expected, and can it be overcome in SQL or PL/PgSQL?
First, here's the working code:
------------------------------------------------------
-- Create compound type to pass down processing chain
------------------------------------------------------
DROP TYPE types.rms_state CASCADE;
CREATE TYPE types.rms_state AS (
running_count int4,
running_sum_squares int4
);
------------------------------------------------------
-- Create the per-row function
------------------------------------------------------
DROP FUNCTION IF EXISTS tools.rms_row_function(types.rms_state, int4);
CREATE FUNCTION tools.rms_row_function (
rms_data_in types.rms_state,
value_from_row int4
)
RETURNS types.rms_state
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $BODY$
DECLARE
rms_data_out types.rms_state;
BEGIN
-- RAISE NOTICE 'rms_row_function: rms_data_in: %', rms_data_in::text;
rms_data_out.running_count := rms_data_in.running_count + 1;
rms_data_out.running_sum_squares := rms_data_in.running_sum_squares + (value_from_row ^ 2);
RETURN rms_data_out;
END;
$BODY$;
------------------------------------------------------
-- Create the final results function
------------------------------------------------------
DROP FUNCTION IF EXISTS tools.rms_result_function(types.rms_state);
CREATE FUNCTION tools.rms_result_function (
rms_data_in types.rms_state
)
RETURNS real
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $BODY$
DECLARE
rms_out real;
BEGIN
-- RAISE NOTICE 'rms_result_function: rms_data_in: %', rms_data_in::text;
IF (rms_data_in.running_count = 0) THEN
rms_out := 0;
ELSE
rms_out := (rms_data_in.running_sum_squares / rms_data_in.running_count)::real;
rms_out := rms_out ^ 0.5; -- Get the square root and return it
END IF;
RETURN rms_out;
END;
$BODY$;
------------------------------------------------------
-- Create the aggregate bindings/declaration
------------------------------------------------------
CREATE AGGREGATE tools.rms (int4)
(
sfunc = tools.rms_row_function,
finalfunc = tools.rms_result_function,
stype = types.rms_state,
FINALFUNC_MODIFY = READ_WRITE,
initcond = '(0,0)' -- Reset on each group, must be a textual version of state data.
);
I'm using a field named analytic_productivity.num_inst in my example, but it could be any int4 field. Here's a stripped-down table declation:
CREATE TABLE IF NOT EXISTS data.analytic_productivity (
id uuid NOT NULL DEFAULT NULL,
facility_id uuid NOT NULL DEFAULT NULL,
num_inst integer NOT NULL DEFAULT 0,
);
The facility table is included in the query for a name lookup:
select facility.name_ as facility_name,
sqrt(avg(power(num_inst, 2))) as inst_rms, -- root mean square/quadratic mean,
rms(num_inst) as inst_rms_check
from analytic_productivity
left join facility on facility.id = analytic_productivity.facility_id
group by 1
order by 1
Below are some sample results.
+-----------------+--------------------+----------------+
| facility_name | inst_rms | inst_rms_check |
+-----------------+--------------------+----------------+
| Anderson | 5.191804567965901 | 5.0990195 |
| Baldwin North | 42.24082451064157 | 42.237423 |
| Curvey | 41.75334367003306 | 41.749252 |
| Daodge Creeek | 28.75910443926612 | 28.757608 |
| Edgards | 42.430040392954375 | 42.426407 |
+-------------------------+--------------------+--------+
I'm not alarmed about the slight difference in scores, as I'm using a real, which only supports six decimals.