Re: [sqlite] [EXTERNAL] Re: random rows
Good idea but if there is no AUTOINCREMENT you can simply get the min/max directly: I think the following works (and for both + and - rowid's) NB: The MaxRowID is not needed with Rows(MinRowID, NumRows) as (select min(RowID) as MinRowID, max(RowID) - min(RowID) + 1 as NumRows from tab), Potential(Candidate) as (select abs(random() % (select NumRows from Rows)) + (select MinRowID - 1 from Rows) as Candidate from generate_series where start=1 and stop=(select 2*NumRows from Rows)) select distinct tab.* from Potential cross join tab on tab.RowID == Potential.Candidate limit 200; NB: dependancy on extension series.c -> generate_series Selecting 200 random rows from tab containing 1 million rows takes 0.2 seconds ... from tab with 10 million rows takes 2 seconds SQLite version 3.24.0 2018-06-01 13:30:45 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .version SQLite 3.24.0 2018-06-01 13:30:45 8cadaf587dc96370f9c8a1dccc366b93021e8cfe4526da9368a088828fd1alt2 zlib version 1.2.11 gcc-7.3.0 sqlite> create table tab (RowID integer primary key, uuid text not null); sqlite> insert into tab (RowID, uuid) select value-500, uuidStringCreateV4() from generate_series where start=1 and stop=1000; sqlite> select min(rowid), max(rowid) from tab; -499|500 sqlite> with Rows(MinRowID, NumRows) as (select min(RowID) as MinRowID, ...> max(RowID) - min(RowID) + 1 as NumRows ...>from tab), ...> Potential(Candidate) as (select abs(random() % (select NumRows from Rows)) + (select MinRowID - 1 from Rows) as Candidate ...> from generate_series ...> where start=1 ...> and stop=(select 2*NumRows from Rows)) ...> select distinct tab.* ...> from Potential ...> cross join tab ...> on tab.RowID == Potential.Candidate ...> limit 200; 4021141|7234943b-9831-48fe-9558-da59367fba61 -3898799|428d5d0a-f48e-4e01-bb69-1ad427fa64c8 -552665|cc3c6570-4bfc-4650-a32e-1cd0bd9b4450 4629777|1edcba39-3253-488a-953b-bbdd4a870e21 4901776|72ba152f-6de2-46f1-8702-2a0f688096d4 ... -3575561|30cf7391-55ae-4b37-be3a-bc318fe5d94e -1672703|251ea02e-4a8f-4ad6-8466-29ebcc42e11e -3032716|f547c0f1-3f74-4f8b-a79c-434219e4c2ab 3654895|b861d1b9-34f7-42d8-91de-b028d49d70c9 -1880955|26d771c0-8ea8-4b54-9aa1-6171e5b00249 4578402|33966112-1fdf-472d-aa78-697be0775848 Run Time: real 1.767 user 1.765625 sys 0.00 --- 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 [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter >>Sent: Friday, 1 June, 2018 12:09 >>To: 'SQLite mailing list' >>Subject: Re: [sqlite] [EXTERNAL] Re: random rows >> >>Just an idea: >> >>If the table has an INTEGER PRIMARY KEY AUTOINCREMENT then then >>maximum rowid is available in the sqlite_sequence table. >>To generate a random sequence of rowids, this can be used, somewhat >>like >> >>SELECT DISTINCT rnd() * (SELECT seq FROM sqlite_sequence WHERE >>name='') as rowid; >> >>Maybe as a view or a CTE. >> >>Then use this as the LHS of a join >> >>SELECT t.* from (...) random cross join t on >>random.rowid = t.rowid LIMIT x; >> >>Creating random rowids should be blindingly fast, even with storing >>the already created rowids in a temp btree. Reading the table via >>rowid is as fast as possible. Any "misses" in the rowid generation >>(i.e. rowids not present) are compensated by the number of rowids >>generated. Speed increases with the desity of rowids (i.e. >>/). Unless of course SQLite >>chooses to create all the rowids before providing the first one. >> >>-Ursprüngliche Nachricht- >>Von: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] Im Auftrag von Stephen Chrzanowski >>Gesendet: Freitag, 01. Juni 2018 18:47 >>An: SQLite mailing list >>Betreff: [EXTERNAL] Re: [sqlite] random rows >> >>Here's my two cents. Don't spend it all in one place... >> >>CREATE TABLE [RandomTable]( >> [PriID] INTEGER PRIMARY KEY AUTOINCREMENT, >> [DataCol] CHAR); >> >>INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(1, 'a'); INSERT >>INTO [RandomTable]([PriID], [DataCol]) VALUES(2, 'b'); INSERT INTO >>[RandomTable]([PriID], [DataCol]) VALUES(3, 'c'); INSERT INTO >>[RandomTable]([PriID], [DataCol]) VALUES(4, 'd'); INSERT INTO >>[RandomTable]([PriID], [DataCol]) VALUES(5, 'e'); INSERT INTO >>[RandomTable]([PriID], [DataCol]) VALUES(6, 'f'); INSERT INTO >>[RandomTable]([PriID], [DataCol])
Re: [sqlite] Size of the SQLite library
On May 31, 2018, at 6:32 PM, Roger Binns wrote: > > On 31/05/18 10:15, Richard Hipp wrote: >> Size is still important. But having useful features is important too. >> I'm continuing to work to find the right balance between these >> competing goals. > > A pattern used in other projects is to have standard downloads, as well > as custom ones. Your jQuery example later on doesn’t much apply here, for several reasons: 1. JavaScript is a dynamic language, while C is a statically-compiled language. That means that all of the symbols needed to link the program need to be available at link time in order to produce a binary. To achieve that with C, you’d have to do things like create mock modules that export an API but don’t implement it, merely to placate the linker. Contrast a language like JavaScript, where you can ship a program that has calls to functions that don’t exist, and as long as you continue to not call those functions, the JS VM won’t balk. 2. There are ways around this with C, such as with the plugin pattern — which is in fact already being used in SQLite’s VFS layer — but it carries an indirection overhead. jQuery is a bad exemplar here because it’s a solution for implementing user-facing actions, which means that as long as each group of actions implemented using it take under 100 ms or so, it’s fast enough. For SQLite, though, adding layers of abstraction merely for the programmer’s convenience means slowing it down materially, which can turn a viable solution into failure. Some sage once opined that any problem in computer science can be solved by adding another layer of abstraction, but there is one that can’t: “The software is too slow, and we’re unwilling to buy more hardware.” 3. SQLite already has a way to generate multiple versions of the source code in a programmatic way: #ifdefs. That’s precisely what the C preprocessor does. JavaScript has no equivalent mechanism, so someone had to go an factor jQuery into modules by hand and rely on the user to provide the correct subset of modules needed by their software. One could write a variant of cpp that would run on the sqlite3.c amalgamation to produce predigested subsets without bringing in all of the #includes, but all that’s really needed here are curated sets of -DSQLITE_* flags, since then the end user can use them with the C preprocessor they already have. I’ve a feeling that I’m missing more reasons, but those will suffice. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: random rows
Good idea but if there is no AUTOINCREMENT you can simply get the min/max directly: I think the following might work (and should work for both + and - rowid's) with Rows(MinRowID, MaxRowID, NumRows) as (select min(RowID) as MinRowID, max(RowID) - min(RowID) + 1 as NumRows from tab), Potential(Candidate) as (select abs(random() % (select NumRows from Rows)) + (select MinRowID - 1 from Rows) as Candidate from generate_series where start=1 and stop=(select 2*NumRows from Rows)) select distinct tab.* from Potential cross join tab on tab.RowID == Potential.Candidate limit 200; NB: dependancy on extension series.c -> generate_series Selecting 200 random rows from tab containing 1 million rows takes 0.2 seconds ... --- 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 [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter >Sent: Friday, 1 June, 2018 12:09 >To: 'SQLite mailing list' >Subject: Re: [sqlite] [EXTERNAL] Re: random rows > >Just an idea: > >If the table has an INTEGER PRIMARY KEY AUTOINCREMENT then then >maximum rowid is available in the sqlite_sequence table. >To generate a random sequence of rowids, this can be used, somewhat >like > >SELECT DISTINCT rnd() * (SELECT seq FROM sqlite_sequence WHERE >name='') as rowid; > >Maybe as a view or a CTE. > >Then use this as the LHS of a join > >SELECT t.* from (...) random cross join t on >random.rowid = t.rowid LIMIT x; > >Creating random rowids should be blindingly fast, even with storing >the already created rowids in a temp btree. Reading the table via >rowid is as fast as possible. Any "misses" in the rowid generation >(i.e. rowids not present) are compensated by the number of rowids >generated. Speed increases with the desity of rowids (i.e. >/). Unless of course SQLite >chooses to create all the rowids before providing the first one. > >-Ursprüngliche Nachricht- >Von: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] Im Auftrag von Stephen Chrzanowski >Gesendet: Freitag, 01. Juni 2018 18:47 >An: SQLite mailing list >Betreff: [EXTERNAL] Re: [sqlite] random rows > >Here's my two cents. Don't spend it all in one place... > >CREATE TABLE [RandomTable]( > [PriID] INTEGER PRIMARY KEY AUTOINCREMENT, > [DataCol] CHAR); > >INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(1, 'a'); INSERT >INTO [RandomTable]([PriID], [DataCol]) VALUES(2, 'b'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(3, 'c'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(4, 'd'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(5, 'e'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(6, 'f'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(7, 'g'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(8, 'h'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(9, 'i'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(10, 'j'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(11, 'k'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(12, 'l'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(13, 'm'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(14, 'n'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(15, 'o'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(16, 'p'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(17, 'q'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(18, 'r'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(19, 's'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(20, 't'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(21, 'u'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(22, 'v'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(23, 'w'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(24, 'x'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(25, 'y'); INSERT INTO >[RandomTable]([PriID], [DataCol]) VALUES(26, 'z'); > >select random() as R,* from RandomTable order by R limit 10; > >Using 3.23.1 > > >On Fri, Jun 1, 2018 at 10:22 AM, Don V Nielsen > >wrote: > >> ?? >> >> SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY >> RANDOM() LIMIT x) >> >> Maybe. It is more memory efficient then trying to sort the entire >lot >> of data. >> >> >> On Thu, May 31, 2018 at 7:13 PM Torsten Curdt >wrote: >> >> > I need to get some random rows from a large(ish) table. >> > >> > The following seems to be the most straight forward - but not the >> fastest. >> > >> > SELECT * FROM table ORDER BY random() limit 200 >> > >> > Is there a faster/better approach? >> > >> > cheers, >> > Torsten >> >
Re: [sqlite] SQL Date Import
Yes, and the database will store the data as entered/bound if it cannot be converted to the requested storage type (column affinity). This is VERY IMPORTANT for you to understand fully and completely including all the rules for storage class and affinity conversions and how they are determined. I would recommend that you SAY what you mean, and not confuse things (including yourself) by using "prayerful" data type declarations that are NOT in the proper set (INTEGER / REAL / NUMERIC / TEXT / BLOB) even though you can use funky names like Vintershitzels (which would be an INTEGER), doing so will only confuse you and anyone reading your code because figuring out what is happening depends on engaging the magical secret decoder ring and consumes valuable brain cycles that could otherwise be used for other purposes. https://www.sqlite.org/datatype3.html Also, if you store DATE or DATETIME (ie, '-mm-dd HH:MM:SS.ss') data within a TEXT string in the database, it is NAIVE. That means that it is not localized and not a specific instant in time. In fact it can cover about 34 hours in time depending on how the string is interpreted. Is is localtime? Where is it localtime? Which particular rules were in effect at the time the string was stored? Were they correct? When you retrieve the string later will it be processed using the same rules? Are those rules correct? Or is the string data stored in UT1 (GMT/Zulu)? If it is UT1 then will it get "properly converted" to the correct "localtime"? (eg: Windows (and a few other OS's) requires lots of third-party help to handle conversions between localtime and UT1 where the UT1 time is between "now" and a couple of years ago otherwise it will get an incorrect conversion result. Linux and most other OSes have a full timezone table and will get it right, hopefully most of the time. No one can predict "localtime" for the future unless you happen to be in a fixed-offset timezone that NEVER changes and all the politicians from whom such whims arise are dead.) In short, you are best served by choosing some UT1 based numeric-only time format (UnixTime, Julianday, etc.) and using that and converting those into "user" format for display at the last possible moment, and converting "user input" into your chosen numeric UT1 format as soon as possible on input, and only working with UT1 numeric data. This, of course, means that if you do things by "date" you have to realize that some days may only have 22 hours and some may have 26 hours and you have to handle that yourself, if it is relevant to the processing you are doing. Similarly the starting and ending UT1 times might not be exactly divisible by what you think they ought to be (a political whim may make one year 35 minutes longer and the following 35 minutes shorter that you might otherwise expect). Handling Timezones correctly is extremely difficult if you are dependent on "wall clock time" (localtime). Unless you are a wee company from Redmond that only ever does business in one time offset that is ... (or you can pick and stick to using one "wall clock" such as where the Head Office is located -- one multinational I worked for used PGH time (Pittsburg) and another chose Houston). Note that you CAN specify an "instant in time" when you store a text datetime string by adding the offset indicator and SQLite will respect that. However, it will not store it by default (so you can specify '-mm-dd HH:MM:SS.ss Z' or '-mm-dd HH:MM:SS.ss-05:00'). Of course, storing offset indicators will make the column inherently unsortable (unless they are all the same), but that is how it goes ... you can always apply the builtin datetime() function to convert an explicit "instant in time" string into a naive UT1 string for sorting ... (Note: I usually pick UnixTime or some derivative (Ratadie, RataMonth, etc) depending on what the stamp is used for. For some applications I make up my own stamp format (for example, IESO/AESO data is inherently in 5 minute intervals, so I use "interval number" derived from unixtime)). --- 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 [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of dmp >Sent: Friday, 1 June, 2018 10:52 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] SQL Date Import > >Currently I do not have SQLite setup to run on its own on a computer. >I figured the answer to my question would be faster if it was posed >to this list. > >Given: > >CREATE TABLE exdate ( > id INTEGER, > mydate DATE NOT NULL, > PRIMARY KEY (id) >); > >Once a number, numeric, is stored. Can a command line import >in standard SQL for DATEs be done, if at all? > >INSERT INTO exdate (id, mydate) VALUES(1, '2018-06-01'); > >danap. > >___
Re: [sqlite] [EXTERNAL] Re: random rows
Just an idea: If the table has an INTEGER PRIMARY KEY AUTOINCREMENT then then maximum rowid is available in the sqlite_sequence table. To generate a random sequence of rowids, this can be used, somewhat like SELECT DISTINCT rnd() * (SELECT seq FROM sqlite_sequence WHERE name='') as rowid; Maybe as a view or a CTE. Then use this as the LHS of a join SELECT t.* from (...) random cross join t on random.rowid = t.rowid LIMIT x; Creating random rowids should be blindingly fast, even with storing the already created rowids in a temp btree. Reading the table via rowid is as fast as possible. Any "misses" in the rowid generation (i.e. rowids not present) are compensated by the number of rowids generated. Speed increases with the desity of rowids (i.e. /). Unless of course SQLite chooses to create all the rowids before providing the first one. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Stephen Chrzanowski Gesendet: Freitag, 01. Juni 2018 18:47 An: SQLite mailing list Betreff: [EXTERNAL] Re: [sqlite] random rows Here's my two cents. Don't spend it all in one place... CREATE TABLE [RandomTable]( [PriID] INTEGER PRIMARY KEY AUTOINCREMENT, [DataCol] CHAR); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(1, 'a'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(2, 'b'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(3, 'c'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(4, 'd'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(5, 'e'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(6, 'f'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(7, 'g'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(8, 'h'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(9, 'i'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(10, 'j'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(11, 'k'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(12, 'l'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(13, 'm'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(14, 'n'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(15, 'o'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(16, 'p'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(17, 'q'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(18, 'r'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(19, 's'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(20, 't'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(21, 'u'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(22, 'v'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(23, 'w'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(24, 'x'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(25, 'y'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(26, 'z'); select random() as R,* from RandomTable order by R limit 10; Using 3.23.1 On Fri, Jun 1, 2018 at 10:22 AM, Don V Nielsen wrote: > ?? > > SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY > RANDOM() LIMIT x) > > Maybe. It is more memory efficient then trying to sort the entire lot > of data. > > > On Thu, May 31, 2018 at 7:13 PM Torsten Curdt wrote: > > > I need to get some random rows from a large(ish) table. > > > > The following seems to be the most straight forward - but not the > fastest. > > > > SELECT * FROM table ORDER BY random() limit 200 > > > > Is there a faster/better approach? > > > > cheers, > > Torsten > > ___ > > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Date Import
Currently I do not have SQLite setup to run on its own on a computer. I figured the answer to my question would be faster if it was posed to this list. Given: CREATE TABLE exdate ( id INTEGER, mydate DATE NOT NULL, PRIMARY KEY (id) ); Once a number, numeric, is stored. Can a command line import in standard SQL for DATEs be done, if at all? INSERT INTO exdate (id, mydate) VALUES(1, '2018-06-01'); danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size of the SQLite library
1. Define in documentation as < 1Mb. (Don't have to visit again.) 2. Continue to strive to keep in the 0.5-1MB range. 3. Add some information on building a MINIMUM size for those concerned that is relatively easy to accomplish without a lot of expertise if possible. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] random rows
Here's my two cents. Don't spend it all in one place... CREATE TABLE [RandomTable]( [PriID] INTEGER PRIMARY KEY AUTOINCREMENT, [DataCol] CHAR); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(1, 'a'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(2, 'b'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(3, 'c'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(4, 'd'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(5, 'e'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(6, 'f'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(7, 'g'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(8, 'h'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(9, 'i'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(10, 'j'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(11, 'k'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(12, 'l'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(13, 'm'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(14, 'n'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(15, 'o'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(16, 'p'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(17, 'q'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(18, 'r'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(19, 's'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(20, 't'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(21, 'u'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(22, 'v'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(23, 'w'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(24, 'x'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(25, 'y'); INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(26, 'z'); select random() as R,* from RandomTable order by R limit 10; Using 3.23.1 On Fri, Jun 1, 2018 at 10:22 AM, Don V Nielsen wrote: > ?? > > SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() > LIMIT x) > > Maybe. It is more memory efficient then trying to sort the entire lot of > data. > > > On Thu, May 31, 2018 at 7:13 PM Torsten Curdt wrote: > > > I need to get some random rows from a large(ish) table. > > > > The following seems to be the most straight forward - but not the > fastest. > > > > SELECT * FROM table ORDER BY random() limit 200 > > > > Is there a faster/better approach? > > > > cheers, > > Torsten > > ___ > > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] random rows
?? SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x) Maybe. It is more memory efficient then trying to sort the entire lot of data. On Thu, May 31, 2018 at 7:13 PM Torsten Curdt wrote: > I need to get some random rows from a large(ish) table. > > The following seems to be the most straight forward - but not the fastest. > > SELECT * FROM table ORDER BY random() limit 200 > > Is there a faster/better approach? > > cheers, > Torsten > ___ > 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] random rows
You may find it faster to do: select c from t where rowid in (list-of-constants) and generate the list of constants using something like a blackrock permutation generator. That takes linear time, whereas all the order-by variants are n.log(n). You need some sort of row-id generator function, which you can do with windows/olap. Beware in Oracle, some of the rowid variant functions only increment if you return the row. sqlite seems to have a trick where copying the table creates sequential rowids, by spec, if you don't have one. S. On 05/31/2018 08:28 PM, Jay Kreibich wrote: I’m not entirely sure your solution will have an even distribution. It depends a lot on how many times random() is called (once per row vs once per sort operation), and how the sort algorithm works. I might do this instead: SELECT * FROM (SELECT random(), t.* FROM t) ORDER BY 1 LIMIT 200; As this makes sure random() is only called once per row. I’m pretty sure this is actually equivalent to yours, but it makes things a bit more explicit. If you only needed one, I’d do something like: SELECT * FROM table ORDER BY rowid LIMIT 1 OFFSET abs(random()) % (SELECT count(*) FROM table); Or even just call that multiple times if you need a moderate number. The ORDER BY should be free, but it could also be eliminated. -j On May 31, 2018, at 7:12 PM, Torsten Curdt wrote: I need to get some random rows from a large(ish) table. The following seems to be the most straight forward - but not the fastest. SELECT * FROM table ORDER BY random() limit 200 Is there a faster/better approach? cheers, Torsten ___ 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
