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...

I will attempt to adapt your formula into this case example. It's quite elegant (given it's expressed in SQL it just makes it caviar ;)) Received on Tue May 01 2007 - 09:29:52 CEST