n

问题

nnnn

现在有一张relation表，里面只有两个字段：from_user和to_user，代表关注关系从from指向to，即from_user关注了to_user。现在要找出互相关注的所有人。

nnnn

from_user    to_usern孙悟空          唐僧n唐僧            如来佛祖n唐僧            观音菩萨n观音菩萨         如来佛祖n唐僧            孙悟空n孙悟空          玉皇大帝n玉皇大帝        如来佛祖n如来佛祖         观音菩萨n如来佛祖         玉皇大帝n如来佛祖         唐僧n孙悟空          猪八戒n猪八戒            嫦娥n猪八戒           孙悟空n猪八戒           唐僧n猪八戒          沙僧n沙僧            猪八戒n沙僧            玉皇大帝n沙僧            孙悟空n沙僧            唐僧

nnnn

解答

nnnn

方法一，自关联join，t1.from = t2.to (t1主动关注者被t2的关注) + t1.to= t2.from (t2主动关注者被t1的关注) ，会有数据膨胀。

nnnn

with tmp asn(nselect '孙悟空' as from_user, '唐僧' as to_usernunion allnselect '唐僧' as from_user, '如来佛祖' as to_usernunion allnselect '唐僧' as from_user, '观音菩萨' as to_usernunion allnselect '观音菩萨' as from_user, '如来佛祖' as to_usernunion allnselect '唐僧' as from_user, '孙悟空' as to_usernunion allnselect '孙悟空' as from_user, '玉皇大帝' as to_usernunion allnselect '玉皇大帝' as from_user, '如来佛祖' as to_usernunion allnselect '如来佛祖' as from_user, '观音菩萨' as to_usernunion allnselect '如来佛祖' as from_user, '玉皇大帝' as to_usernunion allnselect '如来佛祖' as from_user, '唐僧' as to_usernunion allnselect '孙悟空' as from_user, '猪八戒' as to_usernunion allnselect '猪八戒' as from_user, '嫦娥' as to_usernunion allnselect '猪八戒' as from_user, '孙悟空' as to_usernunion allnselect '猪八戒' as from_user, '唐僧' as to_usernunion allnselect '猪八戒' as from_user, '沙僧' as to_usernunion allnselect '沙僧' as from_user, '猪八戒' as to_usernunion allnselect '沙僧' as from_user, '玉皇大帝' as to_usernunion allnselect '沙僧' as from_user, '孙悟空' as to_usernunion allnselect '沙僧' as from_user, '唐僧' as to_usern)nselectna.from_user,na.to_user,nif(b.from_user is not null, 1, 0) as is_friend -- 1：互相关注 nfrom tmp anleft join tmp bnon a.from_user=b.to_user and a.to_user=b.from_usern;

nnnn

方法二，针对表中数据每一行，新增一列单向关系。例如A关注B 或者 B关注A，单向关注关系都是A->B。当A->B这一行时，按单向关注关系分区，分区内有A->B 和 A->B两行，那么计数为2的就是互相关注的。

nnnn

with tmp asn(n    select '孙悟空' as from_user, '唐僧' as to_usernunion allnselect '唐僧' as from_user, '如来佛祖' as to_usernunion allnselect '唐僧' as from_user, '观音菩萨' as to_usernunion allnselect '观音菩萨' as from_user, '如来佛祖' as to_usernunion allnselect '唐僧' as from_user, '孙悟空' as to_usernunion allnselect '孙悟空' as from_user, '玉皇大帝' as to_usernunion allnselect '玉皇大帝' as from_user, '如来佛祖' as to_usernunion allnselect '如来佛祖' as from_user, '观音菩萨' as to_usernunion allnselect '如来佛祖' as from_user, '玉皇大帝' as to_usernunion allnselect '如来佛祖' as from_user, '唐僧' as to_usernunion allnselect '孙悟空' as from_user, '猪八戒' as to_usernunion allnselect '猪八戒' as from_user, '嫦娥' as to_usernunion allnselect '猪八戒' as from_user, '孙悟空' as to_usernunion allnselect '猪八戒' as from_user, '唐僧' as to_usernunion allnselect '猪八戒' as from_user, '沙僧' as to_usernunion allnselect '沙僧' as from_user, '猪八戒' as to_usernunion allnselect '沙僧' as from_user, '玉皇大帝' as to_usernunion allnselect '沙僧' as from_user, '孙悟空' as to_usernunion allnselect '沙僧' as from_user, '唐僧' as to_usern)nselectn	from_usern	,to_usern	,count(1) over(partition by feature) as is_friend ---1：不是 2：是nfromn(n    selectn    from_usern    ,to_usern    --当有互相关注时，保证只将其中的一对用户调换from_user和to_user并拼接n    ,if(from_user>to_user,concat(from_user,to_user),concat(to_user,from_user)) as featuren    from tmpn)t1n;

n