经典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 内部查询
我们首先需要通过一个内部查询生成一个唯一标识符，表示每对用户的关注关系。这个标识符将使我们能够识别出相同用户对的双向关系。
SELECTfrom_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 的出现次数，从而识别出双向关系。
SELECTfrom_user,to_user,SUM(1) OVER(PARTITION BY concat_users) AS flag
FROM(SELECTfrom_user,to_user,IF(from_user > to_user, CONCAT(from_user, '-', to_user), CONCAT(to_user, '-', from_user)) AS concat_usersFROM 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 的记录，这表明这些用户对之间存在双向关注关系。
SELECTt1.from_user,t1.to_user
FROM(SELECTfrom_user,to_user,SUM(1) OVER(PARTITION BY concat_users) AS flagFROM(SELECTfrom_user,to_user,IF(from_user > to_user, CONCAT(from_user, '-', to_user), CONCAT(to_user, '-', from_user)) AS concat_usersFROM follow) t) t1
WHEREt1.flag = 2;
最终结果
| from_user | to_user |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 4 | 5 |
| 5 | 4 |
根据上述查询结果，我们可以分析出每对用户之间的好友关系：
用户 1 和 用户 2 是好友。
用户 4 和 用户 5 是好友。