How to add a numerical value into a window frame in SQLite?

Question

I am having some difficulty in adding a numerical digit into my windows frame specification in SQLite. I am using R in SQLITE. Although if you know how to do this in SQL then that's also helpful.

Here is a link to the sqlite window function documentation - although it's abit hard to understand as to where i should place my numerical value.

https://www.sqlite.org/windowfunctions.html

In particular i am looking at the frame boundary section.

I kepe receiving the error message:

Error: unsupported frame specification 

Any ideas?

My code is the following:

"create temp table forward_looking as 
              SELECT *,  
        COUNT( CASE channel WHEN 'called_office'  THEN 1 ELSE null END)
        OVER (PARTITION by special_digs 
        ORDER BY time 
        RANGE FOLLOWING 604800) 
      AS new_count
      from my_data
    ")

Basically the code should look at the time column which is in unix epoch time and then find 7 days in advance (which is 604800 in unix time) then add a count to new_count. And do this on a row by row term.

I think I may have the numeric in the RANGE FOLLOWING part the wrong way around??

Answer 1

I think that you want:

create temp table forward_looking as 
select 
    d.*,  
    count(*) filter(where channel <> 'called_office') over (
        partition by special_digs 
        order by time 
        range between current row and 604800 following
    ) as new_count
from my_data d

That is, the range clause requires a starting and ending specification (between ... and ...).

Note that I also modified the window function to use the standard filter clause, which makes the logic more obvious.