SQL RAND() Function

The SQL RAND() function is used to generate a floating-point number or whole integer number by randomly.

The SQL RAND() function will generate unique(maximum time) number or duplicate(some time) number at every execution or function calling.

The SQL RAND() function will generate number between 0.0(inclusive) to 1.0(exclusive). The generated number will be in floating-point numbers and by default it will not generate whole integer number. We can generate whole interger number (from any number or within range between 2 numbers) using some formula's with rand() function.

It can be used in any valid SQL SELECT statement as well in SQL where clause.

SELECT RAND(seed_number)

SELECT RND(seed_number)

SELECT FLOOR((RAND() * (X - Y)) + Y)

SELECT INT((RND() * (X - Y) + 1) + Y)

SELECT RAND()

SELECT * FROM Author
WHERE AuthorID = FLOOR((RAND() * (6 - 1)) + 1)

To provide randomized datetime for a specified range of dates. You might be familiar with SQL's Rand() function, which generates random integers on the fly. The same rand() algorithm was applied with additional logic and date manipulation techniques.

Example: The user-defined function's script, which generates random datetime between a range of dates, is provided below. The startdate and enddate inputs of this function are both of the datetime type, and it returns data of the same kind.

CREATE FUNCTION [GenerateRandomDateTime]
    (
      @StartDate DateTime,
      @EndDate DATETIME 
    )
RETURNS DATETIME
AS BEGIN
  
--DECLARE VARIABLES
    DECLARE @RandomDateTime DATETIME
    DECLARE @RandomNumber FLOAT
    DECLARE @IntervalInDays INT ;
    
    SET @IntervalInDays = CONVERT(INT, @EndDate - @StartDate)
    SET @RandomNumber = ( SELECT    [RandNumber]
                          FROM      [RandNumberView]
                        )
SELECT  @RandomDateTime = DATEADD(ss, @RandomNumber * 86400, 
DATEADD(dd, @RandomNumber * @IntervalInDays, @StartDate))
RETURN @RandomDateTime
END
GO

CREATE VIEW [RandNumberView]
AS  SELECT  RAND() AS [RandNumber]
GO

You must have noticed that I utilized the [RandNumberView] view in the example above. Because time dependent operators are prohibited in functions, if I had just used rand() in the function, I would have gotten the following error message: "Invalid use of side-effecting or time-dependent operator in 'rand' within a function." To generate a random datetime, I utilised the "Rand" logic in the aforementioned code on both the day and the time.

Example 2: Let's test by creating a sample table and fill it with random data by using this function.

GO
CREATE TABLE [DateTable] 
    (
      [ID] INT IDENTITY,
      [Date] DATETIME
    )

--Let's add some data
GO
INSERT  INTO [DateTable]
SELECT DBO.[GenerateRandomDateTime]
('2010-10-28 11:21:13.937','2012-10-30 11:23:13.937')
GO 50

SELECT * FROM [DateTable]
GO

In order to insert 50 random datetimes into the table, the above "GO 50" command will execute the insert script batch 50 times. The output I got after running the aforementioned script is seen in the screenshot down below.

With the help of two functions, random() and now(), we can construct random dates in Postgresql between two dates.

• random(): This method returns a value between 0 (inclusive) and 1 (exclusive), therefore the value must be more than or equal to 0 and less than 1.
• now(): The current date, time, and time zone are returned by the PostgreSQL now() function.

Example : Let’s understand with an example.

SELECT NOW() + (random() * (NOW()+'100 days' - NOW())) + '20 days';

When we run the aforementioned query, a random date is produced by randomly multiplying the current date by the following formula: current date + 100 days minus current date plus 20 days plus current date, where 100 days is the desired time window and 20 days indicates how far out to move the time window.

This SQL Server function is used to return a randomly generated decimal value that is between more than and equal to zero (>=0) and less than one.

If we want to obtain a random integer R in the range i <= R < j, we have to use the expression “FLOOR(i + RAND() * (j - i))”.

Features :

• To generate a random decimal value, use this function.
• The returned value ranges from 0 to 1 inclusive (exclusive).
• This function will produce a completely random integer if it is called without any parameters.
• This function will provide a repeated series of random integers if an argument is provided.
• Optional parameters are accepted by this function.

This function uses a formula

“FLOOR(i + RAND() * (j - i))”

To get a random integer R, where R lies in the range of “i <= R < j”.

Syntax :

RAND(N)

Parameter:

N : It returns a repeated series of random numbers if N is given. It returns an entirely random number if N is not supplied. This value serves as a seed and is optional.

Returns : It returns a random number between 0 (inclusive) and 1 (exclusive).

Note:

• Value > 0 and Value < 1 indicate that the RAND function will return a value between 0 and 1 (exclusive).
• In the absence of a seed, the RAND function will produce a totally random number.

Example 1: You can use the following formula to generate a random decimal number (range) between two values:

SELECT RAND()*(b-a)+a;

You can generate a random number for any number between a and b, where an is the smallest and b is the greatest.

SELECT RAND()*(25-10)+10;

A random decimal number between 10 and 25 would be produced by the formula provided.

TIP: This method would never produce a decimal number that was exactly >10 or <25, but one that was between 10 and 25.

Example 2: Let's examine how to create a random decimal number between two values in SQL Server (Transact-SQL) using the RAND function (ie: range).

The following, for instance, would produce a random decimal value between 1 and 10, inclusive (random number would be larger than 1 and less than 10), as follows:

SELECT RAND()*(10-1)+1;

Result:

SELECT RAND(9)*(10-1)+1;

Result:

SELECT RAND(-5)*(10-1)+1;

Result:

Example 3: Getting a random value between 0 and 1.

SELECT RAND();

Result:

Example 4: Getting a random decimal number with seed value of 5.

SELECT RAND(5);

Result:

Example 1: In a sqlite table with 15000 rows, we wanted to know how to randomly remove 20% of the rows. We discovered that Stack Overflow users selected n random entries from a SQL Server table to answer this query.

Nevertheless, we can calculate a point within this space as (263) * 0.6 as the random() function in sqlite produces a signed 64-bit integer. The set of positive signed 64-bit integers will consist of 40 percent of signed integers greater than this, or 20% of the entire set.

Truncate to the integer below, this is 5534023222112865484 .

Therefore you should be able to get 20% of your rows with a simple:

SELECT * FROM table WHERE random() > 5534023222112865485

Or in your case, since you want to delete that many:

DELETE FROM table WHERE random() > 5534023222112865485

I hope you like this strategy. It might be appropriate if you need great performance from such an operation, but because it might be hardware- or version-dependent, it probably isn't worth the risk.

Example 2: Without random

Let's imagine you don't care about the particular entries you delete; all that matters is that you need to get rid of a given amount of table items.

DELETE TOP 2 FROM Cute_Doggos

You're not far off in your assumption that this query will eliminate the top two records in your table. The only issue is that this approach will remove 2 RANDOM records from the table because SQL saves records in a random sequence.

Additionally, SQL has the ability to remove a subset of the records:

DELETE TOP 25 PERCENT FROM Cute_Doggos

Again, this query will delete RANDOM records. In our example, since we have 4 records, this query would delete 1 random record (4 * 0.25 = 1).

What happens when we wish to create a random number for each row of a query is a major complaint with RAND().

using the RAND() math function to produce random integers in SQL Server. RAND is a simple tool for creating random numbers. However, if you attempt to utilise RAND() in a select statement, the random number will repeat in every row that the select query returns.

Having the same random number appear in every row is probably not what you were expecting. How can this issue be solved in order to provide distinct random numbers for each row in the result? Using NEWID, it is possible to obtain unique random numbers for every row. NEWID() is a 16-byte hexadecimal unique identifier.

Example 1: Let's try assigning a random number to each individual in the person database using AdventureWorks, our tried-and-true workhorse, and see what occurs.

SELECT FirstName, 
MiddleName,LastName, RAND() AS RandomNumber
FROM Person.Person

Hmmmmm…. We didn't want to have the same number for everyone, but it appears that is what we have. And that's the problem I have with RAND(): if you run it independently of a query, it returns a different random number each time, but if you run it as part of a query, it consistently returns the same number for each row returned.

So what else can we do? We’ll there is something that gives us a ‘random’ value for every row in the query and that’s our good old friend NEWID().

Let’s try the same query but this time we’ll swap RAND() with NEWID().

SELECT FirstName,MiddleName,LastName, NEWID() AS RandomNumber
FROM Person.Person

That's looking better now that we have individually random values for each of the individuals. However, I still don't like that; what am I expected to do with such a terrible GUID? Wouldn't it be good if we could get a number out that looked normal? CAST or CONVERT it to an INT, perhaps? That won't work, I'll say that right now. However, there is something we can do to improve the situation, CHECKSUM (). The function CHECKSUM(), which is not well known, is intended to return a checksum or hash result for an expression. The fact that the checksum was returned as a numerical value was advantageous for us.

Let’s have a look and see what that’s looking like now….

SELECT FirstName,MiddleName,LastName, CHECKSUM(NEWID()) AS RandomNumber
FROM Person.Person

That’s looking better, now we’ve got a number! You know what?! I’m still not happy (I know I’m a difficult guy to please), I don’t want to be seeing those nasty negative numbers.

Example 2: For each row in the table, we frequently need to produce an ID that is both distinct and random. The SQL Server NEWID() function can be used to produce a random number. A 32 bit Hexadecimal number, specific to your entire system, will be produced randomly via the NEWID() method.

Let's take an example.

SELECT* NEWID as RAND_Id FROM Employee;

Example 3: The 16 bit unique identity in NEWID() might not be helpful in your case. Integer-formatted random numbers can be required. Between two integers, you might even require random numbers. Then, using a formula and CAST the NEWID(), you may obtain a random integer. Here is the syntax and an example of utilizing NEWID() to produce random numbers between two integers.

Syntax:

ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % (@B - @A + 1) + @A

Where:

• A = The smallest number of the range.
• B = The largest number of the range.

Example 1: For each row in the select statement output, this script will produce a distinct random number between two integer values (i.e., between 70 and 100).

Use WideWorldImporters;
DECLARE @A INT;
DECLARE @B INT;
 
SET @A = 70
SET @B = 100
 
SELECT Top 15 
    ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) 
        % (@B - @A + 1) + @A AS Random_Number, 
    * from Sales.Orders;
GO

Example 1: The next example will demonstrate how to make a table with 1000 rows and random values between 1 and 100. For generating random values and distinct values, respectively, we'll utilise the RAND function and CHECKSUM(NEWID()). To change the values from real to integer, we utilise the cast:

with randowvalues
 as(
    select 1 id, CAST(RAND(CHECKSUM(NEWID()))*100 as int) randomnumber
	--select 1 id, RAND(CHECKSUM(NEWID()))*100 randomnumber
    union  all
    select id + 1, CAST(RAND(CHECKSUM(NEWID()))*100 as int)  randomnumber
	--select id + 1, RAND(CHECKSUM(NEWID()))*100  randomnumber
    from randowvalues
    where 
    id < 1000
    )
 
select *
from randowvalues
OPTION(MAXRECURSION 0)

The code will show 100 values between 1 to 100.

Example 2: Integer random values generated in SQL Server

If you want to generate 10000 values, change this line:

id < 1000

With this one:

id < 10000

If you want to generate values from 1 to 10000 change these lines:

select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumber
union  all
select id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int)  randomnumber
from randowvalues

Example 3: If you want to generate real values instead of integer values use these lines replace these lines of the code displayed before:

select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumber
union  all
select id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int)  randomnumber
from randowvalues

And use these ones:

select 1 id, RAND(CHECKSUM(NEWID()))*10000 randomnumber
union  all
select id + 1, RAND(CHECKSUM(NEWID()))*10000  randomnumber
from randowvalues

The query will show real numbers from 0 to 100.

The SQL Server RAND function is the initial method for producing a random number. A float value will be returned.

Example 1: A random number between 0 and 1 is produced by the random() function. The sentence that follows generates a random integer between 0 and 1.

SELECT random();

Output:

Example 2: To generate a random number between 1 and 11, you use the following statement:

SELECT random() * 10 + 1 AS RAND_1_11;

Output:

Example 3: If you want to generate the random number as an integer, you apply the floor() function to the expression as follows:

SELECT floor(random() * 10 + 1)::int;

Output:

Example 4: Generally, to generate a random number between two integers l and h, you use the following statement:

SELECT floor(random() * (h-l+1) + l)::int;

You can develop a user-defined function that returns a random number between two numbers l and h:

CREATE OR REPLACE FUNCTION random_between(low INT ,high INT) 
   RETURNS INT AS
$$
BEGIN
   RETURN floor(random()* (high-low + 1) + low);
END;
$$ language 'plpgsql' STRICT;

Example 5: The following statement calls the random_between() function and returns a random number between 1 and 100:

SELECT random_between(1,100);

Output:

Example 6: If you want to get multiple random numbers between two integers, you use the following statement:

SELECT random_between(1,100)
FROM generate_series(1,5);

Output:

From above example generate a random number between a range of two numbers.

Example 7: We can test to see how evenly distributed the numbers will be. Let’s try running this and see how random our numbers are:

CREATE TABLE #RandomCheck (RandNum SMALLINT);
 
DECLARE @Num AS SMALLINT;
DECLARE @TestCount AS SMALLINT = 10000;
 
WHILE @TestCount > 0
BEGIN
    SET @Num = (SELECT FLOOR(RAND() * (10)) + 1);
 
    INSERT INTO #RandomCheck (RandNum)
    VALUES (@Num);
 
    SET @TestCount -= 1;
END
 
SELECT RandNum, count(*) AS 'Count'
FROM #RandomCheck
GROUP BY RandNum
ORDER BY RandNum;

They end up being pretty evenly distributed.

Example 8: Another option I’ve seen used to get random numbers is the NEWID() function. If we still need a random number between 1 and 10:

SELECT (ABS(CHECKSUM(NEWID())) % 10) + 1;
GO

The absolute number can be obtained using ABS(). In this case, without it, our range would actually be between -8 and 10. We'll get identical outcomes if we insert this query into our prior test script:

Using this approach, our numbers were also dispersed quite equally.

I've chosen to use random numbers in this manner. RAND by itself will provide you with the same number for every row if you're trying to perform something like allocate random numbers to rows in a table. With such kinds of situations, you have to be a little more cautious.

The random number from the table is returned using the order by clause and the orderby random function. Because the random function will select the random values from the PostgreSQL table, it will not behave the same as an order by clause in PostgreSQL. Because the order by clause returns the table's random number when using the random function, it is beneficial for quickly getting data from huge tables. Utilizing the limit clause, we can also retrieve data from the table by utilising the order by random function.

Syntax:

Below is the syntax of the order by random in PostgreSQL.

Select name_of_column1, name_of_column2, name_of_column3, …., 
name_of_columnN from name_of_table ORDER BY RANDOM ();

Select name_of_column1, name_of_column2, name_of_column3, …., 
name_of_columnN from name_of_table ORDER BY RANDOM () limit number;

Select * (select all column from table) 
from name_of_table ORDER BY RANDOM () limit number;

Parameters of PostgreSQL ORDER BY Random:

Below is the parameter description syntax of the order by random in PostgreSQL.

• Select: In PostgreSQL, the order by random function is used in select operations to get data from tables. Data can be retrieved from the chosen table's single column, multiple columns, or all columns.
• Name of Column 1 through Name of Column N: This is the name of the column that was utilised to get data from a column using PostgreSQL's order by random function.
• Table name: This is the name of the table from which the data has been retrieved. In order by a random function, the table name is a crucial and helpful parameter.
• Order by Random: Using the order by clause this session, this function is used to choose a random value to get data. The order by clause, which we used in the query, will pull the random value from the designated table column.
• From: This is referred to as choosing the particular table from which we are retrieving data. The table in the session is chosen using the from keyword.
• Number: This is described as a number that is utilised with the limit clause; it will obtain the number of rows that we specified in the limit.

How Does the Random ORDER BY Function Work?

Below is the working of the order by random in PostgreSQL.

PostgreSQL's order by random function will be used if we want the table's random data.

It will return all rows from the table if we haven't utilised limitations with an order by clause. The requested number of rows from the table will be returned if we used a limit with an order by clause.

Example 1: The instance below demonstrates how all rows from the table will be returned if limits with an order by random function are not used.

select * from stud2 order by random();

select * from stud2 order by random() limit 3;

Example 2: PostgreSQL ORDER BY Random Example

The examples of the PostgreSQL order by random_test function that we have used to describe them are as follows. The random_test table's count and table layout are shown below.

\d+ random_test;

select count (*) from random_test;

select * from random_test limit 1;

Explanation:

• Since a limit clause is not utilised in the first example above with an order by random function, all entries from the PostgreSQL table will be returned.
• In the second example above, we combined an order-by-random function with a limit clause, which caused the query to return the required number of rows from the table that we had previously established.
• The random number from the table we used in the query will be returned by sorting by the random function.
• We must utilize the order by random function on the table if we need a specific number of random lists at once.
• The order by clause in PostgreSQL sorts all the data from the table, hence it is slower than other random methods because of this.
• The PostgreSQL order by random function will return a numeric number between 0 and 1, which is generated using the double-precision type.
• When getting random records from a table in PostgreSQL, the order by random clause is highly helpful and significant.

Example 1: Providing random values inside certain ranges is a common request as well. A range of temperatures in °F will be displayed in the example that follows (I really prefer the metric system, but I will do an exception this time).

The temperature of the human body can fluctuate from 95 to 105.8 °F (the normal range is 97.7-99.5 °F; higher values indicate hyperthermia and lower values indicate fever).

In this example, we will generate values between 95 to 105.8 °F:

with randowvalues
    as(
 
--10.8 is the difference between 105.8 minus 95
 
       select 1 id,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber
	    union  all
        select id + 1,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real)  +95 as randomnumber
        from randowvalues
        where 
          id < 100
      )
 
    select *
    from randowvalues
    OPTION(MAXRECURSION 0)

The result of the T-SQL statement will be values from 95 to 105.8 °F:

Example 2: If you want real numbers from 6 to 10, change these lines of code:

select 1 id,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber
union  all
select id + 1,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real)  +95 as randomnumber

Example 3: With these ones:

select 1 id,CAST(RAND(CHECKSUM(NEWID()))*4 as real) +6 as randomnumber
union  all
select id + 1,CAST(RAND(CHECKSUM(NEWID()))*4 as real)  +6 as randomnumber

Where 6 is the minimum value and 4 is the difference between 10 and 6.

One of the easiest ways to generate a random string is to use a combination of the SUBSTR (), MD5 (), and RAND () functions.

The MD5 () function is used to generate an MD5128-bit checksum representation of a string.

Use the SUBSTR () method to extract part of the MD5 string as needed.

Example 1: The example below shows how the MD5() function returns the checksum for the string garden:

SELECT MD5("garden") AS checksumResult;

Output:

The checksum result is always generated in 32 alphanumers from the arguments passed to the MD5 () function.

Example 2: To generate a random result every time the MD5 () function is called, you need to use the RAND () function to generate a random number, as shown below.

SELECT MD5(RAND()) AS checksumResult;

Output:

SELECT MD5(RAND()) AS checksumResult;

Output:

Example 3: Let's replace the RAND () function with StringGarden again so that we can see how the SUBSTR () method works.

Notice that the following example uses SUBSTR () to extract the first 10 characters from the checksum:

SELECT MD5("garden") AS checksumResult;

Output:

SELECT SUBSTR(MD5("garden"), 1, 10) AS checksumResult;

Output:

Example 4: Then replace the string garden with RAND () again. This time there are 10 random characters each time we call the function.

SELECT SUBSTR(MD5(RAND()), 1, 10) AS randomString;

Output:

Example 5: If you want only 8 characters, then change the third argument for the SUBSTR() function from 10 to 8 as shown below:

SELECT SUBSTR(MD5(RAND()), 1, 8) AS randomString;

And that’s how you generate a random string of characters using the MD5() and RAND() functions.

Example 6: The SQL Server NEWID() function makes it simple to generate a string of random numbers up to 32 characters long for test data or to simply fill a field with random text. A new GUID (globally unique identifier) is generated using the NEWID() function, and we can use that as a foundation to generate a string of random characters.

SELECT NewID() AS [NewID]

This gives you a 36 character string, but four of the characters are dashes. To remove these, we can simply run.

SELECT REPLACE(NEWID(),'-','') AS Random32

which gives us 32 random characters. You can use the left (or right) functions to grab however many characters you need...

SELECT LEFT(REPLACE(NEWID(),'-',''),10) AS Random10

Then convert it to whatever data type you are working with...

SELECT CONVERT(nvarchar(10),LEFT(REPLACE(NEWID(),'-',''),10))

and you are good to go! Each time you run the select, you will get a different 10 digit sequence numbers and letters.

Example 7:

Part 1: This solution is presented in three parts. The first part is the ability to select a random integer between two boundary values. We need to pass in our random value, since SQL does not allow non-determinant functions (e.g. RAND()) to be called from within user-defined functions. As usual, we will also pass in our lower and upper bounds.

CREATE FUNCTION [dbo].[fn_RandIntBetween]
(
  @lower  INT,
  @upper  INT,
  @rand   FLOAT
)
RETURNS INT
AS
BEGIN
  DECLARE @result INT;
  DECLARE @range = @upper - @lower + 1;
  SET @result = FLOOR(@rand * @range + @lower);
  RETURN @result;
END
GO

Part 2: The next part is to create a function to pick a random character from a given list of characters. Picking a random character will use the function we created above.

CREATE FUNCTION [dbo].[fn_PickRandomChar]
(
  @chars VARCHAR(MAX),
  @rand  FLOAT
)
RETURNS CHAR(1)
AS
BEGIN
  DECLARE @result CHAR(1) = NULL;
  DECLARE @resultIndex INT = NULL;
  IF @chars IS NULL
    SET @result = NULL;
  ELSE IF LEN(@chars) = 0
    SET @result = NULL
  ELSE
  BEGIN
    SET @resultIndex = [dbo].[fn_RandIntBetween](1, LEN(@chars), @rand);
    SET @result = SUBSTRING(@chars, @resultIndex, 1);
  END

  RETURN @result;
END

We can use this function as follows.

DECLARE @rchar CHAR(1);
SELECT [dbo].[fn_PickRandomChar]
('abcdefghijklmnopqrstuvwxyz', RAND()) as [Random Char];

Results:

Example 8: The creation of a stored process to produce a random string is the final stage.

Note: A stored procedure is required for this. We will place our action in a procedure because SQL Server does not permit RAND() in a user-defined function.

CREATE PROCEDURE [dbo].[CreateRandomString]
  @minLength INT = 1,
  @maxLength INT = 50,
  @chars VARCHAR(200) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
  @randomString VARCHAR(MAX) = NULL OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- Get the length of our string.
  DECLARE @stringLength INT = [dbo].[fn_RandIntBetween](@minLength, @maxLength, RAND());

  -- Set our random string to an empty string.
  SET @randomString = '';

  -- If our string is not yet the appropriate length, add another character to the string.
  WHILE LEN(@randomString) < @stringLength
  BEGIN
    SET @randomString = @randomString + [dbo].[fn_PickRandomChar](@chars, RAND());
  END

END

Example 9: Now we have all of the tools we need to create a random string. Let’s put it all together and see how this works.

DECLARE @minLength INT = 5;
DECLARE @maxLength INT = 20;
DECLARE @chars VARCHAR(26) = 'abcdefghijklmnopqrstuvwxyz';
DECLARE @text VARCHAR(50) = NULL;

EXEC [dbo].[CreateRandomString] @minLength = @minLength,
     @maxLength = @maxLength,
     @chars = @chars,
     @randomString = @text OUTPUT;
SELECT @randomString AS [Random String];

Result:

Example 1: How to choose a random N number of rows from a large dataset within a group was the problem. I'm looking for a random sample of 1,000 clients organised by the year they signed up.

As it turns out, you can achieve that with SQL using our good friend the analytical function, also known as the window function. More information about these helpful features may be found here and here.

Assuming I have a dataset consisting of user_id and signup_date with signup dates ranging from 2017-01-01 to 2018-07-20:

+----------------------------------+-------------+
|         user_id                  |  signup_date|
|----------------------------------|-------------|
| ed4f25103c7f6d9c41e14c047d2ff930 | 2017-02-04  |
| 7751a2c62c91383aec0e9be07457b5b0 | 2017-03-19  |
| eb63c09470b6d1564fe77132499f921e | 2017-07-11  |
| 84239f8c064ae3dfd7e61c139dbde6d5 | 2017-04-25  |
| 3a7b7ce201ebbe36d6599c0b9f859bad | 2017-04-18  |
+----------------------------------+-------------+

Random Sampling Within Groups using SQL

Here’s just a quick SQL tip I came across today while working on a sample dataset for a take-home exercise.

How to choose a random N number of rows from a large dataset within a group was the problem. I'm looking for a random sample of 1,000 clients organised by the year they signed up.

As it turns out, you can achieve that with SQL using our good friend the analytical function, also known as the window function. More information about these helpful features may be found here and here.

Assuming I have a dataset consisting of user_id and signup_date with signup dates ranging from 2017-01-01 to 2018-07-20:

+----------------------------------+-------------+
| user_id                          | signup_date |
|----------------------------------|-------------|
| ed4f25103c7f6d9c41e14c047d2ff930 | 2017-02-04  |
| 7751a2c62c91383aec0e9be07457b5b0 | 2017-03-19  |
| eb63c09470b6d1564fe77132499f921e | 2017-07-11  |
| 84239f8c064ae3dfd7e61c139dbde6d5 | 2017-04-25  |
| 3a7b7ce201ebbe36d6599c0b9f859bad | 2017-04-18  |
| abfb263db894248709e224fb9a683a61 | 2017-09-24  |
| b74f62527bce6c1bfaf93d6104506b05 | 2017-04-09  |
| 3bf1bb77cc78d921ace25cb16cc37a54 | 2017-07-27  |
| 4b72f06bbef45f45494c4214d2cebd9c | 2017-04-10  |
| 6d0c208da45034fe6a0a2a2db04f117a | 2017-08-21  |
+----------------------------------+-------------+

Example 2: Let’s say we want to pick out 5 random rows by signup year (or month, week, whatever), we will need to:

For each user id, generate a random row_number that resets for each of my periods or groups. This is accomplished by utilising the random() method to order the row number() function.

Although random() can be parameterized with a seed, it seems to me that this only yields repeatable results if your function is only run on one node given the concurrent nature of data warehouse solutions like Snowflake and Redshift (like the leader node).

Using our new random row number as a filter, choose N of those rows.

with randomly_sorted_users as (
    select
        user_id,
        signup_date,
        row_number() over(partition by date_trunc('year', signup_date)
                            order by random()) as random_sort
    from
        user_table

)
select
    user_id,
    signup_date
from
    randomly_sorted_users
where
    random_sort <= 5

This will yield 10 rows in total, 5 chosen randomly by signup year:

+----+----------------------------------+-------------+
| #  | user_id                          | signup_date |
|----|----------------------------------|-------------|
| 1  | e58975cdb6f4b052389b199f623319dd | 2017-10-15  |
| 2  | c7253ee384f5be436fef0b900c9e5125 | 2017-05-03  |
| 3  | 1400ffe327b1b15130fe85535eee58ba | 2017-03-05  |
| 4  | 1079a421f9a59e1716082424310f6dc0 | 2017-12-15  |
| 5  | f4fed873df3786d04cdef1ccce59eb36 | 2017-02-21  |
| 6  | db3a60a5d55fc8a4aa20f5d8a2f276af | 2018-01-25  |
| 7  | 9e991194118d3e4db471106358825685 | 2018-04-11  |
| 8  | 008a3b0f05c601ea39f0e893d4e11043 | 2018-02-09  |
| 9  | 7e40c4cce72ae34ca0e6e4e39efdb514 | 2018-03-04  |
| 10 | 1b9d2c64344629aebf2349d328e76aae | 2018-06-30  |
+----+----------------------------------+-------------+

Example 3: I'll demonstrate how to create random datetime for a given range of dates in this blog post. You might be familiar with SQL's Rand() function, which generates random integers on the fly. I added some more logic using date manipulation routines while still using the same rand() foundation. The user-defined function's script, which generates random datetime between a range of dates, is provided below. The startdate and enddate inputs of this function are both of the datetime type, and it returns data of the same kind.

CREATE FUNCTION [GenerateRandomDateTime]
    (
      @StartDate DateTime,
      @EndDate DATETIME 
    )
RETURNS DATETIME
AS BEGIN
  
--DECLARE VARIABLES
    DECLARE @RandomDateTime DATETIME
    DECLARE @RandomNumber FLOAT
    DECLARE @IntervalInDays INT ;
    
    SET @IntervalInDays = CONVERT(INT, @EndDate - @StartDate)
    SET @RandomNumber = ( SELECT    [RandNumber]
                          FROM      [RandNumberView]
                        )
SELECT  @RandomDateTime = DATEADD(ss, @RandomNumber * 86400,
DATEADD(dd, @RandomNumber * @IntervalInDays, @StartDate))
   RETURN @RandomDateTime
    END
GO

CREATE VIEW [RandNumberView]
AS  SELECT  RAND() AS [RandNumber]

GO

You must have noticed that I utilised the [RandNumberView] view in the example above. Because time dependent operators are prohibited in functions, if I had just used rand() in the function, I would have gotten the following error message: "Invalid use of side-effecting or time-dependent operator in 'rand' within a function."

Example 4: To generate a random datetime, I utilized the "Rand" logic in the aforementioned code on both the day and the time. Let's try by making a sample table and utilizing this function to fill it with random data.

GO
CREATE TABLE [DateTable] 
    (
      [ID] INT IDENTITY,
      [Date] DATETIME
    )
--Let's add some data
GO
INSERT  INTO [DateTable]
SELECT DBO.[GenerateRandomDateTime]
('2010-10-28 11:21:13.937','2012-10-30 11:23:13.937')
GO 50

SELECT * FROM [DateTable]
GO

Above "GO 50" will execute the insert script batch 50 times so above 50 random datetime are inserted in the table. Below is the screenshot of the output which I received after running the above script.

That won't work because Order by doesn't continue to compute Random() after it receives a result.

You must create a series of random numbers, then choose each entry from the cities table one at a time. Any join will result in the result being ordered, hence you will need to create a plsql method to accomplish this.

Example 1: Here is an illustration of a pl-sql procedure that randomly inserts records from one table into another. An exception from the function is this.

The size of the random result set is constrained, and repeating a certain amount is prohibited by additional logic.

--First create cursor of the source records
OPEN _questions SCROLL for  (Select  *    from questions
    where quest_expire > now()::date
    and trim( both '' from quest_type)  = 
_classexams.exam_quest_type
    and trim( both '' from quest_level) = 
_classexams.exam_level
    order by quest_id );

Example 2: Need to limit the number range created by random so not to exceed the record count created by the Cursor:

select count(quest_id) into _rcount from educate.questions
     where quest_expire > now()::date
     and trim( both '' from quest_type)  = 
_classexams.exam_quest_type
     and trim( both '' from quest_level) = 
_classexams.exam_level ;

Generate a Random list of of numbers:

for _randlist IN (Select num from (
    select round(random()*1000)::int as num from 
generate_series(1,100000)) rand
    where num <= _rcount and num > 0 ) LOOP
FETCH ABSOLUTE _randlist.num from _questions into _quest ;

Next Insert into Into the destination Table

end loop;

Example 3: I have a table of user addresses that I'm trying to randomly populate with data from a cities table. Using the following query:

INSERT INTO useraddrs(userid, addrdesc, city, stprov)
SELECT u.userid, 'Home', c.cityname, c.stateabbr
FROM users u, cities c
WHERE u.userid NOT IN (SELECT userid FROM useraddrs)
AND cid=(SELECT cid FROM cities ORDER BY RANDOM() LIMIT 1);