hive经典面试题6--对数去重（[A,B],[B,A]）算一对，该怎么去重？

背景：
在很多业务中，都涉及到对数去重。例如：A和B建联，记了一条数据[A,B]，B又和A建联，又记了一条数据[B,A]。这两条记录，只是记录的顺序不一致，人还是A、B两个人，在业务中，这只算一对。

1.把A和B全部解析出来，然后排序，再拼接起来。这样[A,B]和[B,A]都改成[A,B]，再统计的话就简单多了。

注：到了这里，有人就会说了，都解析出来为啥不用count(distinct A,B)算对数呢，因为顺序不一样，count(distinct
A,B)和count(distinct B,A)是不一样的

代码如下：

spark-sql> with test1
         > (select '100001' as apptypeid,'huawei' as  srcqid,'android' as os,'1.2.3' as ver,'{"oneAccid":"ca123abc","twoAccid":"cb345abc"} 'as userinfo
         > union all
         > select '100001' as apptypeid,'huawei' as  srcqid,'android' as os,'1.2.3' as ver,'{"oneAccid":"cb345abc","twoAccid":"ca123abc"}' as userinfo
         > union all
         > select '100001' as apptypeid,'vivo' as  srcqid,'android' as os,'1.2.1' as ver,'{"oneAccid":"ca234abc","twoAccid":"ca678abc"}' as userinfo
         > union all
         > select '100001' as apptypeid,'oppo' as  srcqid,'android' as os,'1.2.1' as ver,'{"oneAccid":"ca231abc","twoAccid":"ca432abc"}' as userinfo
         > union all
         > select '100001' as apptypeid,'xiaomi' as  srcqid,'android' as os,'1.2.3' as ver,'{"oneAccid":"ca235abc","twoAccid":"cb564abc"}' as userinfo
         > union all
         > select '100001' as apptypeid,'xiaomi' as  srcqid,'android' as os,'1.2.3' as ver,'{"oneAccid":"cb564abc","twoAccid":"ca235abc"}' as userinfo
         > )
         > select
         >   apptypeid,
         >   srcqid,
         >   os,
         >   ver,
         >   concat_ws(',',collect_set(accid)) as accid
         > from 
         > (select 
         >   apptypeid,
         >   srcqid,
         >   os,
         >   ver,
         >   userinfo,
         >   accid
         > from 
         > (select
         >   apptypeid,
         >   srcqid,
         >   os,
         >   ver,
         >   userinfo,
         >   get_json_object(userinfo,'$.oneAccid') as accid
         > from test1
         > 
         > union all
         > select
         >   apptypeid,
         >   srcqid,
         >   os,
         >   ver,
         >   userinfo,
         >   get_json_object(userinfo,'$.twoAccid') as accid
         > from test1) t1
         > order by apptypeid,srcqid,os,ver,userinfo,accid desc) t2
         > group by apptypeid,srcqid,os,ver;
100001	huawei	android	1.2.3	cb345abc,ca123abc
100001	xiaomi	android	1.2.3	ca235abc,cb564abc
100001	oppo	android	1.2.1	ca432abc,ca231abc
100001	vivo	android	1.2.1	ca678abc,ca234abc
Time taken: 0.519 seconds, Fetched 4 row(s)

2.在业务中，我们很少用到array集合，对上述这个问题，其实就是一个集合问题。
一个函数sort_array就可以搞定了

代码如下：

spark-sql> with test1
         > (select '100001' as apptypeid,'huawei' as  srcqid,'android' as os,'1.2.3' as ver,'{"oneAccid":"ca123abc","twoAccid":"cb345abc"} 'as userinfo
         > union all
         > select '100001' as apptypeid,'huawei' as  srcqid,'android' as os,'1.2.3' as ver,'{"oneAccid":"cb345abc","twoAccid":"ca123abc"}' as userinfo
         > union all
         > select '100001' as apptypeid,'vivo' as  srcqid,'android' as os,'1.2.1' as ver,'{"oneAccid":"ca234abc","twoAccid":"ca678abc"}' as userinfo
         > union all
         > select '100001' as apptypeid,'oppo' as  srcqid,'android' as os,'1.2.1' as ver,'{"oneAccid":"ca231abc","twoAccid":"ca432abc"}' as userinfo
         > union all
         > select '100001' as apptypeid,'xiaomi' as  srcqid,'android' as os,'1.2.3' as ver,'{"oneAccid":"ca235abc","twoAccid":"cb564abc"}' as userinfo
         > union all
         > select '100001' as apptypeid,'xiaomi' as  srcqid,'android' as os,'1.2.3' as ver,'{"oneAccid":"cb564abc","twoAccid":"ca235abc"}' as userinfo
         > )
         > select 
         >   apptypeid,
         >   srcqid,
         >   os,
         >   ver,
         >   concat_ws(',',sort_array(array(oneaccid,twoaccid))) as accid
         > from 
         > (select
         >   apptypeid,
         >   srcqid,
         >   os,
         >   ver,
         >   get_json_object(userinfo,'$.oneAccid') as oneaccid,
         >   get_json_object(userinfo,'$.twoAccid') as twoaccid
         > from test1) t1;
100001	huawei	android	1.2.3	ca123abc,cb345abc
100001	huawei	android	1.2.3	ca123abc,cb345abc
100001	vivo	android	1.2.1	ca234abc,ca678abc
100001	oppo	android	1.2.1	ca231abc,ca432abc
100001	xiaomi	android	1.2.3	ca235abc,cb564abc
100001	xiaomi	android	1.2.3	ca235abc,cb564abc
Time taken: 0.329 seconds, Fetched 6 row(s)

通过上面的结果，我们可以看出sort_array这个函数，通过内部的排序机制将[A,B]和[B,A]变成了[A,B]，再用row_number() over()函数排序取第一条就可以解决问题了