How to add a running number column to a view which generates a limited series of randomly chosen integers?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

How to add a running number column to a view which generates a limited series of randomly chosen integers?

Shane Dev
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

petern
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@...
e.org/msg107018.html




On Sun, Jan 7, 2018 at 10:37 PM, Shane Dev <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

Shane Dev
On 8 January 2018 at 09:19, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

R Smith-2

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

Shane Dev
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

Simon Slavin-3
On 8 Jan 2018, at 8:42pm, Shane Dev <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

Shane Dev
On 8 January 2018 at 21:58, Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

R Smith-2
In reply to this post by Shane Dev
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

Donald Griggs
In reply to this post by Shane Dev
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

Simon Slavin-3
In reply to this post by Shane Dev
On 8 Jan 2018, at 9:55pm, Shane Dev <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to add a running number column to a view which generates a limited series of randomly chosen integers?

Shane Dev
In reply to this post by Donald Griggs
Thanks Donald. So simple in hindsight

On 8 January 2018 at 23:20, Donald Griggs <[hidden email]> wrote:

>
>      select random() > 0;  -- random zero or one
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users