sqlite> select kurt(abs(random() % 7)) from generate_series where start=1 and stop=1e6; -1.25154453962449
sqlite> select skew(abs(random() % 7)) from generate_series where start=1 and stop=1e6; 0.00104535938599554 The PRNG is pretty random. It is slightly concave (that is, anti-normal) (a "flat" distribution would have a kurtosis of -1.2) and the curve is slightly skewed above the average. --- 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 Cecil Westerhof >Sent: Sunday, 8 July, 2018 00:59 >To: SQLite mailing list >Subject: Re: [sqlite] Kind of pivot table > >2018-07-08 8:49 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>: > >> >> Why not use MOD (%) as in >> >> ABS(RANDOM() % 6) >> > >You are completely right. How stupid of me. :'-( > >It only has to be: > ABS(RANDOM() % 7) > > > >> >-----Original Message----- >> >From: sqlite-users [mailto:sqlite-users- >> >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >> >Sent: Sunday, 8 July, 2018 00:44 >> >To: SQLite mailing list >> >Subject: Re: [sqlite] Kind of pivot table >> > >> >2018-07-08 8:19 GMT+02:00 Cecil Westerhof ><cldwester...@gmail.com>: >> > >> >> I thought there was a problem with RANDOM. I used: >> >> , ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS >Randomiser >> >> >> >> And it seemed I got a lot of threes. >> >> >> >> To check this I used: >> >> SELECT Randomiser >> >> , COUNT(*) AS Count >> >> FROM ( >> >> SELECT date >> >> , ABS(RANDOM()) / CAST(1.4E18 AS INTEGER) AS >> >Randomiser >> >> FROM CPUUsage >> >> ORDER BY date >> >> ) >> >> GROUP BY Randomiser >> >> ORDER BY Randomiser >> >> >> >> And this gave results like: >> >> "0" "165491" >> >> "1" "166270" >> >> "2" "166207" >> >> "3" "165727" >> >> "4" "165619" >> >> "5" "165749" >> >> "6" "98042" >> >> >> >> So 6 is created less often as 0 - 5, but that is in my use case >not >> >a >> >> problem. >> >> >> >> This worked for me because I have a big table CPUUsage. But if I >> >would not >> >> have, is there another way to to do this? >> >> >> > >> >By the way better select is: >> > SELECT Randomiser >> > , COUNT(*) AS Count >> > FROM ( >> > SELECT date >> > , CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) >AS >> >Randomiser >> > FROM CPUUsage >> > ORDER BY date >> > ) >> > GROUP BY Randomiser >> > ORDER BY Randomiser >> > >> >This gives: >> > "0" "156204" >> > "1" "157032" >> > "2" "155636" >> > "3" "156399" >> > "4" "156256" >> > "5" "155480" >> > "6" "156073" >> > "7" "52" >> > >> >This is much better. Only very rarely you get a seven you do not >> >want. >> >(Again in my case not really a problem.) >> > >> >Because in my case I use Randomiser to get a small subset of the >> >records, >> >this can be solved with: >> > SELECT Randomiser >> > , COUNT(*) AS Count >> > FROM ( >> > SELECT date >> > , CAST(ABS(RANDOM()) / (9.223E18 / 7) AS INTEGER) >AS >> >Randomiser >> > FROM CPUUsage >> > ORDER BY date >> > ) >> > WHERE Randomiser <> 7 >> > GROUP BY Randomiser >> > ORDER BY Randomiser >> > >> > >> >Then I get something like: >> > "0" "155806" >> > "1" "156270" >> > "2" "156473" >> > "3" "155748" >> > "4" "155828" >> > "5" "156196" >> > "6" "156733" >> > >-- >Cecil Westerhof >_______________________________________________ >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
