sql - Retrieve last known value for each column of a row

Question

Not sure about the correct words to ask this question, so I will break it down.

I have a table as follows:

date_time | a | b | c

Last 4 rows:

15/10/2013 11:45:00 | null   | 'timtim' | 'fred'
15/10/2013 13:00:00 | 'tune' | 'reco'   | null
16/10/2013 12:00:00 | 'abc'  | null     | null
16/10/2013 13:00:00 | null   | 'died'   | null

How would I get the last record but with the value ignoring the null and instead get the value from the previous record.

In my provided example the row returned would be

16/10/2013 13:00:00 | 'abc' | 'died' | 'fred'

As you can see if the value for a column is null then it goes to the last record which has a value for that column and uses that value.

This should be possible, I just cant figure it out. So far I have only come up with:

select 
    last_value(a) over w a
from test
WINDOW w AS (
    partition by a
    ORDER BY ts asc
    range between current row and unbounded following
    );

But this only caters for a single column ...

Answer 1

Here I create an aggregation function that collects columns into arrays. Then it is just a matter of removing the NULLs and selecting the last element from each array.

Sample Data

CREATE TABLE T (
    date_time timestamp,
    a text,
    b text,
    c text
);

INSERT INTO T VALUES ('2013-10-15 11:45:00', NULL, 'timtim', 'fred'),
('2013-10-15 13:00:00', 'tune', 'reco', NULL  ),
('2013-10-16 12:00:00', 'abc', NULL, NULL     ),
('2013-10-16 13:00:00', NULL, 'died', NULL    );

Solution

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

WITH latest_nonull AS (
    SELECT MAX(date_time) As MaxDateTime, 
           array_remove(array_accum(a), NULL) AS A, 
           array_remove(array_accum(b), NULL) AS B, 
           array_remove(array_accum(c), NULL) AS C
    FROM T
    ORDER BY date_time
)
SELECT MaxDateTime, A[array_upper(A, 1)], B[array_upper(B,1)], C[array_upper(C,1)]
FROM latest_nonull;

Result

     maxdatetime     |  a  |  b   |  c
---------------------+-----+------+------
 2013-10-16 13:00:00 | abc | died | fred
(1 row)