mysql - 根据连接表计算 SQL 分数

Question

我有 3 个表:

用户

| 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

和帖子

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

因为鲍勃和迪伦只是 friend 。
但如果 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 的 friend 。

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 Fiddle

我不确定你说的 fitted/map/reduce 是什么意思。也许其他人可以回答这部分。