How to get sliding value for discrete data?

Question

MySQL

The table keeps track of the readings of some counter. Values ​​grow unevenly.
Columns id (INT), n (INT), ts (TIMESTAMP).

Measurements take place at random times, irregularly, about 70-150 times a day.

Interested in growth curves over a time interval. For a day, for a week. Compare counter at time T with time T – 24 HOUR. Or T and T - 7 DAYS. Despite the fact that there was no measurement exactly 24 hours ago. There were some neighboring -25 hours and -23 hours, let's say.

We need interpolation of values, and from the calculated intermediate values ​​we can already get an increase for the period at any moment. Let's say in increments of 1 hour.

Are there any suitable functions for this in MySQL, or is it worth considering a solution outside of MySQL?

Haven't dealt with window functions in MySQL before.

Answer 1

An example with linear interpolation.

WITH 
cte1 AS ( SELECT t1.ts, t1.val,
                 t2.ts ts_before, t2.val val_before,
                 t3.ts ts_after, t3.val val_after
          FROM test t1
          JOIN test t2 ON t1.ts >= TIMESTAMP(t2.ts, @delta)
          JOIN test t3 ON t1.ts <= TIMESTAMP(t3.ts, @delta) ),
cte2 AS ( SELECT *, 
                 ROW_NUMBER() OVER (PARTITION BY ts ORDER BY ts_before DESC) rn_before, 
                 ROW_NUMBER() OVER (PARTITION BY ts ORDER BY ts_after ASC) rn_after
          FROM cte1 )
SELECT ts,
       val,
       ts_before,
       val_before,
       ts_after,
       val_after,
       CASE WHEN val_after = val_before
            THEN val_before
            ELSE val_before + (val_after - val_before) / TIMESTAMPDIFF(SECOND, ts_after, ts_before) * TIMESTAMPDIFF(SECOND, ts, TIMESTAMP(ts_before, @delta)) 
            END val_approximated
FROM cte2 
WHERE ts > '2021-01-02'
      AND rn_before = 1
      AND rn_after = 1

DEMO fiddle with some explanation.
Of course, slowing down on a large array will be merciless - so it would be nice to set sane boundaries for all three copies in the first CTE from and to ...

Haven't dealt with window functions in MySQL before.

And it will have to. And not just "to deal with", but to study well, until you fully understand.