计算互相关注的SQL怎么写

计算互相关注的SQL应该怎么写
用户好友关系是一个产品的核心数据，只允许互相关注的用户之间发消息称为强关系型产品，比如微信；反之，不互相关注也能看到动态，比如微博，就是弱关系型产品

因为微信的存在，现在基本能做大的都是社区型的，弱关系型的产品了。所以互联网公司就很容易碰到，从单向关注数据中计算是否双向关注这种需求。

假设现在有一张表，叫table_relation里面只有两个字段，from_user，to_user, 代表关注关系从from指向to，即from_user关注了to_user。

select 
  a.from_user,
  a.to_user,
  if(b.from_user is not null, 1, 0) as is_friend
from table_relation a left join table_relation b
on a.from_user = b.to_user and a.to_user = b.from_user

这个sql可以实现需求，问题是当用户量到了亿级别，关注关系到了百亿级别，join起来的效率就会很低。

避免join的方法就是找到双向关注之间的共性，假设有两条记录(A,B)和(B,A), 它们有什么特点呢？

哈哈，答案就是都有A和B，假设按照字典顺序做一次排序，那么排序后的结果都是(A, B), (A, B)。那我们的思路就是把特征相同的数据分到一组，计算组里面的数据条数，为1则是单向关注，为2则是双向关注。这样，利用窗口函数，不用join就能得到答案了～

select 
  a.from_user,
  a.to_user,
  if( sum(1) over (partition by feature) > 1, 1, 0) as is_friend
from 
(
  select 
    a.from_user,
    a.to_user,
    if(from_user > to_user, concat(to_user, from_user), concat(from_user, to_user)) as feature
  from table_relation 
)a

这里没有考虑数据重复的情况，假设有两条(A,B)(A,B)，那结果就错了，不过这种数据存在说明了数仓建设的失败。如果真有，那就先去重一次即可。

这里也没有考虑用户id是非string数据类型的情况，不过一般都能转成string。

最后，不一定非要排序做字符串，能计算出共同特点就行。比如用hash函数也没问题。