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

From: Vadim Tropashko <vadimtro_invalid_at_yahoo.com>
Date: 30 Apr 2007 16:42:42 -0700
Message-ID: <1177976562.220425.266270@h2g2000hsg.googlegroups.com>

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 in
http://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... Received on Mon Apr 30 2007 - 18:42:42 CDT