Get X number of random integer numbers between A and B

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

Get X number of random integer numbers between A and B

Bart Smissaert
Say I want 10000 random numbers between 100 and 1000 how can I do that
without
selecting from a table?
I know I can do:
select abs(random() %(1000 - 100)) + 100 as rnd from TableWith10000Rows
but there must be a better way.

RBS
_______________________________________________
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: Get X number of random integer numbers between A and B

Stephan Beal-3
On Fri, Jan 22, 2016 at 12:11 PM, Bart Smissaert <[hidden email]>
wrote:

> Say I want 10000 random numbers between 100 and 1000 how can I do that
> without
> selecting from a table?
> I know I can do:
> select abs(random() %(1000 - 100)) + 100 as rnd from TableWith10000Rows
> but there must be a better way.
>
>
Maybe not perfect, but this seems to work...

sqlite> with conf(max) as (select 10), rnd(n, x) as (select abs(random()
%(1000 - 100)) + 100, 1 union all select abs(random() %(1000 - 100)) + 100,
x+1 from rnd where x<(select max from conf)) select * from rnd;
668|1
301|2
619|3
380|4
412|5
263|6
563|7
877|8
573|9
468|10

just swap out the 'conf' part with 1000.



--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
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: Get X number of random integer numbers between A and B

Stephan Beal-3
On Fri, Jan 22, 2016 at 12:22 PM, Stephan Beal <[hidden email]>
wrote:

>
>
> On Fri, Jan 22, 2016 at 12:11 PM, Bart Smissaert <[hidden email]
> > wrote:
>
>> Say I want 10000 random numbers between 100 and 1000 how can I do that
>> without
>> selecting from a table?
>> I know I can do:
>> select abs(random() %(1000 - 100)) + 100 as rnd from TableWith10000Rows
>> but there must be a better way.
>>
>>
> Maybe not perfect, but this seems to work...
>
> sqlite> with conf(max) as (select 10), rnd(n, x) as (select abs(random()
> %(1000 - 100)) + 100, 1 union all select abs(random() %(1000 - 100)) + 100,
> x+1 from rnd where x<(select max from conf)) select * from rnd;
>

Another variant which moves all the configurable bits up one level:

sqlite> with conf(max,slack) as (select 20, 2), rnd(n, x) as (select
abs(random() %(max - slack)) + slack, 1 from conf union all select
abs(random() %(max - slack)) + slack, x+1 from rnd, conf where x<conf.max)
select * from rnd;
6|1
3|2
10|3
18|4
12|5
5|6
2|7
2|8
11|9
18|10
16|11
7|12
9|13
13|14
10|15
3|16
17|17
2|18
17|19
19|20

To get the range from your original description, swap out max/slack with
1000/100, but if i'm not mistaken it will returns the range inclusive range
[0,999].

It also works as a view:

sqlite> create view rng10 as with conf(max,slack) as (select 10, 0), rnd(n,
x) as (select abs(random() %(max - slack)) + slack, 1 from conf union all
select abs(random() %(max - slack)) + slack, x+1 from rnd, conf where
x<conf.max) select * from rnd;
sqlite> select * from rng10;
5|1
0|2
3|3
2|4
4|5
8|6
3|7
1|8
1|9
1|10
sqlite> select * from rng10;
6|1
5|2
0|3
1|4
5|5
3|6
3|7
4|8
5|9
1|10

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
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: Get X number of random integer numbers between A and B

Bart Smissaert
In reply to this post by Stephan Beal-3
Thanks, that works very well and is a lot faster than involving a table.
I don't need the x output, so I have done:

with conf(max) as
(select 10000), rnd(n, x) as
(select abs(random() %(1000 - 100)) + 100, 1 union all
select abs(random() %(1000 - 100)) + 100, x + 1
from rnd where x < (select max from conf))
select n from rnd

Not familiar with CTE's and it looks quite complex.

RBS

On Fri, Jan 22, 2016 at 11:22 AM, Stephan Beal <[hidden email]>
wrote:

> On Fri, Jan 22, 2016 at 12:11 PM, Bart Smissaert <[hidden email]
> >
> wrote:
>
> > Say I want 10000 random numbers between 100 and 1000 how can I do that
> > without
> > selecting from a table?
> > I know I can do:
> > select abs(random() %(1000 - 100)) + 100 as rnd from TableWith10000Rows
> > but there must be a better way.
> >
> >
> Maybe not perfect, but this seems to work...
>
> sqlite> with conf(max) as (select 10), rnd(n, x) as (select abs(random()
> %(1000 - 100)) + 100, 1 union all select abs(random() %(1000 - 100)) + 100,
> x+1 from rnd where x<(select max from conf)) select * from rnd;
> 668|1
> 301|2
> 619|3
> 380|4
> 412|5
> 263|6
> 563|7
> 877|8
> 573|9
> 468|10
>
> just swap out the 'conf' part with 1000.
>
>
>
> --
> ----- stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
> Wolf
> _______________________________________________
> 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: Get X number of random integer numbers between A and B

Bart Smissaert
In reply to this post by Stephan Beal-3
Thanks, will look at that as well.
I was thinking achieving this with a user function,
maybe using sqlite3_randomness, but I this looks very good.

RBS

On Fri, Jan 22, 2016 at 12:39 PM, Stephan Beal <[hidden email]>
wrote:

> On Fri, Jan 22, 2016 at 12:22 PM, Stephan Beal <[hidden email]>
> wrote:
>
> >
> >
> > On Fri, Jan 22, 2016 at 12:11 PM, Bart Smissaert <
> [hidden email]
> > > wrote:
> >
> >> Say I want 10000 random numbers between 100 and 1000 how can I do that
> >> without
> >> selecting from a table?
> >> I know I can do:
> >> select abs(random() %(1000 - 100)) + 100 as rnd from TableWith10000Rows
> >> but there must be a better way.
> >>
> >>
> > Maybe not perfect, but this seems to work...
> >
> > sqlite> with conf(max) as (select 10), rnd(n, x) as (select abs(random()
> > %(1000 - 100)) + 100, 1 union all select abs(random() %(1000 - 100)) +
> 100,
> > x+1 from rnd where x<(select max from conf)) select * from rnd;
> >
>
> Another variant which moves all the configurable bits up one level:
>
> sqlite> with conf(max,slack) as (select 20, 2), rnd(n, x) as (select
> abs(random() %(max - slack)) + slack, 1 from conf union all select
> abs(random() %(max - slack)) + slack, x+1 from rnd, conf where x<conf.max)
> select * from rnd;
> 6|1
> 3|2
> 10|3
> 18|4
> 12|5
> 5|6
> 2|7
> 2|8
> 11|9
> 18|10
> 16|11
> 7|12
> 9|13
> 13|14
> 10|15
> 3|16
> 17|17
> 2|18
> 17|19
> 19|20
>
> To get the range from your original description, swap out max/slack with
> 1000/100, but if i'm not mistaken it will returns the range inclusive range
> [0,999].
>
> It also works as a view:
>
> sqlite> create view rng10 as with conf(max,slack) as (select 10, 0), rnd(n,
> x) as (select abs(random() %(max - slack)) + slack, 1 from conf union all
> select abs(random() %(max - slack)) + slack, x+1 from rnd, conf where
> x<conf.max) select * from rnd;
> sqlite> select * from rng10;
> 5|1
> 0|2
> 3|3
> 2|4
> 4|5
> 8|6
> 3|7
> 1|8
> 1|9
> 1|10
> sqlite> select * from rng10;
> 6|1
> 5|2
> 0|3
> 1|4
> 5|5
> 3|6
> 3|7
> 4|8
> 5|9
> 1|10
>
> --
> ----- stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
> Wolf
> _______________________________________________
> 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