Re: Interpolation problem - pg 12.4 - full correct version!

On 8/19/20 3:08 PM, David G. Johnston wrote:
> On Wed, Aug 19, 2020 at 11:51 AM Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie
> <mailto:linehanp(at)tcd(dot)ie>> wrote:
>
>
> I think my *MAJOR* problem is that I've developed what is,
> essentially, a totally brute force approach - and this simply won't
> work at the scenario becomes more complex - take a look at the CASE
> statement - it's horrible and would only become exponentially worse as
> the number NULLs rises.
>
> So, my question is: Is there a recognised technique (using SQL only,
> not PL/pgSQL - soutions based on the latter are easy to find) whereby
> I can do a basic Linear Interpolation?
>
> I don't have a recognized technique, nor care to ponder one right now, but what
> you've described would best be done in pure SQL using WITH RECURSIVE, which
> provides an iterative approach to SQL result building.  Which is more commonly
> done in a procedural language.  The algorithm you describe is an iterative
> algorithm and so I'm wondering why place the arbitrary restriction on using pure
> SQL when it likely wouldn't provide a very readable nor performant solution
> relative to a procedural (pl/pgsql or otherwise) one?

Might want to consider using PL/R with something like the R imputeTS package:

https://cran.r-project.org/web/packages/imputeTS/readme/README.html

HTH,

Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development