Hi,
Many possible solutions. If [xi] is the date for which you
want extrapolation, the (modified) Lagrangian interpolation (extrapolation)
would be, quite simply:
==================
SELECT SUM(eta)
FROM ( SELECT b.y * EXP(SUM(LOG( ABS(
( [xi] - a.x ) / ( b.x - a.x )
) ) ) ) As
eta
FROM myTable As a INNER JOIN myTable As b
ON a.x <> b.x
)
====================
where myTable has two fields, x and y: x is the field name for the
date_time, and y is the measure you read, at that moment.
The Lagrangian intra/extrapolation, given three points (x1, y1), (x2, y2),
(x3, y3) is
y = y(x)
= 1* ((x-x2)/(x1-x2)) * ((x-x3)/(x1-x3)) * y1
+
((x-x1)/(x2-x1)) * 1* ((x-x3)/(x2-x3)) * y2
+
((x-x3)/(x3-x1)) *((x-x2)/(x1-x2)) * 1 * y3
It passes exactly by the three points (n, in general) as you can easily
see plugging x=x1, or x2, or x3, in the previous formula.
I modified it adding ABS around each terms...
It is also a formula easy to program in SQL, once we remember that:
a*b*...*z = exp( ln(a) + ln(b) + ... + ln(z) )
as long as all the values are strictly > 0. That is why I used ABS.
The Lagrangian interpolation/extrapolation is known to behave poorly for
extrapolation, and is generally used only for interpolation (ie, between
xmin and xmax), because the dominant terms will make the function like
y = x^n
"far" from any supplied points, which is possible when extra-pollation is
used.
The query is interesting in many aspects, one noticeable one is its
inner join based on <>, rather than the more usual = .
Hoping you may help,
Vanderghast, Access MVP
