问题

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

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

解答

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

with tmp as
(
select '孙悟空' as from_user, '唐僧' as to_user
union all
select '唐僧' as from_user, '如来佛祖' as to_user
union all
select '唐僧' as from_user, '观音菩萨' as to_user
union all
select '观音菩萨' as from_user, '如来佛祖' as to_user
union all
select '唐僧' as from_user, '孙悟空' as to_user
union all
select '孙悟空' as from_user, '玉皇大帝' as to_user
union all
select '玉皇大帝' as from_user, '如来佛祖' as to_user
union all
select '如来佛祖' as from_user, '观音菩萨' as to_user
union all
select '如来佛祖' as from_user, '玉皇大帝' as to_user
union all
select '如来佛祖' as from_user, '唐僧' as to_user
union all
select '孙悟空' as from_user, '猪八戒' as to_user
union all
select '猪八戒' as from_user, '嫦娥' as to_user
union all
select '猪八戒' as from_user, '孙悟空' as to_user
union all
select '猪八戒' as from_user, '唐僧' as to_user
union all
select '猪八戒' as from_user, '沙僧' as to_user
union all
select '沙僧' as from_user, '猪八戒' as to_user
union all
select '沙僧' as from_user, '玉皇大帝' as to_user
union all
select '沙僧' as from_user, '孙悟空' as to_user
union all
select '沙僧' as from_user, '唐僧' as to_user
)
select
a.from_user,
a.to_user,
if(b.from_user is not null, 1, 0) as is_friend -- 1：互相关注 
from tmp a
left join tmp b
on a.from_user=b.to_user and a.to_user=b.from_user
;

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

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