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

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 +---------+-------+-----------+-------+-------+---------+--+

 

posted @ 2018-02-03 12:52  cctext  阅读(571)  评论(0)  编辑  收藏  举报