Skip to content

Instantly share code, notes, and snippets.

@DanielJoyce
Last active April 23, 2020 04:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save DanielJoyce/aeecc46530feba907e20f17f59bfb78a to your computer and use it in GitHub Desktop.
Save DanielJoyce/aeecc46530feba907e20f17f59bfb78a to your computer and use it in GitHub Desktop.
Null Ignoring Weighted Avg ("Wacky") operator in pure postgres

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

View on DB Fiddle

@DanielJoyce
Copy link
Author

Wacky now returns an array containing N/D where in each slice both N and D were not null, and the number of samples where N and D where both not null

@DanielJoyce
Copy link
Author

In wacky_div

perhaps this line:

          CASE WHEN accum[2] = 0.0::float8 THEN NULL ELSE accum[1]/accum[2] END,

Should return 0 instead of null, or the line below it should return NULL as well when the line above is null?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment