SQL选择您可能认识的人 [英] SQL selecting people you may know
问题描述
The question you're asking appears subjective and is likely to be closed.
我在填写标题字段时看到上面的可怕的警告，我并不感到惊讶.
I wasn't surprised when I saw above horrible warning while I was filling in title field.
我读了几乎所有谈论friends of friends或mutual friends的线程，但是我不确定是否找到了我想做的正确解决方案.
I read almost every thread talking about friends of friends or mutual friends but I'm not sure I found the right solution that I want to do.
对不起，我不擅长英语或SQL.
I'm sorry I'm not good at English nor SQL.
当我不擅长两种语言时，如何找到正确的答案?
How can I find the right answer while being not good at both of languages?
我决定不得不问.我不会为down-vote或任何duplication warning失望.
I decided I have to ask. I won't let myself down for down-votes or any duplication warnings.
我希望得到答案，我会尽可能真诚地写下来，以便进一步解决类似的问题.
As I want the answer, I'll write down as sincerely as possible for any further similar problems can be helped.
我有一张与朋友关系的表.
I have a table for friend relations.
FRIEND (TABLE)
-----------------------------------
PLAYER_ID(PK,FK) FRIEND_ID(PK,FK)
-----------------------------------
1 2 // 1 knows 2
2 1 // 2 knows 1
1 3 // 1 knows 3
2 3 // 2 knows 3
2 4 // 2 knows 4
2 5 // 2 knows 5 // updated
3 5 // 3 knows 5 // updated
1 100
1 200
1 300
100 400
200 400
300 400
composite primary keys和PLAYER都是PLAYER表中的外键.
我问过这样的好人并得到了人彼此认识"的答案.
I asked and got answered from such nice people for "people know each other".
我有这样的看法.
ACQUAINTANCE (VIEW)
-----------------------------------
PLAYER_ID(PK,FK) FRIEND_ID(PK,FK)
-----------------------------------
1 2 // 1 knows 2
2 1 // 2 knows 1
您可能会注意到，这种关系的业务逻辑具有以下两个目的.
As you might be noticed, this relationships' business logic has following two purposes.
- 一个玩家可以说他或她认识其他人.
- 两个人都说彼此认识时，可以说是熟人.
现在，我想知道有什么好的方法
And, now, I want to know is there any good way for
- 选择其他PLAYER_ID
- 使用给定的PLAYER(PLAYER_ID)(例如1)
- 每个人都是给定玩家直接朋友的朋友"之一
- 每个人都不是玩家本人(1-> 2-> 1除外)
- 每个人都不是玩家的直接朋友(不包括1-> 2-> 3 x 1-> 3中的3)
- 如果可能，请按共同朋友的数量订购.
我认为在>您可能认识的人" sql查询是我必须遵循的最接近路径.
I think the Justin Niessner's answer in "people you may know" sql query is the closest path that I must follow.
谢谢.
如果该主题确实重复且不必要，我将关闭线程.
I'll close the thread if this subject is really duplicated and not necessary.
更新---------------------------------------------- ----------------
UPDATE --------------------------------------------------------------
拉斐尔·奥尔特豪斯(RaphaëlAlthaus)的评论whose name is same with my future daughter(是男孩的名字吗?)，
for Raphaël Althaus's comment whose name is same with my future daughter (is it boy's name?),
3是friends of friends of 1的候选人，因为
1 knows 2
2 knows 3
但由于
1 already knows 3
基本上我想为given player服务
people he or she may know
which is not himself or herself // this is nothing but obvious
which each is not already known to himself
有上表
by 1 -> 2 -> 4 and 1 -> 3 -> 5
4 and 5 can be suggested for 1 as 'people you may know'
order by number of mutual friends will be perfect
but I don't think I can understand even if someone show me how. sorry.
谢谢.
更新---------------------------------------------- -----------------------
UPDATE ---------------------------------------------------------------------
我认为，即使这不是正确的答案，我也必须根据自己学到的知识逐步进行尝试. 如果我做错了任何事情，请告诉我.
I think I must try step by step by myself from what I've learned FROM HERE WITH VARIOUS PEOPLE even if it's not the right answer.
Please let me know if I'm doing anything wrong.
首先，让我自己加入FRIEND表本身.
First of all, let me self join the FRIEND table itself.
SELECT *
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
打印
+-----------+-----------+-----------+-----------+
| PLAYER_ID | FRIEND_ID | PLAYER_ID | FRIEND_ID |
+-----------+-----------+-----------+-----------+
| 1 | 2 | 2 | 1 |
| 1 | 2 | 2 | 3 |
| 1 | 2 | 2 | 4 |
| 1 | 2 | 2 | 5 |
| 1 | 3 | 3 | 5 |
| 2 | 1 | 1 | 2 |
| 2 | 1 | 1 | 3 |
| 2 | 3 | 3 | 5 |
+-----------+-----------+-----------+-----------+
仅F2.FRIEND_ID
F2.FRIEND_ID only
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
打印
+-----------+
| FRIEND_ID |
+-----------+
| 1 |
| 3 |
| 4 |
| 5 |
| 5 |
| 2 |
| 3 |
| 5 |
+-----------+
仅1个
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1;
打印
+-----------+
| FRIEND_ID |
+-----------+
| 1 |
| 3 |
| 4 |
| 5 |
| 5 |
+-----------+
不是1
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1;
打印
+-----------+
| FRIEND_ID |
+-----------+
| 3 |
| 4 |
| 5 |
| 5 |
+-----------+
不是1的直接已知物
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1);
打印
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 5 |
+-----------+
我想我要去那里.
更新---------------------------------------------- -------------------
UPDATE -----------------------------------------------------------------
添加了以下路径
1 -> 100 -> 400
1 -> 200 -> 400
1 -> 300 -> 400
最后一次查询打印(再次)
And the last query prints (again)
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 5 |
| 400 |
| 400 |
| 400 |
+-----------+
最后，我得到了候选人:4、5、400
at last, I got the candidates: 4, 5, 400
投放distinct肯定可以达到主要目标
Putting distinct surely work for the primary goal
SELECT DISTINCT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1);
打印
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 400 |
+-----------+
现在，需要通过相互计数来排序.
And, now, ordering by mutual counts needed.
以下是每个候选人的共同朋友数.
Here comes the number of mutual friends for each candidates.
+-----------+
| FRIEND_ID |
+-----------+
| 4 | 1 (1 -> 2 -> 4)
| 5 | 2 (1 -> 2 -> 5, 1 -> 3 -> 5)
| 400 | 3 (1 -> 100 -> 400, 1 -> 200 -> 400, 1 -> 300 -> 400)
+-----------+
我该如何按共同的朋友数量计算和订购?
How can I calculate and order by those number of mutual friends?
SELECT F2.FRIEND_ID, COUNT(*)
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1)
GROUP BY F2.FRIEND_ID;
打印
+-----------+----------+
| FRIEND_ID | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 5 | 2 |
| 400 | 3 |
+-----------+----------+
我明白了！
SELECT F2.FRIEND_ID, COUNT(*) AS MFC
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1)
GROUP BY F2.FRIEND_ID
ORDER BY MFC DESC;
打印
+-----------+-----+
| FRIEND_ID | MFC |
+-----------+-----+
| 400 | 3 |
| 5 | 2 |
| 4 | 1 |
+-----------+-----+
有人可以确认吗?该查询是否最佳?将其作为视图时可能会出现性能问题吗?
Can anybody please confirm this? Is that query optimal? Any possible performance problem when make it as a view?
谢谢.
更新---------------------------------------------- ----------------------------------------------
UPDATE --------------------------------------------------------------------------------------------
我创建了一个视图
CREATE VIEW FOLLOWABLE AS
SELECT F1.PlAYER_ID, F2.FRIEND_ID AS FOLLOWABLE_ID, COUNT(*) AS MFC
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F2.FRIEND_ID != F1.PLAYER_ID
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = F1.PLAYER_ID)
GROUP BY F2.FRIEND_ID
ORDER BY MFC DESC;
并经过测试.
mysql> select * from FOLLOWABLE;
+-----------+---------------+-----+
| PlAYER_ID | FOLLOWABLE_ID | MFC |
+-----------+---------------+-----+
| 1 | 400 | 3 |
| 1 | 5 | 2 |
| 2 | 100 | 1 |
| 2 | 200 | 1 |
| 2 | 300 | 1 |
| 1 | 4 | 1 |
+-----------+---------------+-----+
6 rows in set (0.01 sec)
mysql> select * from FOLLOWABLE WHERE PLAYER_ID = 1;
+-----------+---------------+-----+
| PlAYER_ID | FOLLOWABLE_ID | MFC |
+-----------+---------------+-----+
| 1 | 400 | 3 |
| 1 | 5 | 2 |
| 1 | 4 | 1 |
+-----------+---------------+-----+
3 rows in set (0.00 sec)
推荐答案
使用此 编辑
SELECT `friend_id` AS `possible_friend_id`
FROM `friends`
WHERE `player_id` IN ( --selecting those who are known
SELECT `friend_id` --by freinds of #1
FROM `friends`
WHERE `player_id` = 1)
AND `friend_id` NOT IN ( --but not those who are known by #1
SELECT `friend_id`
FROM `friends`
WHERE `player_id` = 1)
AND NOT `friend_id` = 1 --and are not #1 himself
--if one is known by multiple people
--he'll be multiple time in the list
GROUP BY `possible_friend_id` --so we group
ORDER BY COUNT(*) DESC --and order by amount of repeatings
这篇关于SQL选择您可能认识的人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
