2018-07-08 11:00 GMT+02:00 Cecil Westerhof <[email protected]>:
> 2018-07-08 8:19 GMT+02:00 Cecil Westerhof <[email protected]>:
>
>> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
