See last sentence of paragraph 3 of
https://www.sqlite.org/windowfunctions.html#built_in_window_functions
Perhaps the following?
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 t,
(select v
from s1
where t <= ts.t
and v is not null
order by t desc) as s1v,
(select v
from s2
where t <= ts.t
and v is not null
order by t desc) as s2v,
(select v
from s3
where t <= ts.t
and v is not null
order by t desc) as s3v
from ts
order by t;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <[email protected]> On
>Behalf Of [email protected]
>Sent: Saturday, 28 December, 2019 20:45
>To: [email protected]
>Subject: [sqlite] last_value() without nulls?
>
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
