Subscribe to this thread
Home - General / All posts - Does Future have a Random Number function?
artlembo


2,818 post(s)
#09-Sep-17 00:07

I have been searching, but have not seen a function for generating a random number. does one exist?

tjhb

7,364 post(s)
#09-Sep-17 00:16

Nope, not yet. So a tiny .NET script function is best bet.

I haven't done it though. We need to be very careful about maintaining entropy when calling from multiple threads. Sequential calls from different threads can otherwise return the same "random" value.

Dimitri

4,107 post(s)
online
#09-Sep-17 07:29

Given that "random" numbers are pseudo-random, you could do a quick hack using UuidMakeNew() to generate a UUID, cast it as a string, grab a group of however many characters you want from that string, and then convert from hex to a decimal number. Depending on what you need the pseudo-random number for, that could be OK.

Dimitri

4,107 post(s)
online
#10-Sep-17 13:11

and then convert from hex to a decimal number.

Tim is right... a small script is the way to go. When out of curiosity I tried to do this entirely in SQL I could not find an SQL function to parse a hexadecimal string into a number, and CAST won't do it, not as a straight hex string nor as a string started with 0x. So, if you must use a script function to cast a hex string into a number, may as well use a script function that generates a random number.

adamw

7,102 post(s)
#10-Sep-17 10:41

Out of curiosity, what's the scenario?

artlembo


2,818 post(s)
#10-Sep-17 13:27

There are lots of scenarios, but in this scenario it is random sampling:

1. Take a large data set

2. Add a new column called random

3. Populate the field with random numbers

4. Sort by the random field

5. Grab the first x records in the table and you have essentially selected X random records

I do this a lot in 8.

Also, lots of statistical inferences call for random values to be generated and sometimes I use those.

tjhb

7,364 post(s)
#11-Sep-17 02:33

Art,

In that sort of case, I thought Dimitri's suggestion sounded ideal, so I tested

SELECT

SPLIT (

    COLLECT [mfd_id][Name]

    ORDER BY UuidMakeNew() ASC

    FETCH 10 -- number of samples

    )

FROM [mfd_meta]

;

Unfortunately though, if I keep repeating this query, then I tend very strongly to get the same sequence of mfd_id values in the table for successive runs. Same for different numbers of samples (FETCH N).

For example, using FETCH 1000 (effectively all records) on a new project, I consistently get sequence 2, 3, 4, 1. Same in a second and a third new project.

I wonder if this has something to do with how underlying Microsoft code creates UUID values. Quasi-guaranteed to be unique, but not unordered, it seems.

artlembo


2,818 post(s)
#11-Sep-17 03:38

I suppose I could use Postgres or SQLite to return random values using the EXECUTE statement. But, the logical thing would be to add the function to put the database on par with other in this particular area.

Also, referring to another post, a ProjectPoint would be very useful for a number of scenarios. I have offered a few suggestions for things that should be included to match what other databases already have.

adamw

7,102 post(s)
#11-Sep-17 08:26

This is a big part of why we don't have random number generator functions available officially yet in queries.

The UuidMakeNew() takes no parameters and so the query engine detects that it does not depend on the record values and so the query engine basically ignores it in ORDER BY similarly to how it would ignore ORDER BY Sin(Pi/2), for example. (The exact mechanics are such that the ignoring comes slightly later than if the ORDER BY wasn't there at all, but the effect is the same in that there are no multiple calls to UuidMakeNew() and no reshuffling of records.)

There are, of course, ways to let the query engine do what is desired here, and we have plans to implement it, this is on the wishlist.

adamw

7,102 post(s)
#11-Sep-17 08:40

Thanks.

I asked to make sure whatever we do with random numbers covers the scenario you are after adequately. What you described is pretty simple, I see no issues.

Here is how you can do random numbers now.

A C# script called 'Script':

//C#

 

class Script

{

 

public static int Rnd(int seed)

{

  return new System.Random(seed).Next();

}

 

public static double RndDouble(int seed)

{

  return new System.Random(seed).NextDouble();

}

 

static Manifold.Context Manifold;

static void Main()

{

  // just some test code, may be omitted

  Manifold.Application.Log(Rnd(2).ToString());

  Manifold.Application.OpenLog();

}

 

}

Example query:

--SQL9

FUNCTION Rnd(v INT32INT32 AS SCRIPT [Script] ENTRY 'Script.Rnd';

FUNCTION RndDouble(v INT32FLOAT64 AS SCRIPT [Script] ENTRY 'Script.RndDouble';

SELECT mfd_id, Rnd(mfd_id), RndDouble(mfd_id) FROM mfd_meta;

The functions take a seed value to let the query engine know that result values vary per record and make the results repeatable (same seed values are going to produce same resulting values). You don't have to use seed values in the functions.

The script functions are not very economical (normally, you would create a single random number generator and use it to generate all values), but this is not important until tens of millions of calls.

If you want non-repeatable results, use something like this:

// C#

 

class Script

{

 

public static int Rnd(int seed)

{

  return new System.Random(seed ^ (int)System.DateTime.Now.Ticks).Next();

}

 

public static double RndDouble(int seed)

{

  return new System.Random(seed ^ (int)System.DateTime.Now.Ticks).NextDouble();

}

 

static Manifold.Context Manifold;

static void Main()

{

}

 

}

Hope this helps.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.