SQL查询6个分离度用于网络分析 [英] SQL query 6 degrees of separation for network analysis

问题描述

我正在使用D3.js进行网络分析，以显示我的应用程序中已连接的电话号码（分离度最低为6度）。查找初始连接的SQL（postgres）在下面，非常简单。但是，我对如何修改它以遍历六个级别的连接然后停止感到困惑。

I'm building a network analysis using D3.js to show connected phone numbers within my app down to six degrees of separation. The SQL (postgres) to find initial connections is below and fairly straightforward. However, I am stumped on how to modify this to traverse through six levels of connections then stop.

SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN (SELECT hash FROM game.phone_hashes WHERE player_id = $1);

我发现提到了Common Table Expressions（CTE）并通过对该问题的研究进行了递归，不确定如何在此处应用它们。

I have found mentions of Common Table Expressions (CTE) and recursion through research into this problem, but am unsure how to apply them here.

我的目标是通过通用电话哈希将所有玩家连接到初始玩家（$ 1），然后将所有玩家通过公用电话哈希连接到这些连接中的每一个，并连续6个间隔。

What I'm aiming for is to get all the players connected to the initial player ($1) through a common phone hash, then all the players connected to each of those connections through a common phone hash, and on and on out to 6 degrees of separation.

推荐答案

我想这就是您的意思：

with recursive tc as(
select $1 as player_id, 1 as level
  union
select ph2.player_id, level+1
  from tc, phone_hashes ph1, phone_hashes ph2
  where tc.player_id=ph1.player_id
  and ph1.hash=ph2.hash
  and tc.level < 6  
)    
select distinct player_id from tc

这篇关于SQL查询6个分离度用于网络分析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！