SQLite Window functions: give NULL result when sufficient window size is not available

Question

create table boll as select
     *, (avg(close) over win)-2*sqrt((avg(close*close) over win)-pow((avg(close) over win),2)) as BOLD,
     (avg(close) over win)+2*sqrt((avg(close*close) over win)-pow((avg(close) over win),2)) as BOLU
     from bhav
     window win as (partition by isin order by timestamp rows 19 preceding);

With the above query, is there a way to return NULL from calculations over window win if there are less than 19 rows preceding the current?

Answer 1

You can use COUNT() window function over the window that you define to check how many rows there are:

create table boll as 
select *, 
       CASE WHEN COUNT(*) OVER win >= 19 THEN (avg(close) over win) - 2 * sqrt((avg(close*close) over win)-pow((avg(close) over win),2)) END as BOLD,
       CASE WHEN COUNT(*) OVER win >= 19 THEN (avg(close) over win)+2*sqrt((avg(close*close) over win)-pow((avg(close) over win),2)) END as BOLU
from bhav
window win as (partition by isin order by timestamp rows 19 preceding);