I have two tables. Need to list the Name field randomly in the User col in tbl 2 using Big query SQL. Can someone help me please?

Table 1

Table 2

CodePudding user response：

First generate both tables tbl1 and tbl2. Then add a row_number as id_ok to tbl1. In the table helper we extract the maximum row numbers of tbl1.

floor((max_.A)*rand()) 1 as id_ok The rand generates a number between 0 and 1. Multiplied with the row number of table tbl1 (max_.A) and rounding downwards with floor gives a range between 0 and row_number -1. Therefore we add 1 and call it id_ok. In the last step we union the helper table with tbl1_.

with tbl1 as (select * from unnest(split("Tom Jack Harry"," ")) name),
tbl2 as (select * from unnest(split("Jan Feb Mar Apr May"," ")) month),

tbl1_ as (select row_number() over () as id_ok, * from tbl1),
helper as (
Select tbl2.*, floor((max_.A)*rand()) 1 as id_ok
from tbl2,((Select max(id_ok) A from tbl1_)) max_
)

Select *
from helper 
left join tbl1_
using(id_ok)

CodePudding user response：

Try this approach below:

with table_1 as (
  select 1 as id, 'Tom' as name
  union all select 2 as id, 'Jack' as name
  union all select 3 as id, 'Harry' as name
),
table_2 as (
  select 'Jan' as month, 2023 as year
  union all select 'Feb' as month, 2023 as year
  union all select 'Mar' as month, 2023 as year
  union all select 'Apr' as month, 2023 as year
  union all select 'May' as month, 2023 as year
),
add_t2_id as (
select 
month,
year,
cast(ROUND(1   RAND() * (3 - 1)) as int64) as rand_val
from table_2
)

select 
t2.month,
t2.year,
t1.name as user
from add_t2_id t2
inner join table_1 t1
on t1.id=t2.rand_val

I created a cte table namely add_t2_id that pair table_2 rows with a random number cast(ROUND(1 RAND() * (3 - 1)) as int64) as rand_val (generates random number to 1 -3). Then made a query joining add_t2_id and table_1 tying the random number generated by add_t2_id to the id from table_1.

Sample Result: