Oracle: How fill in the gaps between rows using previous next row’s values using sql

I have the table:

id  year    quarter total
==  ====    ======= =====
1   2010    1       100
1   2010    2       0
1   2010    3       100
1   2010    4       100
2   2010    1       20
2   2010    2       20
2   2010    3       20
2   2010    4       20

How fill in the gaps between rows using previous next row’s values in Oracle to get the following output:

id  year    quarter total
==  ====    ======= =====
1   2010    1       100
1   2010    2       100
1   2010    3       100
1   2010    4       100
2   2010    1       20
2   2010    2       20
2   2010    3       20
2   2010    4       20

the values of previous and next columns should be the same to fill the rows.

Answer

If you want all rows to be the same for a given year and id, just use max():

select t.*,
       max(total) over (partition by year, id) as imputed_total
from t;

There seems to be no need for conditional logic. You could just do:

select t.id, t.year, t.quarter,
       max(total) over (partition by year, id) as imputed_total
from t;