代码之家 › 专栏 › 技术社区 › Niko Gamulin

用纯sql计算两个用户之间的链接强度

sql

Niko Gamulin · 技术社区 · 14 年前

S=(普通好友数)/(个人好友数1联合个人好友数2)

为了计算上面的值,我已经开始编写以下查询:

WITH user1 AS
(
SELECT calling_party, called_party FROM monthly_connections WHERE calling_party = 'a' OR called_party ='a'
),
user2 AS
(
SELECT calling_party, called_party FROM monthly_connections WHERE calling_party = 'b' OR called_party ='b'
),
commonUsers AS
(
SELECT COUNT (*) common_users_count FROM user1 u1 INNER JOIN user2 u2 ON u1.called_party = u2.called_party OR u1.calling_party = u2.calling_party OR u1.called_party = u2.calling_party OR u1.calling_party = u2.called_party
),
unionUsers AS
(
SELECT COUNT(*) FROM user1  UNION SELECT  COUNT(*) FROM user2
)

然后应使用工会用户的数量(不确定是否正确写入)作为分母。无论如何,我不知道如何完成程序,以获得所需的价值,所以我会感谢你的帮助。

谢谢您!

4 回复 | 直到 14 年前

cdonner 14 年前

count(*)查询返回可以算术添加的标量。不需要使用UNION(这是一个SET操作)。

Yellowfog 14 年前

我想你想说的是,S是普通朋友的数量,而不是1个人或2个人的朋友总数。

也许其他人会给你正确的SQL,但这里有一些伪代码,我想可以得到这两个数字:

SELECT COUNT(*) as AllFriends, 
SUM(Case when A.FriendID is not null and B.FriendID is not null then 1 else 0 end) AS JointFriends FROM
(
  (SELECT FriendID from Friends WHERE PersonID=x) A
  FULL OUTER JOIN 
  (SELECT FriendID from Friends WHERE PersonID=y) B
  ON A.FriendID = B.FriendID
) C

Niko Gamulin 14 年前

WITH user1_buddies AS
(
SELECT called_party AS buddy FROM monthly_connections WHERE calling_party = '80A8A8D9D9AC58BE479C59D9BC59625691F32E76'
UNION SELECT calling_party AS buddy FROM monthly_connections WHERE called_party ='80A8A8D9D9AC58BE479C59D9BC59625691F32E76'
),
user2_buddies AS
(
SELECT calling_party AS buddy FROM monthly_connections WHERE  called_party ='11171309B5B6163D71B477D99D29763E4A7305E1'
UNION SELECT called_party AS buddy FROM monthly_connections WHERE calling_party = '11171309B5B6163D71B477D99D29763E4A7305E1'
),
commonUsers AS
(
SELECT cu.b1, cu.b2 FROM (SELECT u1.buddy b1, u2.buddy b2 FROM user1_buddies u1 INNER JOIN user2_buddies u2 ON u1.buddy = u2.buddy) cu
),
allUsers AS
(
SELECT b allUsersCount FROM (SELECT buddy b FROM user1_buddies UNION SELECT buddy b FROM user2_buddies) cu
)
SELECT(CAST((SELECT COUNT (*) FROM commonUsers) AS decimal(10,5)) / (CAST((SELECT COUNT (*) FROM allUsers) AS decimal(10,5)))) link_strength

Beth 14 年前

在多个步骤中处理这个问题可能更容易,这样您就可以看到并检查中间输出。

您需要一个带有userID pk的用户表,并且需要将它与自身交叉连接(完全外部连接)以获取所有的伙伴对,除了添加到where子句以排除userID=userID(自身)所在的行。没有人是他们自己的好友或自称为好友。)这定义了所有可能的好友连接集。

您已经有了一组好友的每月连接表,呼叫buddy->呼叫buddy。这定义了一种类型的伙伴连接。

您需要一个月连接表的第三个实例,其中包含2个月连接表实例,它们连接在被调用字段上。确保在where子句中排除两个表中调用用户相等的行。如果两个不同的用户调用同一个第三个用户,则定义第三种类型的好友连接。

现在您可以确定每对好友的共同好友数。这是这三个集合的行数加在一起。