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

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?

CodePudding user response：

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);

CodePudding user response：

Just look 19 timestamps back with LAG:

create table boll as 
  select *,
  case when lag(timestamp, 19) over (rows between 19 preceding and current) is null then
    null
  else
    avg(close) over win) - 2 * sqrt((avg(close*close) over win) - pow((avg(close) over win), 2)
  end as BOLD,
  case when lag(timestamp, 19) over (rows between 19 preceding and current) is null then
    null
  else
    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);