Schema (PostgreSQL v12)
CREATE TABLE test (ts timestamp, value1 float8, value2 float8);
insert into test values
('2017-01-01', 1, 1),
('2017-01-02', 1, null),
('2017-01-03', 1, 1),
('2017-01-04', NULL, 100),
('2017-01-05', 1, 1),
('2017-01-06', NULL, 1),
('2017-01-07', 10, null),
('2017-01-09', 1, 1),
('2017-01-10', 1, 1),
('2017-01-11', NULL, 1),
('2017-01-12', NULL, 1);
CREATE OR REPLACE FUNCTION wacky_accum_sfunc(accum float8[],numerator float8, denominator float8) RETURNS float8[]
IMMUTABLE PARALLEL SAFE LANGUAGE sql AS
$f$
-- SELECT accum
SELECT ARRAY[
CASE WHEN denominator is null or numerator is null then accum[1] else accum[1] + numerator END,
CASE WHEN denominator is null or numerator is null then accum[2] else accum[2] + denominator END,
accum[3] + CASE WHEN denominator is null or numerator is null then 0 else 1 END
]::float8[]
$f$;
CREATE OR REPLACE FUNCTION wacky_div(accum float8[]) RETURNS float8[]
IMMUTABLE PARALLEL SAFE LANGUAGE sql AS
$f$
SELECT ARRAY [
CASE WHEN accum[2] = 0.0::float8 THEN NULL ELSE accum[1]/accum[2] END,
accum[3]
]
$f$;
CREATE AGGREGATE wacky(float8, float8) (
SFUNC = wacky_accum_sfunc,
STYPE = float8[],
FINALFUNC = wacky_div,
INITCOND = '{0,0,0}'
);
Query #1
select
wacky(value1, value2),
sum(value1)/sum(value2) as naive
from test;
wacky | naive |
---|---|
1,5 | 0.14814814814814814 |
In wacky_div
perhaps this line:
Should return 0 instead of null, or the line below it should return NULL as well when the line above is null?