Creating a table of a given size filled with random numbers

Data Modeling
Fan

Creating a table of a given size filled with random numbers

How to generate a table of a given size (say, 1 million rows) with a column that represent a random number?

I need to create a table, which will have two columns: row_num, rand_num:

Row_num rand_num

             1           23

             2             3

             3           12

             4           89

             5            …

I can use random function random (1,100) if I already have a table with a certain number of rows. My struggle is to do it from scratch.

Tags (1)
6 REPLIES
Junior Contributor

Re: Creating a table of a given size filled with random numbers

WITH cte (n) AS
(
SELECT day_of_calendar AS n
FROM sys_calendar.CALENDAR
WHERE n BETWEEN 1 AND 1000
)
SELECT
(t1.n -1) * 1000 + t2.n
,RANDOM(1,100)
FROM cte AS t1, cte AS t2
Fan

Re: Creating a table of a given size filled with random numbers

Wow, it works, and it's clever. I was thinking to use sys_calendar.calendar table as a reference, but could not figure out how to expand beyond its limit. This is a good example of usefulness of the carthesian join.

I did not quite understand how WITH statement works. Can you explain it, please, or provide reference?

Perhaps, it is equivalent to:

SELECT (t1.n -1) * 1000 + t2.n ,RANDOM(1,100) FROM

(SELECT day_of_calendar AS n FROM sys_calendar.CALENDAR WHERE n BETWEEN 1 AND 1000) t1,

(SELECT day_of_calendar AS n FROM sys_calendar.CALENDAR WHERE n BETWEEN 1 AND 1000) t2

In any case, thanks so much!

Junior Contributor

Re: Creating a table of a given size filled with random numbers

Yes, this is exactly the same query.

WITH is mainly usefull if you need to use the same Derived Table multiple times.

Otherwise it's just by habit, Teradata only supported the WITH syntax in a limited way (mainly for recursive queries), (most of) those limitations have been lifted, but I still hardly use them. Other people coming from DB2 or SQL Server only use them instead of Derived Tables.

Re: Creating a table of a given size filled with random numbers

 (t1.n -1) * 1000 + t2.n


Could you explain what this bit does?

Junior Contributor

Re: Creating a table of a given size filled with random numbers

This creates a sequential number from 1 to 1,000,000: 

0 + 1

0 + 2

...

0 + 1000

1000 + 1

1000 + 2

...

1000 + 1000

2000 + 1

2000 + 2

Re: Creating a table of a given size filled with random numbers

Oh, that's clever.

  That's why the cross join is there.

 

Thanks dnoeth!