HAWQ PostgreSQL - Increment row based on previous row

Question

I need to create a table2 from this table1 trying to update the below table :

TABLE1:
ID  Rank    Event
123456  1   178
123456  2   
123456  3   
123456  4   155
123456  5   
123456  6   192
123456  7   
356589  1   165
356589  2   
356589  3   
356589  4   166
565984  1   1025
565984  2   
987456  1   85
987456  2   
987456  3   
987456  4   22
987456  5   
987456  6   

Trying to fill the 'Event' column based on previous value (like Ctrl+D in Excel)

TABLE2:
ID  Rank    Event
123456  1   178
123456  2   178
123456  3   178
123456  4   155
123456  5   155
123456  6   192
123456  7   192
356589  1   165
356589  2   165
356589  3   165
356589  4   166
565984  1   1025
565984  2   1025
987456  1   85
987456  2   85
987456  3   85
987456  4   22
987456  5   22
987456  6   22

Problem is that event does not follow order and count(ID, Rank) is not constant too. I cannot try using variable based function as it has millions of records and also cannot use 'update' as its Hawq.

Any suggestions? Appreciate!

Answer 1

You could use FIRST_VALUE:

SELECT ID, RANK, 
  FIRST_VALUE(Event) OVER(PARTITION BY ID ORDER BY Rank) AS Event
FROM tab;

EDIT:

Apologies! Each ID has multiple Event codes.

You could handle it with additional grouping:

WITH cte AS (
  SELECT ID, RANK, EVENT,
    SUM(CASE WHEN event IS NULL THEN 0 ELSE 1 END) 
        OVER(PARTITION BY ID ORDER BY RANK) AS grp
  FROM t
)
SELECT ID, RANK,
   FIRST_VALUE(Event) OVER(PARTITION BY ID, grp ORDER BY Rank) AS Event
FROM cte;

Rextester Demo

Answer 2

It is possible to do this without a subquery using array operations:

select v.*,
       (array_remove(array_agg(event) over (partition by id order by rank), NULL))[count(event) over (partition by id order by rank)]
from (values (1, 'a', 15),
             (2, 'a', null),
             (3, 'a', null),
             (4, 'a', 20),
             (5, 'a', null),
             (1, 'b', 4),
             (2, 'a', null)
     ) v(rank, id, event)
order by id, rank

Answer 3

you can try to use max with window function

CREATE TABLE T(ID int, Rank int, Event varchar(50));


INSERT INTO T VALUES (123456, 1,'178');
INSERT INTO T VALUES (123456, 2,'');
INSERT INTO T VALUES (123456, 3,'');
INSERT INTO T VALUES (123456, 4,'');
INSERT INTO T VALUES (123456, 5,'');
INSERT INTO T VALUES (123456, 6,'');
INSERT INTO T VALUES (123456, 7,'');
INSERT INTO T VALUES (356589, 1,'165');
INSERT INTO T VALUES (356589, 2,'');
INSERT INTO T VALUES (356589, 3,'');
INSERT INTO T VALUES (356589, 4,'');
INSERT INTO T VALUES (565984, 1,'1025');
INSERT INTO T VALUES (565984, 2,'');
INSERT INTO T VALUES (987456, 1,'85');
INSERT INTO T VALUES (987456, 2,'');
INSERT INTO T VALUES (987456, 3,'');
INSERT INTO T VALUES (987456, 4,'');
INSERT INTO T VALUES (987456, 5,'');
INSERT INTO T VALUES (987456, 6,'');

Query 1:

SELECT t.id,t.rank,max(Event) over (partition by ID order by Rank) 
FROM T

Results:

|     id | rank |  max |
|--------|------|------|
| 123456 |    1 |  178 |
| 123456 |    2 |  178 |
| 123456 |    3 |  178 |
| 123456 |    4 |  178 |
| 123456 |    5 |  178 |
| 123456 |    6 |  178 |
| 123456 |    7 |  178 |
| 356589 |    1 |  165 |
| 356589 |    2 |  165 |
| 356589 |    3 |  165 |
| 356589 |    4 |  165 |
| 565984 |    1 | 1025 |
| 565984 |    2 | 1025 |
| 987456 |    1 |   85 |
| 987456 |    2 |   85 |
| 987456 |    3 |   85 |
| 987456 |    4 |   85 |
| 987456 |    5 |   85 |
| 987456 |    6 |   85 |