TSQL：A表字段与B表中的关联，关联条件中一列是随机关联的实现方式

A表字段与B表中的关联，关联条件中一列是随机关联的实现方式

create table test(
rsrp string,
rsrq string,
tkey string,
distan string
);

insert into test values('-90.28','-37','tkey1','10');
insert into test values('-92.35','-40','tkey1','30');
insert into test values('-94.36','-34','tkey2','5');
insert into test values('-93.88','-38','tkey2','19');

select * from test;
+------------+------------+------------+--------------+--+
| test.rsrp  | test.rsrq  | test.tkey  | test.distan  |
+------------+------------+------------+--------------+--+
| -90.28     | -37        | tkey1      | 10           |
| -92.35     | -40        | tkey1      | 30           |
| -94.36     | -34        | tkey2      | 5            |
| -93.88     | -38        | tkey2      | 19           |
+------------+------------+------------+--------------+--+

create table test_latlng
(
tkey string,
lat string,
lng string
);
insert into test_latlng values('tkey1','lat1','lng1');
insert into test_latlng values('tkey1','lat2','lng2');
insert into test_latlng values('tkey1','lat3','lng3');
insert into test_latlng values('tkey1','lat4','lng4');
insert into test_latlng values('tkey2','lat1','lng1');
insert into test_latlng values('tkey2','lat2','lng2');
insert into test_latlng values('tkey2','lat3','lng3');
insert into test_latlng values('tkey2','lat4','lng4');

0: jdbc:hive2://10.78.152.62:21066/> select * from test_latlng;
+-------------------+------------------+------------------+--+
| test_latlng.tkey  | test_latlng.lat  | test_latlng.lng  |
+-------------------+------------------+------------------+--+
| tkey1             | lat1             | lng1             |
| tkey1             | lat2             | lng2             |
| tkey1             | lat3             | lng3             |
| tkey1             | lat4             | lng4             |
| tkey2             | lat1             | lng1             |
| tkey2             | lat2             | lng2             |
| tkey2             | lat3             | lng3             |
| tkey2             | lat4             | lng4             |
+-------------------+------------------+------------------+--+

select rsrp,rsrq,t10.tkey,lat,lng,t10.rn 
from 
(
select rsrp,rsrq,tkey,row_number()over(partition by tkey order by cast(rand() * 100 as int) asc) as rn 
from test
group by rsrp,rsrq,tkey
) t10
inner join 
(
select lat,lng,tkey,row_number()over(partition by tkey order by cast(rand() * 10000 as int) asc) as rn 
from test_latlng
group by lat,lng,tkey
) t11
on t10.tkey=t11.tkey and t10.rn=t11.rn;
+---------+-------+-----------+-------+-------+---------+--+
|  rsrp   | rsrq  | t10.tkey  |  lat  |  lng  | t10.rn  |
+---------+-------+-----------+-------+-------+---------+--+
| -90.28  | -37   | tkey1     | lat2  | lng2  | 1       |
| -92.35  | -40   | tkey1     | lat3  | lng3  | 2       |
| -93.88  | -38   | tkey2     | lat3  | lng3  | 1       |
| -94.36  | -34   | tkey2     | lat2  | lng2  | 2       |
+---------+-------+-----------+-------+-------+---------+--+