Re: Select rows based on aproximate value

On 2015-02-09 20:26, Jan Stozek wrote:
> Hi,
>
> 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?
>

Here's a sketch. Start by creating a calendar table and populate it with a number of years worth of data. In general such a table is very useful, but for now we will only use it as a domain. Something like:

     create table calendar ( dt date not null primary key );

     select c.dt, coalesce(p.prise, (select prise
                                     from prises p2
                                     where dt = (select max(dt)
                                                 from prices
                                                 where dt < p.dt)) )
     from prices p
     left join calendar c
         on p.dt = c.dt

coalesce will return the leftmost argument that is not null. If p.prise is null we will get the last prise before the current dt.

Another option would be to use variables. This is just a sketch so there might be errors in it. If you want a tested solution provide create table statement(s) and sample data as insert statements.

/Lennart Received on Tue Feb 10 2015 - 08:36:39 CET