From: | Kirk Roybal <kirk(at)webfinish(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | lag_until_you_get_something() OVER () window function |
Date: | 2014-10-28 17:40:19 |
Message-ID: | d1ca3ccb97fefe4c4c95325d45b2e6ec@webfinish.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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;
Sorry, I'm not a C-coder, or I'd whip this up myself and submit it.
Thank you for your consideration,
/Kirk
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2014-10-28 17:45:36 | Re: WIP: Access method extendability |
Previous Message | Tom Lane | 2014-10-28 17:37:33 | Re: WIP: Access method extendability |