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