Re: [sqlite] last_value() without nulls?

[jasql]

> On 29. Dec 2019, at 19:27, Keith Medcalf  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  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-bs3-b s3-b  s3-f
>> 3  s1-c  s1-c   ~   ~   ~~ s3-f
>> 4  ~ ~  s2-d~   ~~ s3-f
>> 6  ~ ~  ~   s3-fs3-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-as3-bs3-b s3-b  s3-f
>> 3  s1-c  s1-c   s2-as3-bs3-b s3-b  s3-f
>> 4  ~ s1-c   s2-ds3-bs3-b s3-b  s3-f
>> 6  ~ s1-c   s2-ds3-fs3-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
>> 

Re: [sqlite] last_value() without nulls?

[Keith Medcalf]

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  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-bs3-b s3-b  s3-f
>3  s1-c  s1-c   ~   ~   ~~ s3-f
>4  ~ ~  s2-d~   ~~ s3-f
>6  ~ ~  ~   s3-fs3-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-as3-bs3-b s3-b  s3-f
>3  s1-c  s1-c   s2-as3-bs3-b s3-b  s3-f
>4  ~ s1-c   s2-ds3-bs3-b s3-b  s3-f
>6  ~ s1-c   s2-ds3-fs3-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] Assertion Bug in Sqlite

[Yongheng Chen]

Hi,

We found an assertion bug in Sqlite. Here’s the PoC:

—
CREATE TABLE v0 ( v1 ) ; CREATE TABLE v2 ( v3 INTEGER UNIQUE ON CONFLICT ABORT 
) ; CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( ( SELECT v1 AS PROMO_REVENUE 
FROM v2 JOIN v0 USING ( VALUE ) ) AND 0 ) BEGIN DELETE FROM v2 ; END ; CREATE 
VIRTUAL TABLE v4 USING fts4 ( v5 FLOAT UNIQUE AS( 'AIR' ) , v7 UNIQUE GENERATED 
ALWAYS AS( v1 ) , v9 INT CHECK( NOT v3 < 'BUILDING' ) , v8 INT UNIQUE GENERATED 
ALWAYS AS( NULL ) , v6 ) ; ALTER TABLE zipfile RENAME TO x ;
—

This needs to be run in one line and the bug exists in the latest development 
code.

Yongheng & Rui
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] last_value() without nulls?

[jasql]

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-bs3-b s3-b  s3-f
3  s1-c  s1-c   ~   ~   ~~ s3-f
4  ~ ~  s2-d~   ~~ s3-f
6  ~ ~  ~   s3-fs3-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-as3-bs3-b s3-b  s3-f
3  s1-c  s1-c   s2-as3-bs3-b s3-b  s3-f
4  ~ s1-c   s2-ds3-bs3-b s3-b  s3-f
6  ~ s1-c   s2-ds3-fs3-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

Re: [sqlite] SQL help

[x]

Thanks Barry. I think that’s saying something like what I was saying but I put 
it better than them. I’m buoyed by the fact it’s not just Scottish education 
that could be done gooder 


From: Barry Smith
Sent: 28 December 2019 22:49
To: SQLite mailing list
Subject: Re: [sqlite] SQL help

Why does section 6.4.3.a.i of that linked standard not apply to his sub select?

In the quote below, CR is Column Reference, CN is Column Name.

3) If CR does not contain a , then CR shall be contained
within the scope of one or more s or s whose associated tables include a column whose  is CN. Let the phrase possible qualifiers denote those
s and s.

a) Case:

  i) If the most local scope contains exactly one possible
 qualifier, then the qualifier Q equivalent to that unique
  or  is implicit.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users