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:
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.
WITH cte (n) AS
SELECT day_of_calendar AS n
WHERE n BETWEEN 1 AND 1000
(t1.n -1) * 1000 + t2.n
FROM cte AS t1, cte AS t2
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!
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.
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