Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
Thanks Donald. So simple in hindsight On 8 January 2018 at 23:20, Donald Griggs wrote: > > select random() > 0; -- random zero or one > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
On 8 Jan 2018, at 9:55pm, Shane Dev wrote: > The statement - > > select cast(round(abs(random())/9223372036854775808) as int) > > means I want sqlite to toss a virtual coin and return 0 or 1. Your code is meant to be self-documenting. Any time you find a number like 9223372036854775808 in your code, think hard about what you’re doing. SELECT random() & 1 should return either 0 or 1 on a random basis. & is binary AND, | is binary OR. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
Regarding: select cast(round(abs(random())/9223372036854775808) as int) means I want sqlite to toss a virtual coin and return 0 or 1. It looks like a kludge but I could find neither a simpler nor clearer way of expressing this, can you? Maybe: select abs(random() % 2); -- random zero or one or select random() > 0; -- random zero or one where the comment is the most important part.;-) > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
On 2018/01/08 10:42 PM, Shane Dev wrote: Hi Ryan, Do you mean it would be more efficient to generate entries such as these (with randomly chosen integers and running numbers) in the application and then committing them to the database with sqlite3_exec or similar? For a large number of entries, I assumed there would be greater overhead in preparing and binding the values than both generating and storing them with a single RCTE. I more intended to suggest you generate the consecutive list of numbers in your code . SQLite has to jump through some CTE hoops to do that, but in your code it's as simple as: i = 0; while sqlite_step ... i++; ... The random numbers would be more efficient in your code too, though it is also straight forward in SQL. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
On 8 January 2018 at 21:58, Simon Slavin wrote: > > > num+1, cast(round(abs(random())/9223372036854775808) as int) from > > you’ve probably looking at sanity in the rear view mirror. Suppose > someone has to read your code and figure out what it’s meant to do. If you > expect your code to be read by others, the amount of documentation you'll > have to write takes longer than writing the software properly. The statement - select cast(round(abs(random())/9223372036854775808) as int) means I want sqlite to toss a virtual coin and return 0 or 1. It looks like a kludge but I could find neither a simpler nor clearer way of expressing this, can you? > > > For a large number of entries, I assumed there would be greater overhead > in > > preparing and binding the values than both generating and storing them > with > > a single RCTE. > > SQLite is just software written in C. There’s no reason to believe it’ll > be any more efficiently than your own C code. Even your respect for the > SQLite development team shouldn’t outweigh the advantage of working with > code you write yourself. > > I think Ryan was suggesting to calculate the entries in the main application code and then use sqlite to perform millions of inserts - which would imply a call to sqlite3_exec with an extremely long *sql string (or sqlite3_prepare_v2() with millions of call to sqlite3_bind_int64, etc). Wouldn't it be more efficient call one sqlite3_exec with *sql being a single RCTE which does all the work? An additional advantage is that it relies on sqlite's own c code which I would hope is more thoroughly debugged and portable that my own. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
On 8 Jan 2018, at 8:42pm, Shane Dev wrote: > Do you mean it would be more efficient to generate entries such as these > (with randomly chosen integers and running numbers) in the application and > then committing them to the database with sqlite3_exec or similar? There’s the question of understanding and debugging the code. It’s possible to do strange and complicated things entirely in SQLite. Especially with RCTEs and triggers. But when you see things like num+1, cast(round(abs(random())/9223372036854775808) as int) from you’ve probably looking at sanity in the rear view mirror. Suppose someone has to read your code and figure out what it’s meant to do. If you expect your code to be read by others, the amount of documentation you'll have to write takes longer than writing the software properly. > For a large number of entries, I assumed there would be greater overhead in > preparing and binding the values than both generating and storing them with > a single RCTE. SQLite is just software written in C. There’s no reason to believe it’ll be any more efficiently than your own C code. Even your respect for the SQLite development team shouldn’t outweigh the advantage of working with code you write yourself. How low do you need the overhead to be ? Have you tried a simple solution and found it takes an unacceptable time ? Did you get complaints from your customers ? You can spend two days writing complicated code which shaves 45 milliseconds off your execution time. Code which is difficult to debug and too complicated for other people to understand. Or you could spend those two days adding a useful function to your program. Or lying on a beach. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
Hi Ryan, Do you mean it would be more efficient to generate entries such as these (with randomly chosen integers and running numbers) in the application and then committing them to the database with sqlite3_exec or similar? For a large number of entries, I assumed there would be greater overhead in preparing and binding the values than both generating and storing them with a single RCTE. On 8 January 2018 at 10:23, R Smith wrote: > > On 2018/01/08 11:17 AM, Shane Dev wrote: > >> >> P.S one reason I am using SQL instead the main application to perform such >> calculations is precisely to avoid using variables (and hence the evils of >> mutable state). Why do you say it is more efficient? >> > > Because it is much more efficient, memory used in stead of computations > through the DB engine. It's even vastly more efficient when you do these > things in your programming language (Zero parsing to start with)... > > That however doesn't mean you should only do it this way. There are > obviously merits (such as mutable state evility, if there is such a word) > to using a less efficient method. > > ___ > 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
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
On 2018/01/08 11:17 AM, Shane Dev wrote: P.S one reason I am using SQL instead the main application to perform such calculations is precisely to avoid using variables (and hence the evils of mutable state). Why do you say it is more efficient? Because it is much more efficient, memory used in stead of computations through the DB engine. It's even vastly more efficient when you do these things in your programming language (Zero parsing to start with)... That however doesn't mean you should only do it this way. There are obviously merits (such as mutable state evility, if there is such a word) to using a less efficient method. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
On 8 January 2018 at 09:19, petern wrote: > Your inner CTE will have to examine every generated row and count only > matches toward "running_num". > > Good idea, that works - sqlite> with r(num, rand, running_num) as (select 1, cast(round(abs(random())/9223372036854775808) as int), 1 union all select num+1, cast(round(abs(random())/9223372036854775808) as int), case rand when 1 then running_num+1 else running_num end from r) select running_num, num from r where rand=1 limit 3; running_num num 1 1 2 2 3 6 sqlite> P.S one reason I am using SQL instead the main application to perform such calculations is precisely to avoid using variables (and hence the evils of mutable state). Why do you say it is more efficient? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?
Your inner CTE will have to examine every generated row and count only
matches toward "running_num". You'll also need another column like
"last_running_num" with a referencing CASE statement in the "running_num"
column to condition emitting, for example, a non-null "running_num"...
Or you can simply and more efficiently use a local variable extension like
auxint.c on your existing VIEW (without the LIMIT) like so:
sqlite> .load auxint
sqlite> SELECT auxint('id',1)running_num,num FROM vrand limit 10;
running_num,num
1,1
2,2
3,3
4,4
5,5
6,9
7,11
8,14
9,15
10,16
The ~20 lines of code for auxint.c are here:
https://www.mail-archive.com/sqlite-users@mailinglists.sqlit
e.org/msg107018.html
On Sun, Jan 7, 2018 at 10:37 PM, Shane Dev wrote:
> Hello,
>
> The view VRAND below generates a series of 3 randomly chosen integers -
>
> CREATE VIEW vrand as with r(num, rand) as (
> select 1, cast(round(abs(random())/9223372036854775808) as int)
> union all
> select num+1, cast(round(abs(random())/9223372036854775808) as int) from
> r)
> select num from r where rand=1 limit 3;
>
> sqlite> select * from vrand;
> num
> 1
> 2
> 4
>
> sqlite> select count(*) from vrand;
> count(*)
> 3
>
> Now I would like to add a "running number" column with a result set like -
>
> running_num, num
> 1, 1
> 2, 2
> 3, 4
>
> However, the follow statement seems to trigger an infinite loop -
>
> select (select count(*) from vrand where num <= v.num), num from vrand as
> v;
>
> How can a running number column be added to this view?
> ___
> 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] How to add a running number column to a view which generates a limited series of randomly chosen integers?
Hello, The view VRAND below generates a series of 3 randomly chosen integers - CREATE VIEW vrand as with r(num, rand) as ( select 1, cast(round(abs(random())/9223372036854775808) as int) union all select num+1, cast(round(abs(random())/9223372036854775808) as int) from r) select num from r where rand=1 limit 3; sqlite> select * from vrand; num 1 2 4 sqlite> select count(*) from vrand; count(*) 3 Now I would like to add a "running number" column with a result set like - running_num, num 1, 1 2, 2 3, 4 However, the follow statement seems to trigger an infinite loop - select (select count(*) from vrand where num <= v.num), num from vrand as v; How can a running number column be added to this view? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
