Re: lag_until_you_get_something() OVER () window function

On Tue, Oct 28, 2014 at 12:40 PM, Kirk Roybal <kirk(at)webfinish(dot)com> wrote:
> Hi Guys,
>
> I propose a lag (and/or lead) window function that propagates the last
> non-null value to the current row.
> Here's an example of what I mean by that:
>
> CREATE TABLE lag_test (id serial primary key, natural_key integer, somebody
> text);
>
> INSERT INTO lag_test(natural_key, somebody)
> VALUES (1, NULL), (1, 'Kirk'), (1, NULL), (2, 'Roybal'), (2, NULL), (2,
> NULL);
>
> /*
>
> Creates this data in the table.
> id natural_key somebody
> -- ----------- --------
> 1 1 NULL
> 2 1 Kirk
> 3 1 NULL
> 4 2 Roybal
> 5 2 NULL
> 6 2 NULL
>
> lag_until_you_get_something(text) function should return this in the
> "somebody" column:
>
> id natural_key somebody
> -- ----------- --------
> 1 1 NULL
> 2 1 Kirk
> 3 1 Kirk
> 4 2 Roybal
> 5 2 Roybal
> 6 2 Roybal
>
> Notice that row 6 has a value "Roybal", when the last known value was in row
> 4. Also, Row 1 did not get a value.
> */
>
> -- Query that gets the right result for limited example data:
>
> CREATE FUNCTION last_elem (text[]) RETURNS text AS $$
> SELECT $1[array_upper($1,1)];
> $$ LANGUAGE SQL;
>
>
> SELECT id, natural_key,
> last_elem(string_to_array(string_agg(somebody, '|') OVER (ORDER BY
> natural_key, id)::text, '|')) lag_hard
> FROM lag_test
> ORDER BY natural_key, id;

Here's a more efficient and cleaner version of same:

CREATE OR REPLACE FUNCTION GapFillInternal(
s anyelement,
v anyelement) RETURNS anyelement AS
$$
BEGIN
RETURN COALESCE(v,s);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE AGGREGATE GapFill(anyelement) (
SFUNC=GapFillInternal,
STYPE=anyelement
);

postgres=# select id, natural_key, gapfill(somebody) OVER (ORDER BY
natural_key, id) from lag_test;
id │ natural_key │ gapfill
────┼─────────────┼─────────
1 │ 1 │
2 │ 1 │ Kirk
3 │ 1 │ Kirk
4 │ 2 │ Roybal
5 │ 2 │ Roybal
6 │ 2 │ Roybal
(6 rows)

merlin