Skip to Content
author's profile photo Former Member
Former Member

Do you have any idea how to create and use RAND() function in HANA?

Hi,

I'm new in SAP HANA and I try to select random rows from result set, in a way similar to:

SELECT * FROM table WHERE RAND() > 0.5;

Could someone tell mi how can I create RAND() function (eg. how can I return calculation result, not an algorithm) from custom procedure

and then how can I use it in query.

Thank you in advance,

Marcin

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Posted on Sep 20, 2012 at 05:20 PM

    Hi Marcin,

    I don't think there is a RAND() function defined in HANA yet, but something similar to what I had implemented in the past, the random number can be generated from the microsecond value of the time function.

    You can try using substring(now(), 21,3) which give the 3 digit (or even last or first 2 digits) microsecond value of the time, which can be used to generate the random number.

    Please check if this can be used in your requirement.

    Regards,

    Ravi

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Ravindra Channe

      Hi Ravi,

      Yes, I have thought about combining microseconds with SIN() numbers (after reading your post), but I already have written my RAND() procedure. If you are interested, here is the source code (maybe it helps someone). It is based on George Marsaglia's algorithm (http://www.codeproject.com/Articles/25172/Simple-Random-Number-Generation) and requires a table (named RAND_PARAMS) to store generator values:

      -- prerequisites:

      CREATE TABLE RAND_PARAMS (M_Z BIGINT, M_W BIGINT);

      INSERT INTO RAND_PARAMS VALUES(362436069,521288629); -- generator seeds

      CREATE PROCEDURE RAND(OUT o DOUBLE) LANGUAGE SQLSCRIPT AS

      m_z BIGINT;

      m_w BIGINT;

      BEGIN

      SELECT M_Z, M_W INTO m_z, m_w FROM RAND_PARAMS;

      m_z := 36969 * BITAND(:m_z,65535) + BITAND(:m_z,4294901760)/65536; -- m_z = 36969 * (m_z & 65535) + (m_z >> 16);

      m_w := 18000 * BITAND(:m_w,65535) + BITAND(:m_w,4294901760)/65536; -- m_w = 18000 * (m_w & 65535) + (m_w >> 16);

      o := :m_z *65536 + :m_w; -- return (m_z << 16) + m_w;

      o := BITAND(:o, 4294967295)/4294967296.0; -- TO_INT(o)/MAX_RAND -> let's scale to <0,1) range

      UPDATE RAND_PARAMS SET M_Z = BITAND(:m_z, 4294967295), M_W = BITAND(:m_w, 4294967295); -- let's preserve 32 bits only

      END;

      thanks for your interest; best regards

      Marcin

  • author's profile photo Former Member
    Former Member
    Posted on Sep 25, 2012 at 10:09 PM

    Hi Marcin,

    You can perhaps try to create a simple produre that returns the value with SELECT query using current system timestamp on miliseconds fraction.

    For example:

    3-digits number:

    SELECT TO_NUMBER(TO_CHAR(NOW(),'FF3')) FROM DUMMY

    Regards,

    Ferry

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 26, 2012 at 05:48 AM

    Hi everyone,

    I've been told there is undocumented RAND() function (from rev. 32). I've tried it and it really works:

    select RAND() from dummy;

    Thanks everyone for help,

    regards

    Marcin

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 08, 2012 at 03:20 AM

    This is from a while ago, but just to report that (at least in rev36+), I have RAND() as a valid function.

    I didn't find it in the documentation, but testing a little bit, it apparently returns a (float?) number from 0 to 1.

    Using simple multipliers & sums, it's possible to achieve a regular random function.

    For example, a function that always returns an integer between A and B:

    SELECT TO_INT('A' + ('B'-'A')*RAND()) FROM DUMMY;

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 21, 2014 at 10:04 AM

    Hi,

    in SP7 RAND() function included into official documentation:

    Description

    Returns a pseudo-random value in the range of [0, 1.0). Its return value type is DOUBLE.

    Example

    SELECT RAND() FROM DUMMY;

    RAND

    3.907985046680551e-14

    Denis.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.