Hi everyone,
I am trying to correlate several sparse time series with disjunct time-points
in such a way that null values (via left join) are replaced by the most recent
value in each time series respectively.
Each series only records changes in values, which are then assumed to remain
constant until the next change.
Here's a small self-contained example with various attempts at getting the
expected results:
with
s1( t, v ) as ( values ( 1, 's1-a' ), ( 3, 's1-c' ) ),
-- series 1
s2( t, v ) as ( values ( 1, 's2-a' ), ( 4, 's2-d' ) ),
-- series 2
s3( t, v ) as ( values ( 2, 's3-b' ), ( 6, 's3-f' ) ),
-- series 3
ts( t ) as ( select t from s1 union select t from s2 union select t from s3
) -- all time stamps
select
ts.t as t,
s1.v as s1_v,
-- what I would like/expect to work
last_value( s1.v )
-- filter ( where s1.v is not null ) -- error
over ( order by s1.t ) as s1_lv,
-- further (failed) attempts...
last_value( s2.v )
over ( order by s2.t rows unbounded preceding ) as s2_lv_rows,
last_value( s3.v )
over ( order by s3.t rows unbounded preceding ) as s3_lv_rows,
last_value( s3.v )
over ( order by s3.t range unbounded preceding ) as s3_lv_range,
last_value( s3.v )
over ( order by s3.t groups unbounded preceding ) as s3_lv_groups,
-- no idea what this could be useful for - taken from sqlite docs
last_value( s3.v )
over ( order by s3.t
rows between unbounded preceding and unbounded following
) as s3_lv_all
from
ts
left join s1 on s1.t = ts.t
left join s2 on s2.t = ts.t
left join s3 on s3.t = ts.t
order by
ts.t
;
Results:
t s1_v s1_lv s2_lv_rows s3_lv_rows s3_lv_range s3_lv_groups s3_lv_all
- ---- ----- ---------- ---------- ----------- ------------ ---------
1 s1-a s1-a s2-a ~ ~ ~ s3-f
2 ~ ~ ~ s3-b s3-b s3-b s3-f
3 s1-c s1-c ~ ~ ~ ~ s3-f
4 ~ ~ s2-d ~ ~ ~ s3-f
6 ~ ~ ~ s3-f s3-f s3-f s3-f
Expected results:
t s1_v s1_lv s2_lv_rows s3_lv_rows s3_lv_range s3_lv_groups s3_lv_all
- ---- ----- ---------- ---------- ----------- ------------ ---------
1 s1-a s1-a s2-a ~ ~ ~ s3-f
2 ~ s1-a s2-a s3-b s3-b s3-b s3-f
3 s1-c s1-c s2-a s3-b s3-b s3-b s3-f
4 ~ s1-c s2-d s3-b s3-b s3-b s3-f
6 ~ s1-c s2-d s3-f s3-f s3-f s3-f
The "filter ( where ... is not null )" clause *seems* like it should do what I
want, but it just produces the error: "FILTER clause may only be used with
aggregate window functions" :-(
In oracle I would use "last_value( ... ) ignore nulls over ( ... )".
Is this somehow possible in SQLite (3.30.1)?
Should I open a ticket?
Thanks,
Steve
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
