Need two ORDER two times when using RANDOM

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Need two ORDER two times when using RANDOM

Cecil Westerhof-5
I have the following query:
SELECT *
,      randomiser
,      randomiser
FROM   (
    SELECT *
    ,      ABS(RANDOM()) / 5E17 AS randomiser
    FROM   proverbs
    WHERE  used <> 'notUsed'
    ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
)
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC

And this gives for example:
"Voor niets gaat de zon op."    "2017-01-12"    "0.337325790117148"
"0.337325790117148"    "0.337325790117148"
"Met de wolven in het bos meehuilen."    "2017-01-11"
"2.59601454335206"    "2.59601454335206"    "2.59601454335206"
"Als katten muizen,
mauwen ze niet."    "2017-01-11"    "2.7932230420896"
"2.7932230420896"    "2.7932230420896"
"Uitstel is afstel."    "2017-01-12"    "1.88933779146209"
"1.88933779146209"    "1.88933779146209"
"Het is rozengeur en maneschijn."    "2017-01-13"    "1.16363975452034"
"1.16363975452034"    "1.16363975452034"

But when I remove the last ORDER (which seems redundant) I get:
"Het is rozengeur en maneschijn."    "2017-01-13"    "3.0795495790489"
"2.7886449148631"    "6.78717082169993"
"Met de kippen op stok."    "2017-01-16"    "6.56483737827297"
"1.32007069440753"    "5.18920985400017"
"Schoenmaker,
blijf bij je leest."    "2017-01-12"    "8.17180081902947"
"5.91518750003302"    "4.78956808218011"
"Een goed begin is het halve werk."    "2017-01-17"
"7.70627730482033"    "15.580638189131"    "2.0146022387495"
"Zoals het klokje thuis tikt,
tikt het nergens."    "2017-01-18"    "8.53059705262686"
"15.7307229942"    "1.1516802288132"

So randomiser is not stable any-more.


For the moment I work with:
SELECT   *
,        randomiser
,        randomiser
FROM   (
    SELECT   *
    ,        ABS(RANDOM()) / 5E17 AS randomiser
    FROM     proverbs
    WHERE    used <> 'notUsed'
    ORDER BY used ASC
)
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC

​Then randomiser is stable and the time needed is only slightly more as
time for the second query. (It is about 43, 53 and 46 ms.)​

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need two ORDER two times when using RANDOM

R Smith-2
There is nothing "stable" about the randomizer (at least not if it does
its work well).

In your queries you are simply seeing 2 different orderings with the
values accompanying the "randomized" order being linked to two different
random sets. To put it another way, the "last" order-by is not so much
redundant as it is the ONLY one that counts and the first order-by
(inside the sub-1uery) causes the sub-query to be "made stable" while it
can still have rows where the random value and the random order are
calculating to two different values so the order doesn't seem to hold to
the value (as it shouldn't). Once this sub-query has been "remembered"
though, any next order-by will do the job just fine because now values
do not get re-calculated anymore, they just get recalled from flat memory.

The only reason your two "randomiser" references in the outer query
shows the same value is that they are using the same alias in a single
row, which provides the Query Planner an easy reference to just
duplicate while inside the same single row in the cursor, but the
calculation for the ORDER BY clause happens at another time and place,
and so there is no re-referencing of the value that used to be referred
to by "randomiser" way back when. Alias != Variable.


Hope that is semi-clear...
Ryan

On 2018/03/11 9:52 AM, Cecil Westerhof wrote:

> I have the following query:
> SELECT *
> ,      randomiser
> ,      randomiser
> FROM   (
>      SELECT *
>      ,      ABS(RANDOM()) / 5E17 AS randomiser
>      FROM   proverbs
>      WHERE  used <> 'notUsed'
>      ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
> )
> ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
>
> And this gives for example:
> "Voor niets gaat de zon op."    "2017-01-12"    "0.337325790117148"
> "0.337325790117148"    "0.337325790117148"
> "Met de wolven in het bos meehuilen."    "2017-01-11"
> "2.59601454335206"    "2.59601454335206"    "2.59601454335206"
> "Als katten muizen,
> mauwen ze niet."    "2017-01-11"    "2.7932230420896"
> "2.7932230420896"    "2.7932230420896"
> "Uitstel is afstel."    "2017-01-12"    "1.88933779146209"
> "1.88933779146209"    "1.88933779146209"
> "Het is rozengeur en maneschijn."    "2017-01-13"    "1.16363975452034"
> "1.16363975452034"    "1.16363975452034"
>
> But when I remove the last ORDER (which seems redundant) I get:
> "Het is rozengeur en maneschijn."    "2017-01-13"    "3.0795495790489"
> "2.7886449148631"    "6.78717082169993"
> "Met de kippen op stok."    "2017-01-16"    "6.56483737827297"
> "1.32007069440753"    "5.18920985400017"
> "Schoenmaker,
> blijf bij je leest."    "2017-01-12"    "8.17180081902947"
> "5.91518750003302"    "4.78956808218011"
> "Een goed begin is het halve werk."    "2017-01-17"
> "7.70627730482033"    "15.580638189131"    "2.0146022387495"
> "Zoals het klokje thuis tikt,
> tikt het nergens."    "2017-01-18"    "8.53059705262686"
> "15.7307229942"    "1.1516802288132"
>
> So randomiser is not stable any-more.
>
>
> For the moment I work with:
> SELECT   *
> ,        randomiser
> ,        randomiser
> FROM   (
>      SELECT   *
>      ,        ABS(RANDOM()) / 5E17 AS randomiser
>      FROM     proverbs
>      WHERE    used <> 'notUsed'
>      ORDER BY used ASC
> )
> ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
>
> ​Then randomiser is stable and the time needed is only slightly more as
> time for the second query. (It is about 43, 53 and 46 ms.)​
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users