> On 29. Dec 2019, at 19:27, Keith Medcalf <kmedc...@dessus.com> wrote: > > > See last sentence of paragraph 3 of > https://www.sqlite.org/windowfunctions.html#built_in_window_functions
I knew I'd seen that sentence somewhere :-) > Perhaps the following? > [...] It works! OK, ever since we gained CTE's I've avoided embedded queries because, for my brain, they're hard to layout, understand, test, etc... Yours appear to only return the first matching row for each value of ts.t via an implicit "limit 1" - didn't know that... hmmm... Even so... would I be completely mad for attempting to lift the "built-in window functions can't be filtered" limitation? Is there a hard and fast reason for why they can't use pre-filtered partitions? If only for the linguistic clarity of last_value() over your embedded query solution, I'd like to have a go at it - but not if you know in advance that I'll only end up banging my head on the wall. Thanks for the quick turn around and working solution! (not to mention SQLite itself - awesome project!) Steve > -- > 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 <sqlite-users-boun...@mailinglists.sqlite.org> On >> Behalf Of ja...@opensauce.de >> Sent: Saturday, 28 December, 2019 20:45 >> To: sqlite-users@mailinglists.sqlite.org >> 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 >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
