Rolling Average in sqlite

Question

I want to calculate a rolling average in a table and keep track of the starting time of each calculated window frame.

My problem is, that I expect result count reduced compared of the rows in the table. But my query retuns the exact same row number. I think I understand why it does not work, but I don't know the remedy.

Let's say I have a table with example data that looks like this:

+------+-------+
| Tick | Value |
+------+-------+
|    1 |     1 |
|    2 |     3 |_
|    3 |     5 |
|    4 |     7 |_
|    5 |     9 |
|    6 |    11 |_
|    7 |    13 |
|    8 |    15 |_
|    9 |    17 |
|   10 |    19 |_
+------+-------+

I want to calculate the average of every nth item, for example of two rows (see marks above) so that I get an result of:

+--------------+--------------+
| OccurredTick | ValueAverage |
+--------------+--------------+
|            1 |            2 |
|            3 |            6 |
|            5 |           10 |
|            7 |           14 |
|            9 |           18 |
+--------------+--------------+

I tried that with

SELECT 
    FIRST_VALUE(Tick) OVER (
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) OccurredTick,
    AVG(Value) OVER (
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) ValueAverage 
FROM TableName;

What I get in return is:

+--------------+--------------+
| OccurredTick | ValueAverage |
+--------------+--------------+
|            1 |            2 |
|            2 |            4 |
|            3 |            6 |
|            4 |            8 |
|            5 |           10 |
|            6 |           12 |
|            7 |           14 |
|            8 |           16 |
|            9 |           18 |
|           10 |           19 |
+--------------+--------------+

GMB · Accepted Answer · 2020-04-03T14:00:18.757

You could use aggregation. If tick is always increasing with no gaps:

select min(tick), avg(value) avg_value
from mytable
group by cast((tick - 1) / 2 as integer)

You can change 2 to whatever group size suits to best.

If tick are not sequentially increasing, we can generate a sequence with row_number()

select min(tick), avg(value) avg_value
from (
    select t.*, row_number() over(order by tick) rn
    from mytable t
) t
group by cast((rn - 1) / 2 as integer)

Rolling Average in sqlite

1 Answers1