How not to let random be calculated again and again and

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

How not to let random be calculated again and again and

Cecil Westerhof-5
I have a query that I use to randomly select a set of records, but an older
one should have a higher change and a never used record is selected before
a used record. For this I use a query that looks a bit like this:
    SELECT "Last Used"
    ,      Randomiser
    ,      Randomiser
    ,      Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
    FROM (
        SELECT   *
        ,        abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
        FROM     foo
    )
    ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
    LIMIT    5

But Randomiser is calculated every-time it is used. For example this just
gave:
    "2017-10-20"    "1"    "1"    "2458046.5"
    "2017-10-18"    "0"    "3"    "2458047.5"
    "2017-10-19"    "5"    "5"    "2458047.5"
    "2017-10-17"    "2"    "5"    "2458048.5"
    "2017-10-20"    "3"    "1"    "2458048.5"

Is there a way to generate Randomiser in such a way it is only calculated
once pro record?

--
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: How not to let random be calculated again and again and

Keith Medcalf

The easiest way is likely to make the query so that it cannot be flattened by adding an ORDER BY (that does not reference the column containing the non-deterministic function by name -- ie, use only OUTPUT column position indicators (ordinals), not names or aliases).  This will require the query planner to use a co-routine for the inner table so that only the values will get passed up to the outer query.  

Depending on the version of SQLite you are using, which you did not mention.

SELECT ....
  FROM (  SELECT ...
            FROM ...
        ORDER BY 1)
ORDER BY ...
LIMIT ...
;

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Cecil Westerhof
>Sent: Monday, 6 November, 2017 01:16
>To: SQLite mailing list
>Subject: [sqlite] How not to let random be calculated again and again
>and
>
>I have a query that I use to randomly select a set of records, but an
>older
>one should have a higher change and a never used record is selected
>before
>a used record. For this I use a query that looks a bit like this:
>    SELECT "Last Used"
>    ,      Randomiser
>    ,      Randomiser
>    ,      Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
>    FROM (
>        SELECT   *
>        ,        abs(random()) / CAST(1.4E18 AS INTEGER) AS
>Randomiser
>        FROM     foo
>    )
>    ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
>    LIMIT    5
>
>But Randomiser is calculated every-time it is used. For example this
>just
>gave:
>    "2017-10-20"    "1"    "1"    "2458046.5"
>    "2017-10-18"    "0"    "3"    "2458047.5"
>    "2017-10-19"    "5"    "5"    "2458047.5"
>    "2017-10-17"    "2"    "5"    "2458048.5"
>    "2017-10-20"    "3"    "1"    "2458048.5"
>
>Is there a way to generate Randomiser in such a way it is only
>calculated
>once pro record?
>
>--
>Cecil Westerhof
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
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: How not to let random be calculated again and again and

Cecil Westerhof-5
2017-11-06 10:39 GMT+01:00 Keith Medcalf <[hidden email]>:

>
> The easiest way is likely to make the query so that it cannot be flattened
> by adding an ORDER BY (that does not reference the column containing the
> non-deterministic function by name -- ie, use only OUTPUT column position
> indicators (ordinals), not names or aliases).  This will require the query
> planner to use a co-routine for the inner table so that only the values
> will get passed up to the outer query.
>
> Depending on the version of SQLite you are using, which you did not
> mention.
>
> SELECT ....
>   FROM (  SELECT ...
>             FROM ...
>         ORDER BY 1)
> ORDER BY ...
> LIMIT ...
> ;
>

​That works likes a charm. I now use:
    SELECT "Last Used"
    ,      Randomiser
    ,      Randomiser
    ,      Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
    FROM (
        SELECT   *
        ,        abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
                 FROM     teaInStock
        ORDER BY 1
    )
    ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
    LIMIT    5

And this gives for example:


​"2017-10-17"    "2"    "2"    "2458045.5"
"2017-10-20"    "0"    "0"    "2458046.5"
"2017-10-19"    "3"    "3"    "2458048.5"
"2017-10-18"    "6"    "6"    "2458050.5"
"2017-10-19"    "5"    "5"    "2458050.5"​

So problem solved. The double order is inefficient, but is only used during
debugging, which now works. :-D

Thanks.


By the I am testing it in “DB Browser for SQLite” which uses 3.15.2.

I am not sure in which programming language the real version will be
implemented, but it will probably use a version near that one. For example
my Java uses 3.20.0.



> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Cecil Westerhof
> >Sent: Monday, 6 November, 2017 01:16
> >To: SQLite mailing list
> >Subject: [sqlite] How not to let random be calculated again and again
> >and
> >
> >I have a query that I use to randomly select a set of records, but an
> >older
> >one should have a higher change and a never used record is selected
> >before
> >a used record. For this I use a query that looks a bit like this:
> >    SELECT "Last Used"
> >    ,      Randomiser
> >    ,      Randomiser
> >    ,      Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
> >    FROM (
> >        SELECT   *
> >        ,        abs(random()) / CAST(1.4E18 AS INTEGER) AS
> >Randomiser
> >        FROM     foo
> >    )
> >    ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
> >    LIMIT    5
> >
> >But Randomiser is calculated every-time it is used. For example this
> >just
> >gave:
> >    "2017-10-20"    "1"    "1"    "2458046.5"
> >    "2017-10-18"    "0"    "3"    "2458047.5"
> >    "2017-10-19"    "5"    "5"    "2458047.5"
> >    "2017-10-17"    "2"    "5"    "2458048.5"
> >    "2017-10-20"    "3"    "1"    "2458048.5"
> >
> >Is there a way to generate Randomiser in such a way it is only
> >calculated
> >once pro record?
>

--
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: How not to let random be calculated again and again and

Cecil Westerhof-5
2017-11-06 11:11 GMT+01:00 Cecil Westerhof <[hidden email]>:

> 2017-11-06 10:39 GMT+01:00 Keith Medcalf <[hidden email]>:
>
>>
>> The easiest way is likely to make the query so that it cannot be
>> flattened by adding an ORDER BY (that does not reference the column
>> containing the non-deterministic function by name -- ie, use only OUTPUT
>> column position indicators (ordinals), not names or aliases).  This will
>> require the query planner to use a co-routine for the inner table so that
>> only the values will get passed up to the outer query.
>>
>
​It looks like that is not necessary.​

​I played again a little with it. It could be optimised a bit by sorting on
the date, so it will be almost sorted correctly in the inner sort. I should
just use 3 then.

But I just tried what happens if I order by name and that seems to work OK
also.

I have now:
SELECT *
,      Randomiser
,      Randomiser
FROM (
    SELECT   *
    ,        abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
    FROM     teaInStock
    ORDER BY "Last Used"
)
ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
LIMIT    5

And this gives:
"Goudsbloem"        "2017-10-22"    "3"     "2"    "2"    "2"
"Groene Sencha"     "2017-10-29"    "B6"    "0"    "0"    "0"
"Lemon"             "2017-10-24"    "B2"    "6"    "6"    "6"
"Darjeeling"        "2017-10-30"    "5"     "0"    "0"    "0"
"Ginger Lemon Chai" "2017-10-30"    "D4"    "1"    "1"    "1"


​So you can use the name. Something I prefer vastly above positional.



> Depending on the version of SQLite you are using, which you did not
>> mention.
>>
>> SELECT ....
>>   FROM (  SELECT ...
>>             FROM ...
>>         ORDER BY 1)
>> ORDER BY ...
>> LIMIT ...
>> ;
>>
>
> ​That works likes a charm. I now use:
>     SELECT "Last Used"
>     ,      Randomiser
>     ,      Randomiser
>     ,      Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
>     FROM (
>         SELECT   *
>         ,        abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
>                  FROM     teaInStock
>         ORDER BY 1
>     )
>     ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
>     LIMIT    5
>
> And this gives for example:
> ​
>
> ​"2017-10-17"    "2"    "2"    "2458045.5"
> "2017-10-20"    "0"    "0"    "2458046.5"
> "2017-10-19"    "3"    "3"    "2458048.5"
> "2017-10-18"    "6"    "6"    "2458050.5"
> "2017-10-19"    "5"    "5"    "2458050.5"​
>
> So problem solved. The double order is inefficient, but is only used
> during debugging, which now works. :-D
>
> Thanks.
>
>
> By the I am testing it in “DB Browser for SQLite” which uses 3.15.2.
>
> I am not sure in which programming language the real version will be
> implemented, but it will probably use a version near that one. For example
> my Java uses 3.20.0.
>

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