Re: Fwd: Tricky join and update with same table

James David Smith wrote
> So for each person I want to take the first row where the
> point_geom is not null, and update all of the rows above it with that
> value. It's driving me nuts.

*Not tested but the theory has worked for me in the past for similar
problems.

/*
 * Useful for situation where you can build an ordered array where the last
 * cell value represents the current row and in the case where that value is
NULL
 * you want to retrieve the last known non-null value; effectively
 * carrying that value forward to the current row.
 */
CREATE OR REPLACE FUNCTION array_last_nonnull(in_array anyarray)
RETURNS anyelement
AS $$

    SELECT unnest FROM (
    SELECT unnest, row_number() OVER () AS array_index FROM (
    SELECT unnest($1)
    ) explode ) filter
    WHERE unnest IS NOT NULL
    ORDER BY array_index DESC
    LIMIT 1;

$$
LANGUAGE sql
STRICT
IMMUTABLE
;

SELECT id, array_last_nonnull(
array_agg(value) OVER (PARTITION BY id ORDER BY idx DESC ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)
,
array_agg(value) OVER (PARTITION BY id ORDER BY idx DESC ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM (VALUES (1,1,NULL), (1,2,1), (1,3,2), (2,1,NULL), (2,2,1)) val (id,
idx, value)
;

The function and array_agg window function expression could probably be
written differently - i.e., in a "first non-null" manner - but I already
have this function installed and understood so I used it instead.  The idea
to build an array for each ID, using a window function, of all possible
values and then pick out of that array the relevant non-null value that you
need.

Since you are looking forward, not backward, you must use the "RANGE" clause
as part of the window function definition.

Whether this performs well is another matter but one optimization you can
make, if the data supports it, is to limit the size of the frame so that the
corresponding array doesn't get too large.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Fwd-Tricky-join-and-update-with-same-table-tp5765253p5765284.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.