0

I have a table A with below values

year  quarter price source
2017   1       10    s
2017   3       12    t
2018   1       10    m
2018   2       15    r

I would like to see the table as below. Yes, I would like to fill the non specified quarters with previous quarter values.

I thought of creating a temp table B with all quarters and do a join with this table A but how can I fill the price and source fields from previous quarter.

year  quarter price  source
2017   1       10      s
2017   2       10      s
2017   3       12      t
2017   4       12      t
2018   1       10      m
2018   2       15      r
user416
  • 516
  • 3
  • 12
  • 34
  • 1
    You don't need temp tables, create missing rows using simple hierarchical generator, left join your table and finally use `lag... ignore nulls` combined with `coalesce` or `nvl`, like [here](http://sqlfiddle.com/#!4/38e61d/1). – Ponder Stibbons Sep 19 '18 at 13:33
  • I am running against 100k records and the connect runs for ever... – user416 Sep 20 '18 at 20:18

1 Answers1

0

For this, oracle has the LAG window function, which allows you to access previous rows. I think, it is anyways not required to fill the data to the next row as you can easily gather it when you select it.

E.g.

SELECT year,
   quarter,
   price,
   source,
   price,
   LAG(price, 1, 0) OVER (ORDER BY year, quarter) AS price_previous FROM   A;
Stefan Jud
  • 1
  • 2