Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpolation

On 1 mai, 01:42, Vadim Tropashko <vadimtro_inva..._at_yahoo.com> wrote:
> On Apr 29, 3:04 am, Cimode <cim..._at_hotmail.com> wrote:
>
>
>
> > Among the least explored opportunities RM has to offer comes the issue
> > of numerical / time series linear interpolation of values. Recently,
> > I have solved the following problem on a community board which I
> > encountered this problem a few years ago with different facts while
> > working for the Frech railroad company. I thought about using the
> > recent example (better for pedagogical purposes) to illustrate the
> > work done a few years ago.
> ...
> > (
> > select E.id, E.Distance, dateadd(s, (E.Distance/G.distance) * G.gen,
> > E.start) arrivaltime
> > from yourtable F inner join
> > (
> > select A.ID, A.Distance, B.arrivaltime start,
> > datediff(s,B.arrivaltime, C.arrivaltime) as gen
> > from yourtable A
> > left outer join
> > (
> > select id, distance, arrivaltime from yourtable
> > where ArrivalTime is not null
> > ) B
> > on B.id < A.id
> > left outer join
> > (
> > select id, distance, arrivaltime from yourtable
> > where ArrivalTime is not null
> > ) C
> > on C.id > A.id
> > where A.arrivaltime is null
> > ) E
> > on E.ID = F.ID
>
> > left outer join
> > (
> > select sum(A.Distance) distance, datediff(s,B.arrivaltime,
> > C.arrivaltime) as gen
> > from yourtable A
> > left outer join
> > (
> > select id, distance, arrivaltime from yourtable
> > where ArrivalTime is not null
> > ) B
> > on B.id < A.id
> > left outer join
> > (
> > select id, distance, arrivaltime from yourtable
> > where ArrivalTime is not null
> > ) C
> > on C.id > A.id
> > where A.arrivaltime is null
> > group by
> > datediff(s,B.arrivaltime, C.arrivaltime)
> > ) G
> > on E.gen = G.gen
> > )
> > union
> > (
> > select ID, Distance, Arrivaltime from yourtable where ArrivalTime is
> > not null
> > )
>
> Check up a section on interpolation inhttp://vadimtropashko.files.wordpress.com/2007/02/ch3.pdf
> SQL query for Lagrange interpolation
>
> select x, sum(y*mul) interp_Y from (
> select x,j,y, product(a) mul
> from (
> select bb.X x, bj.X j, bj.Y y, (bb.X-bk.X)/(bj.X-bk.X) a
> from data bj,data bk, data bb
> where bj.Y is not null and bk.Y is not null
> and bj.X!=bk.X
> ) group by x,j,y
> ) group by x
>
> is almost as concise as math formula! We draw a polynomial curve
> through a set of n points -- it must be that the solution is naturally
> expressed in a set oriented programming language, such as SQL...

Indeed. Thank you for bringing that concise insight. My pupose here is not that much about the effectiveness, correctness of the interpolating formula but triggering some thought about its application in the handling of missing data in in better way than using 3VL. My belief (and hope) would be that using interpolation could represent a method more *systematic* (and therefore more easy to program in a dbms) to handle missing information. Quite frankly, I am having difficulty conceiving that seom day a dbms would be able to automate vertical/horizontal decomposition. I have more hope into either relation subtyping and/or datum value interpolation. I would be grateful to hear your opinion on that subject. Received on Tue May 01 2007 - 09:26:51 CEST