Sorry, if this is a lame question, but:
I have a table with prices of some goods registered for some
dates, such as:
2014-01-10 $120
2014-01-11 $121
2014-01-13 $119
2014-01-15 $118
I would like to create a view which would fill the missing
prices, ie. quote the existing prices where available, and return the
last known price if the price for a particular day is unknown, perhaps
with a reasonable limit in case a product disappears from the market:
2014-01-10 $120
2014-01-11 $121
2014-01-12 $121
2014-01-13 $119
2014-01-14 $119
2014-01-15 $118
2014-01-16 $118
2014-01-17 $118
2014-01-18 $118
..........
2014-02-01 NULL
If it was a mere matter of calculating the price one time, I
would probably use a function for that, but I need later to combine
the figures with the data from other tables with a date being an index
field.
2014-01-10 $120 data a
2014-01-11 $121 data b
2014-01-12 $121 data c
2014-01-13 $119 data d
2014-01-14 $119 data e
2014-01-15 $118 NULL
2014-01-16 $118 NULL
2014-01-17 $118 data f
2014-01-18 $118 data g
Without it on 12th, 14th, and after 15th, the price field would
return NULL, which I want to avoid, as it could ruin calculations
using the data.
If you believe that a view construction is not the best
solution here, I'd be grateful for pointing me in the right direction
- as you can see, the databases are not a field of my deepest
expertise. ;)
Thank you very much for your hints.
PS. The engine behind it is actually MariaDB, but I believe that it's
fairly compatible with mysql, isn't it?
