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

user_1921546 0
Viewed 100
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?

window-functions count sqlite average sql
2 Answers

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

Just look 19 timestamps back with LAG:

create table boll as 
  select *,
  case when lag(timestamp, 19) over (order by timestamp) is not null then
    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 (order by timestamp) is not null 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);
user_22707620
Related