A表字段与B表中的关联，关联条件中一列是随机关联的实现方式
1 create table test( 2 rsrp string, 3 rsrq string, 4 tkey string, 5 distan string 6 ); 7 8 insert into test values('-90.28','-37','tkey1','10'); 9 insert into test values('-92.35','-40','tkey1','30'); 10 insert into test values('-94.36','-34','tkey2','5'); 11 insert into test values('-93.88','-38','tkey2','19'); 12 13 select * from test; 14 +------------+------------+------------+--------------+--+ 15 | test.rsrp | test.rsrq | test.tkey | test.distan | 16 +------------+------------+------------+--------------+--+ 17 | -90.28 | -37 | tkey1 | 10 | 18 | -92.35 | -40 | tkey1 | 30 | 19 | -94.36 | -34 | tkey2 | 5 | 20 | -93.88 | -38 | tkey2 | 19 | 21 +------------+------------+------------+--------------+--+ 22 23 create table test_latlng 24 ( 25 tkey string, 26 lat string, 27 lng string 28 ); 29 insert into test_latlng values('tkey1','lat1','lng1'); 30 insert into test_latlng values('tkey1','lat2','lng2'); 31 insert into test_latlng values('tkey1','lat3','lng3'); 32 insert into test_latlng values('tkey1','lat4','lng4'); 33 insert into test_latlng values('tkey2','lat1','lng1'); 34 insert into test_latlng values('tkey2','lat2','lng2'); 35 insert into test_latlng values('tkey2','lat3','lng3'); 36 insert into test_latlng values('tkey2','lat4','lng4'); 37 38 0: jdbc:hive2://10.78.152.62:21066/> select * from test_latlng; 39 +-------------------+------------------+------------------+--+ 40 | test_latlng.tkey | test_latlng.lat | test_latlng.lng | 41 +-------------------+------------------+------------------+--+ 42 | tkey1 | lat1 | lng1 | 43 | tkey1 | lat2 | lng2 | 44 | tkey1 | lat3 | lng3 | 45 | tkey1 | lat4 | lng4 | 46 | tkey2 | lat1 | lng1 | 47 | tkey2 | lat2 | lng2 | 48 | tkey2 | lat3 | lng3 | 49 | tkey2 | lat4 | lng4 | 50 +-------------------+------------------+------------------+--+ 51 52 select rsrp,rsrq,t10.tkey,lat,lng,t10.rn 53 from 54 ( 55 select rsrp,rsrq,tkey,row_number()over(partition by tkey order by cast(rand() * 100 as int) asc) as rn 56 from test 57 group by rsrp,rsrq,tkey 58 ) t10 59 inner join 60 ( 61 select lat,lng,tkey,row_number()over(partition by tkey order by cast(rand() * 10000 as int) asc) as rn 62 from test_latlng 63 group by lat,lng,tkey 64 ) t11 65 on t10.tkey=t11.tkey and t10.rn=t11.rn; 66 +---------+-------+-----------+-------+-------+---------+--+ 67 | rsrp | rsrq | t10.tkey | lat | lng | t10.rn | 68 +---------+-------+-----------+-------+-------+---------+--+ 69 | -90.28 | -37 | tkey1 | lat2 | lng2 | 1 | 70 | -92.35 | -40 | tkey1 | lat3 | lng3 | 2 | 71 | -93.88 | -38 | tkey2 | lat3 | lng3 | 1 | 72 | -94.36 | -34 | tkey2 | lat2 | lng2 | 2 | 73 +---------+-------+-----------+-------+-------+---------+--+