1

I want to create a MATERIALIZED VIEW called ohlc that calculates the OHLC values of a stock's price. Stock prices are stored in an append-only price table.

The pricing data is updated regularly but ohlc is only refreshed once per hour. This got me thinking... Is it possible to bridge the gap by layering a VIEW on top of the MATERIALIZED VIEW?

Meaning, is it possible to define a VIEW that would return values from the ohlc table if they exists; and otherwise calculate the values on the fly? This means that the majority of rows would come from the cache, and only the most recent data would have to get calculated on the fly.

Is such a thing possible? If so, how?

Gili
  • 86,244
  • 97
  • 390
  • 689

1 Answers1

0

One possible way to achieve this is by constructing your VIEW as a UNION ALL between 1) the ohlc materialized view and 2) the query you use to build ohlc, but filtered by timestamps greater than the max timestamp recorded in ohlc. This is not the most elegant solution, but I believe it is the simplest.

richyen
  • 8,114
  • 4
  • 13
  • 28
  • Is there a way to join the two tables on a common unique key, and instruct the query to always return the left row if it exists; otherwise, return the right row? – Gili Sep 29 '22 at 18:20
  • I think you're referring to a `RIGHT JOIN` -- but that will produce two columns. From there, you can use `COALESCE` to select the Left if populated, and use Right if Left is null – richyen Sep 29 '22 at 18:25
  • Right, so the question becomes: will the optimizer be smart enough to skip the calculation if the MATERIALIZED VIEW's row is non-null. I guess I'll test that at some point. – Gili Sep 29 '22 at 18:35