2018-07-08 11:00 GMT+02:00 Cecil Westerhof <cldwester...@gmail.com>: > 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? >> > > Solved it. Do not need a table any-more and generate Randomiser in the > correct way: > > SELECT Randomiser > , COUNT(*) AS Count > FROM ( > WITH RECURSIVE > cnt(x) AS ( > SELECT 1 > UNION ALL > SELECT x + 1 FROM cnt > LIMIT 1.1E6 > ) > SELECT x > , ABS(RANDOM()) % 7 AS Randomiser > FROM cnt > ORDER BY x > ) > GROUP BY Randomiser > ORDER BY Randomiser > > And this gives: > "0" "157139" > "1" "157865" > "2" "156849" > "3" "157226" > "4" "156916" > "5" "157230" > "6" "156775" > > By the way: it is only slightly faster as the version where I used the > table. >
I can shave off about 30% by rewriting it to: WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt LIMIT 1.1E6 ) SELECT ABS(RANDOM()) % 7 AS Randomiser , COUNT(*) FROM cnt GROUP BY Randomiser ORDER BY Randomiser -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users