SQL案例学习-微博好友关系

微博中的用户关系需要使用有向图（Directed Graph）表示。

因为关注是单向关联，A关注了B，但是B不一定关注A。下图是一个简单的粉丝关系示意图：

如果A关注了B，就会存在一条从A到B的带箭头的边。上图中的“刘一”关注了“周八”，同时“刘一”和“李四”相互关注。

对于有向图而言，度又分为入度（In-degree）和出度（Out-degree）。入度表示有多少条边指向该顶点，出度表示有多少条边是以该顶点为起点的。“刘一”的入度为4（微博的粉丝数），出度为2（微博关注的人数）。

对于社交网络关系中的图数据而言，一般使用邻接列表（Adjacency List）进行存储。例如，上面的粉丝关系可以使用下图所示的邻接列表进行描述。

 

每个顶点都有一个记录着与它相关的顶点列表。“刘一”关注了“李四”和“周八”，上图显示了一个关注列表。我们也可以创建一个逆向的邻接列表，用于存储用户的粉丝。

对于好友关系这种无向图，每条边都是双向的关注关系，可以使用两个邻接列表进行存储。

具体到数据库中的表而言，我们可以为顶点创建一个表，为顶点之间的边创建一个表，从而实现邻接表模型。

t_fans表中存储了用户的粉丝，例如：

 其中，user_id是用户编号，fans_id是该用户的粉丝编号。

t_followed表中存储了用户关注的人，例如：

 其中，user_id是用户编号，followed_id是该用户关注的其他用户。

粉丝关系分析

1.我的关注

查询刘一关注的人

实现SQL：

SELECT u.user_name AS "我的关注" FROM  
t_followed f
JOIN t_user u ON u.user_id=f.followed_id
WHERE f.user_id = (SELECT user_id FROM t_user WHERE user_name = '刘一');

执行结果：

“刘一”关注了“李四”和“周八”。 

2.共同关注

查询和“刘一”关注了相同用户的其他用户

实现SQL：

WITH cf(user1,user2,followed) AS (
  SELECT d.user_id,r.fans_id,r.user_id 
  FROM t_followed d
  JOIN t_fans r
  ON r.user_id=d.followed_id AND r.fans_id != d.user_id
  WHERE d.user_id = (SELECT user_id FROM t_user WHERE user_name = '刘一')
)


SELECT u1.user_name "用户1",u2.user_name "用户2",u3.user_name "共同关注"
FROM cf
JOIN t_user u1 ON u1.user_id=cf.user1
JOIN t_user u2 ON u2.user_id=cf.user2
JOIN t_user u3 ON u3.user_id=cf.followed

执行结果：

“刘一”和“孙七”共同关注了“周八”。

其中，cf代表了和“刘一”拥有共同关注用户的其他用户。

主查询通过多个连接语句返回了这些用户的姓名。

3.我的粉丝

查询“刘一”的粉丝

实现SQL：

SELECT u.user_name AS "我的粉丝" FROM  
t_fans f
JOIN t_user u ON u.user_id=f.fans_id
WHERE f.user_id = (SELECT user_id FROM t_user WHERE user_name = '刘一')

执行结果：

“刘一”拥有4个粉丝。

4.互为粉丝

查询哪些用户之间互相关注

实现SQL：

WITH df(user1,user2) AS (
  SELECT r.user_id,r.fans_id
  FROM t_fans r
  JOIN t_followed d ON r.user_id=d.user_id AND r.fans_id=d.followed_id 
  AND r.user_id < r.fans_id
)

SELECT u1.user_name AS "用户一",u2.user_name AS "用户二"
FROM  df
JOIN t_user u1 ON u1.user_id=df.user1
JOIN t_user u2 ON u2.user_id=df.user2

执行结果：

“刘一”和“李四”互为粉丝，而“孙七”和“周八”则互相关注。

其中，df代表了相互关注的两个用户。

主查询通过连接语句返回了这些用户的姓名。

示例表和脚本：

-- 创建用户表t_user
CREATE TABLE t_user(
  user_id   INTEGER PRIMARY KEY,
  user_name VARCHAR(50) NOT NULL
);
 
INSERT INTO t_user VALUES(1, '刘一');
INSERT INTO t_user VALUES(2, '陈二');
INSERT INTO t_user VALUES(3, '张三');
INSERT INTO t_user VALUES(4, '李四');
INSERT INTO t_user VALUES(5, '王五');
INSERT INTO t_user VALUES(6, '赵六');
INSERT INTO t_user VALUES(7, '孙七');
INSERT INTO t_user VALUES(8, '周八');
INSERT INTO t_user VALUES(9, '吴九');



-- 创建粉丝表t_fans
CREATE TABLE t_fans(
  user_id     INTEGER NOT NULL, 
  fans_id INTEGER NOT NULL, 
  PRIMARY KEY (user_id, fans_id)
);

INSERT INTO t_fans VALUES(1, 2);
INSERT INTO t_fans VALUES(1, 3);
INSERT INTO t_fans VALUES(1, 4);
INSERT INTO t_fans VALUES(1, 7);
INSERT INTO t_fans VALUES(2, 3);
INSERT INTO t_fans VALUES(3, 4);
INSERT INTO t_fans VALUES(4, 1);
INSERT INTO t_fans VALUES(5, 2);
INSERT INTO t_fans VALUES(5, 8);
INSERT INTO t_fans VALUES(6, 4);
INSERT INTO t_fans VALUES(7, 8);
INSERT INTO t_fans VALUES(8, 1);
INSERT INTO t_fans VALUES(8, 7);

-- 创建关注表t_followed
CREATE TABLE t_followed(
  user_id     INTEGER NOT NULL, 
  followed_id INTEGER NOT NULL, 
  PRIMARY KEY (user_id, followed_id)
);

INSERT INTO t_followed VALUES(1, 4);
INSERT INTO t_followed VALUES(1, 8);
INSERT INTO t_followed VALUES(2, 1);
INSERT INTO t_followed VALUES(2, 5);
INSERT INTO t_followed VALUES(3, 1);
INSERT INTO t_followed VALUES(3, 2);
INSERT INTO t_followed VALUES(4, 1);
INSERT INTO t_followed VALUES(4, 3);
INSERT INTO t_followed VALUES(4, 6);
INSERT INTO t_followed VALUES(7, 1);
INSERT INTO t_followed VALUES(7, 8);
INSERT INTO t_followed VALUES(8, 5);
INSERT INTO t_followed VALUES(8, 7);