根据连接表计算SQL分数

Question

我有3个表:

Users

| id  | name  |
---------------
| 1   | Bob   |
| 2   | Dylan |
| 3   | Jenna |

Friendships，它是友谊关系的联接表

| user1_id | user2_id |
-----------------------
|    1     |    2     | // Bob and Dylan are friends
|    2     |    3     | // Dylan and Jenna are friends

和Posts

| id   | user_id  | title           | content |
-----------------------------------------------
|  1   |    1     | Bob's post      |   ...   |
|  2   |    2     | Dylan's post    |   ...   |
|  3   |    3     | Jenna's post    |   ...   |

我想计算每个帖子的分数，分数公式如下:

score = (is_post_author_friend_of_user(X)) ? 2 : 1

其中X为user_id。
是否有可能在纯SQL中实现这样的计算?当然，这意味着需要检索多对多的关系数据。
如果是，是否适合这份工作?这种情况下map/reduce呢?我可能使用了我不完全理解的术语。

清晰示例

如果Bob请求查看所有帖子，分数将如下所示:

| id   | user_id  | title           | content | score |
-------------------------------------------------------
|  1   |    1     | Bob's post      |   ...   |   1   |
|  2   |    2     | Dylan's post    |   ...   |   2   |
|  3   |    3     | Jenna's post    |   ...   |   1   |

因为Bob是Dylan唯一的朋友。
但是如果Dylan做同样的请求，分数将是:

| id   | user_id  | title           | content | score |
-------------------------------------------------------
|  1   |    1     | Bob's post      |   ...   |   2   |
|  2   |    2     | Dylan's post    |   ...   |   1   |
|  3   |    3     | Jenna's post    |   ...   |   2   |

因为Dylan是Bob和Jenna的朋友

Answer 1

是的，我相信这在纯SQL中是可能的。

你可以试试这个——它可能不是最快的，但它似乎对我有用。

select
  p.id,
  p.user_id,
  p.title,
  p.content,
  count(f.user1_id) + 1
from
  Posts p
left outer join
  Friendships f
  on (f.user1_id = p.user_id and f.user2_id = 2) -- change the 2 to the requesting id.
  or (f.user2_id = p.user_id and f.user1_id = 2) -- change the 2 to the requesting id.
group by
  p.id,
  p.user_id,
  p.title,
  p.content
;

SQL小提琴

我不知道你说的合适/地图/减少是什么意思。也许其他人可以回答这个问题。