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