SQLite Select SUM Rolling Window-CodePudding

I have this table in SQLite

Table [Ticks]
Fields: 2
    [Value]: INT
    [Time]: DATETIME

And I want to select a window or partition of 10 hours of values and make a sum of those values then move one row forward and do the same for last 10 hours through the whole range of records.

The value field contains -1 or 1

How can I achieve this? Is this possible with the WINDOW, PARTITION query?

CodePudding user response：

You can convert the time to seconds and then use range():

select t.*,
       sum(value) over (order by strftime('%s', time)   0
                        range between 35999 preceding and current row
                       ) as sum_10hours
from ticks t;

The strftime() expression converts the value to seconds. The range takes (106060 - 1) seconds before to the current row.

Here is a db-fiddle.