Re: lag_until_you_get_something() OVER () window function

This is a pretty elegant way of getting there.

It also does a better job of respecting the window frame.

I'll use this until this
https://commitfest.postgresql.org/action/patch_view?id=1096 [1] shows
up.

Thanks

On 2014-10-28 17:35, Merlin Moncure wrote:

> 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

Links:
------
[1] https://commitfest.postgresql.org/action/patch_view?id=1096