查找每个用户在Facebook上的受欢迎程度百分比。受欢迎程度百分比定义为：用户拥有的朋友总数除以平台上的用户总数，然后乘以100转换成一个百分比。 输出每个用户及其受欢迎程度百分比。按用户id升序排列。 “id1”和“id2”列是成对的朋友。表如下：

id1	id2
1	2
1	3
1	4
1	5
1	6
2	6
2	7
3	8
3	9

要求输出结果

id1	feiend_bfb
1	55.56%
2	33.33%
3	33.33%
4	11.11%
5	11.11%
6	22.22%
7	11.11%
8	11.11%
9	11.11%

数据准备

create table temp_friends_0302
(
    id1 int,--'用户',
    id2 int -- '用户好友'
) row format delimited fields terminated by '\t'
    stored as orc
    tblproperties ("orc.compress" = "snappy");

insert into temp_friends_0302
values (1, 2),
       (1, 3),
       (1, 4),
       (1, 5),
       (1, 6),
       (2, 6),
       (2, 7),
       (3, 8),
       (3, 9);
select * from temp_friends_0302;

 

解决方案

其实本题的用意在于求用户数以及用户对应的好友数

 1、通过函数计算每个用户的好友数，id1 id2 是相互的，需要union 一下

select id1, id2
from temp_friends_0302
union all
select id2 id1, id1 id2
from temp_friends_0302;

所有操作应该以此数据集作为底层数据

计算好友用户数

select id1, size(collect_set(id2)) friend_num
from (select id1, id2
      from temp_friends_0302
      union all
      select id2 id1, id1 id2
      from temp_friends_0302) t
group by id1;

  2、计算用户总数

select id1, sum(1) over () user_num, friend_num
from (select id1, size(collect_set(id2)) friend_num
      from (select id1, id2
            from temp_friends_0302
            union all
            select id2 id1, id1 id2
            from temp_friends_0302) t
      group by id1) t;

 3、根据第二部结果计算最总结果:用户拥有的朋友总数除以平台上的用户总数，然后乘以100转换成一个百分比。

select id1, cast(friend_num * 100 / t_num as decimal(5, 2)) || '%' feiend_bfb
from (select id1, sum(1) over () t_num, friend_num
      from (select id1, size(collect_set(id2)) friend_num
            from (select id1, id2
                  from temp_friends_0302
                  union all
                  select id2 id1, id1 id2
                  from temp_friends_0302) t
            group by id1) t) t;

  总结

主要使用了 size(collect_set())函数，collect_set() 具有剔重效果，所以无需考虑重复数据，另外需要注意用户好友是相互的，所以原表需要union一下。