经典sql题（十一）查找共同好友或相互关注 二

在处理海量数据时，使用 JOIN 可能会导致性能瓶颈，特别是当参与 JOIN 的表非常大时，JOIN 操作通常需要扫描多个表，并且在连接条件上进行复杂的匹配，这会消耗大量的计算资源和时间。相反，使用聚合或过滤的方法可以减少数据处理的复杂性，从而提高性能。以下是使用不依赖于 JOIN 的方法找出互相关注用户对的方法

1. 问题背景

设想我们有一个名为 follow 的表，记录用户之间的关注关系。我们的目标是找出所有互相关注的用户对。比如，如果用户 A 关注了用户 B，而用户 B 也关注了用户 A，则我们可以认为这是一种双向关系。

2. 数据结构

假设我们的 follow 表结构如下：

from_user	to_user
1	2
2	1
1	3
3	1
4	5
5	4
2	3

3. SQL 查询步骤解析

3.1 内部查询

我们首先需要通过一个内部查询生成一个唯一标识符，表示每对用户的关注关系。这个标识符将使我们能够识别出相同用户对的双向关系。

SELECT
    from_user,
    to_user,
    IF(from_user > to_user, 
       CONCAT(from_user, '-', to_user), 
       CONCAT(to_user, '-', from_user)) AS concat_users
FROM 
    follow;

结果

from_user	to_user	concat_users
1	2	1-2
2	1	1-2
1	3	1-3
3	1	1-3
4	5	4-5
5	4	4-5
2	3	2-3

3.2 中间结果 - 聚合双向关系

接下来，我们需要利用窗口函数来计算每个 concat_users 的出现次数，从而识别出双向关系。

SELECT
    from_user,
    to_user,
    SUM(1) OVER(PARTITION BY concat_users) AS flag
FROM
    (
        SELECT
            from_user,
            to_user,
            IF(from_user > to_user, 
               CONCAT(from_user, '-', to_user), 
               CONCAT(to_user, '-', from_user)) AS concat_users
        FROM 
            follow
    ) t;

结果

from_user	to_user	flag
1	2	2
2	1	2
1	3	2
3	1	2
4	5	2
5	4	2
2	3	1
3	2	1

3.3 外部查询 - 筛选双向关系

最后，我们在外部查询中筛选出 flag = 2 的记录，这表明这些用户对之间存在双向关注关系。

SELECT
    t1.from_user,
    t1.to_user
FROM
    (
        SELECT
            from_user,
            to_user,
            SUM(1) OVER(PARTITION BY concat_users) AS flag
        FROM
            (
                SELECT
                    from_user,
                    to_user,
                    IF(from_user > to_user, 
                       CONCAT(from_user, '-', to_user), 
                       CONCAT(to_user, '-', from_user)) AS concat_users
                FROM 
                    follow
            ) t
    ) t1
WHERE
    t1.flag = 2;

最终结果

from_user	to_user
1	2
2	1
4	5
5	4

根据上述查询结果，我们可以分析出每对用户之间的好友关系：

用户 1 和 用户 2 是好友。
用户 4 和 用户 5 是好友。