[大数据]计算相互关注类型的SQL

这是前一段时间某公司的面试题，有多种思路，我这里简单实现两种：

gift表: 代表着uid给to_uid 送了礼物

follow表：代表uid关注了to_uid

要求的结果表为：

说明：

用户a给用户b刷了礼物，求用户a 与 用户b的互相关注类型，0代表两者互不关注，
1代表用户a关注了用户b，2代表用户b关注了用户a，3代表互相关注。

实现思路如下：

思路1：

SELECT tmp.uid,tmp.to_uid,sum(follow_type) follow_type
from (
SELECT
g.uid,g.to_uid,
CASE WHEN f.uid IS NULL THEN 0 ELSE 1 END AS follow_type
FROM gift g 
LEFT JOIN follow f 
ON g.uid = f.uid
AND g.to_uid = f.to_uid
UNION ALL
SELECT
g.uid,g.to_uid,
CASE WHEN f.uid IS NULL THEN 0 ELSE 2 END AS follow_type
FROM gift g 
LEFT JOIN follow f 
ON g.uid = f.to_uid
AND g.to_uid = f.uid
) tmp
GROUP BY tmp.uid,tmp.to_uid;

思路2 ：

select 
uid,to_uid,follow_type
from(
SELECT
a.uid,a.to_uid,
case WHEN count(*)  over(partition by a.uid,a.to_uid) = 2 THEN 3
WHEN (fnv_hash(a.uid)- fnv_hash(a.to_uid)) = (fnv_hash(b.uid) - fnv_hash(b.to_uid)) THEN 1 
WHEN b.to_uid IS NULL THEN 0 
ELSE 2 END AS follow_type
FROM gift a 
LEFT JOIN follow b 
on fnv_hash(a.uid)+ fnv_hash(a.to_uid) = fnv_hash(b.uid) + fnv_hash(b.to_uid)
)tmp
group by uid,to_uid,follow_type

• 注：fnv_hash()是Impala中计算hash值的函数，在hive中该函数为hash()