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