文章/答案/技术大牛

发布

社区首页 >问答首页 >用于网络分析的SQL查询6度分离

问用于网络分析的SQL查询6度分离
EN

Stack Overflow用户

提问于 2015-11-19 21:40:52

回答 2查看 1.5K关注 0票数 8

我正在构建一个网络分析，使用D3.js显示我的应用程序中连接的电话号码，将其降低到六度的距离。查找初始连接的SQL (postgres)位于下面，并且相当简单。然而，我很难理解如何修改它来遍历六层连接，然后停止。

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);

通过对这个问题的研究，我发现提到了公共表表达式(CTE)和递归，但我不知道如何在这里应用它们。

我的目标是让所有玩家通过一个普通的手机哈希连接到最初的播放器(1美元)，然后所有的玩家通过一个普通的电话哈希连接到每个连接上，然后一直到6度。

sql

postgresql

回答 2

Stack Overflow用户

回答已采纳

发布于 2015-11-24 10:03:37

我想这就是你的意思：

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

票数 8

Stack Overflow用户

发布于 2015-11-23 12:11:21

我认为应该是：

-- 6 degrees of separation
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 hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE hash IN
   (SELECT hash FROM game.phone_hashes
    WHERE hash IN 
    (SELECT hash FROM game.phone_hashes
     WHERE hash IN
     (SELECT hash FROM game.phone_hashes
      WHERE player_id = $1))))));

请参阅以下工作：

-- 1 degree of separation
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);

-- 2 degrees of separation
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 hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE player_id = $1));

-- 3 degrees of separation
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 hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE player_id = $1)));

-- 4 degrees of separation
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 hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE hash IN
   (SELECT hash FROM game.phone_hashes
    WHERE player_id = $1))));


-- 5 degrees of separation
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 hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE hash IN
   (SELECT hash FROM game.phone_hashes
    WHERE hash IN
    (SELECT hash FROM game.phone_hashes
     WHERE player_id = $1)))));

-- 6 degrees of separation
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 hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE hash IN
   (SELECT hash FROM game.phone_hashes
    WHERE hash IN 
    (SELECT hash FROM game.phone_hashes
     WHERE hash IN
     (SELECT hash FROM game.phone_hashes
      WHERE player_id = $1))))));

票数 4

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/33814857

复制

相似问题

问用于网络分析的SQL查询6度分离
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于网络分析的SQL查询6度分离EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于网络分析的SQL查询6度分离
EN