Generate multiple random numbers in sqlite

Question

I would like to create size N sets of random numbers in Sqlite.

Currently, the best I have is to combine a collection of random() calls like this

 create view random_view as select random() union select random() union select random() union select random() union select random() ; 

Maybe someone has a smarter solution that could generate N numbers?

Answer 1

Not tried, but maybe it? For any table consisting of at least N rows,

 SELECT RANDOM() FROM anytable LIMIT ? 

replacing ? on N.

Answer 2

 create temp table rtab (x integer primary key, v default (random())); create temp trigger rtrig before insert on rtab when new.x > 0 begin insert or replace into rtab (x) values (new.x - 1); end; PRAGMA recursive_triggers = on; insert into rtab (x) values (9); sqlite> select * from rtab; 0|-6742468521271879323 1|-8020364160821973904 2|4567559116463208288 3|5330277995838989553 4|-9000358059551141276 5|-7148307065140334921 6|8156512560793181351 7|-10751076681323044 8|-7335834651732027766 9|6837665741304560539 sqlite> 

Answer 3

If you need millions of lines and there is shell / bash, this might be good enough:

echo -e '.import "/dev/stdin" mytable\n'; sleep 0.5 ; for i in {1..10000000}; do echo $RANDOM; done) | sqlite3 my.db

The wait phase is critical to prevent sqlite from being read from the first numbers as SQL commands due to buffering. Ymmv

Answer 4

You can use a recursive query .

This query generates a table with 1000 random numbers:

 CREATE TABLE test(field1); INSERT INTO test WITH RECURSIVE cte(x) AS ( SELECT random() UNION ALL SELECT random() FROM cte LIMIT 1000 ) SELECT x FROM cte;