Skip to content

Instantly share code, notes, and snippets.

@wolever

wolever/example.sql

Created January 4, 2013 23:00
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save wolever/4458294 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save wolever/4458294 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. wolever created this gist Jan 4, 2013.
    13 changes: 13 additions & 0 deletions example.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,13 @@
    DROP TABLE IF EXISTS foo;
    CREATE TEMPORARY TABLE foo (x FLOAT);
    INSERT INTO foo VALUES (1);
    INSERT INTO foo VALUES (2);
    INSERT INTO foo VALUES (3);
    INSERT INTO foo VALUES (4);
    INSERT INTO foo VALUES (100);

    SELECT avg(x), tmean(x, 2.0), tmean(x, 1.5) FROM foo;

    -- avg | tmean | tmean
    -- -----+-------+-------
    -- 22 | 22 | 2.5
    56 changes: 56 additions & 0 deletions tmean.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,56 @@
    DROP TYPE IF EXISTS tmean_stype CASCADE;

    CREATE TYPE tmean_stype AS (
    deviations FLOAT,
    count INT,
    acc FLOAT,
    acc2 FLOAT,
    vals FLOAT[]
    );

    CREATE OR REPLACE FUNCTION tmean_sfunc(tmean_stype, float, float)
    RETURNS tmean_stype AS $$
    SELECT $3, $1.count + 1, $1.acc + $2, $1.acc2 + ($2 * $2), array_append($1.vals, $2);
    $$ LANGUAGE SQL;

    CREATE OR REPLACE FUNCTION tmean_finalfunc(tmean_stype)
    RETURNS float AS $$
    DECLARE
    fcount INT;
    facc FLOAT;
    mean FLOAT;
    stddev FLOAT;
    lbound FLOAT;
    ubound FLOAT;
    val FLOAT;
    BEGIN
    mean := $1.acc / $1.count;
    stddev := sqrt(($1.acc2 / $1.count) - (mean * mean));
    lbound := mean - stddev * $1.deviations;
    ubound := mean + stddev * $1.deviations;
    -- RAISE NOTICE 'mean: % stddev: % lbound: % ubound: %', mean, stddev, lbound, ubound;

    fcount := 0;
    facc := 0;
    FOR i IN array_lower($1.vals, 1) .. array_upper($1.vals, 1) LOOP
    val := $1.vals[i];
    IF val >= lbound AND val <= ubound THEN
    fcount := fcount + 1;
    facc := facc + val;
    END IF;
    END LOOP;

    IF fcount = 0 THEN
    return NULL;
    END IF;
    RETURN facc / fcount;
    END;
    $$ LANGUAGE plpgsql;

    CREATE AGGREGATE tmean(float, float)
    (
    SFUNC = tmean_sfunc,
    STYPE = tmean_stype,
    FINALFUNC = tmean_finalfunc,
    INITCOND = '(-1, 0, 0, 0, {})'
    );