Fill NULL column values ​​with delay values ​​(N)

Question

I have a simple table in which there are no values ​​for the someone column. I want to fill the NULL values ​​with the previous values ​​in ascending order along the id field, but not descending values ​​(elements in the past may differ). For the sake of experiment (my actual query is much more complicated), I can’t just use the UPDATE query to populate the table, I have to do it as SELECT.

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);

The sample code creates the table as follows:

id  natural_key  somebody
--  -----------  --------
1   1            NULL
2   1            Kirk
3   1            NULL
4   2            Roybal
5   2            NULL
6   2            NULL

So far I have this:

SELECT id,
       natural_key,
       COALESCE(somebody, lag(somebody) OVER (PARTITION BY natural_key)) somebody
FROM lag_test
ORDER BY natural_key, id;

What returns this:

id  natural_key  somebody
--  -----------  --------
1   1            NULL
2   1            Kirk
3   1            Kirk
4   2            Roybal
5   2            Roybal
6   2            NULL

I would like this to return:

id  natural_key  somebody
--  -----------  --------
1   1            NULL
2   1            Kirk
3   1            Kirk
4   2            Roybal
5   2            Roybal
6   2            Roybal

The main question is: how do I get lag () for N rows to work in the past so that row id: 6, natural_key: 2 gets the value for the someone column?

I work with PG 9.3.4.

: , , [], . , - :

SELECT id,
       natural_key,
       COALESCE(somebody,
                lag(somebody, 1) OVER (PARTITION BY natural_key),
                lag(somebody, 2) OVER (PARTITION BY natural_key),
                lag(somebody, 3) OVER (PARTITION BY natural_key)
               ) somebody
FROM lag_test
ORDER BY natural_key, id;

, OP. .

2:

.

SELECT id, natural_key, 
  regexp_replace(string_agg(somebody, '|') OVER (ORDER BY id)::text, '^.*\|', '', 'g') somebody 
FROM lag_test 
ORDER BY natural_key, id;

, "|" . Kinda hacky, .

Answer 1

- , . , , .

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), (1, NULL), (1, 'James'), (1, NULL),
        (2, 'Roybal'), (2, NULL), (2, NULL),
        (3, NULL), (3, 'Truman'), (3, NULL), (3, NULL);

, - ( LAG), , . .

SELECT  lt.ID ID, lt.Natural_key,
        CASE WHEN lt.Somebody IS NULL
            THEN lt1.Somebody
            ELSE lt.Somebody END SomeBody
  FROM  lag_test lt
  LEFT JOIN lag_test lt1
    ON  lt1.ID =(
        SELECT  MAX( ID )
          FROM  lag_test
         WHERE  Natural_key = lt.Natural_key
           and  ID < lt.ID
           AND  SomeBody IS NOT NULL);

SQL Fiddle .

Answer 2

, :

SELECT lt.id, lt.natural_key, l.somebody from lag_test lt inner join (select 
lt.natural_key, lt.somebody from lag_test lt inner join (select MAX(id) as LastID, 
somebody from lag_test WHERE NOT somebody is null GROUP BY somebody) as lson 
lt.id=ls.LastID) as l on lt.natural_key=l.natural_key

, .

id  natural_key somebody
----------------------------
1   1   Kirk
2   1   Kirk
3   1   Kirk
4   1   Kirk
5   2   Roybal
6   2   Roybal
7   2   Roybal