Why not use MOD (%) as in ABS(RANDOM() % 6)
--- 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: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
