postgresql - PostgreSQL 中用户交互(回复、提及)返回时间

Question

假设我们有 3 个表:tweet、users、tweet_mentusers，其中 tweet.user_id = users.id 和 tweet.id = tweet_mentusers.tweet_id。

表格的表示可以是:

    tweet                users                    tweet_mentusers                   

id   | user_id           id                       tweet_id | mentuser_id       
----- --------          -------                   --------- ------------
11   |  1                 1                         11     |  3
12   |  2                 2                         12     |  4
13   |  2                 2                         13     |  4
14   |  1                 1                         14     |  3
15   |  1                 1                         15     |  5
16   |  2                 2                         16     |  4

因此，我正在尝试确定这些用户之间的交互。我在 PostgreSQL 中的查询是:

select id1, id2 
from (
      select tweet.id as tweetid, tweet_mentusers.mentionedusers_id as id1 
      from tweet 
      inner join tweet_mentusers on tweet.id = tweet_mentusers.tweet_id
      group by 2, 1
      ) a inner join
     (
     select users.id as id2, tweet.id as tweetid 
     from users 
     inner join tweet on users.id = tweet.user_id 
     group by 1, 2
     ) b on a.tweetid = b.tweetid

所以查询返回发推的人的 ID 和他们提到的人。但是，我想知道是否有一种方法可以只返回唯一的交互以及交互发生的次数，因为此查询返回所有交互。到目前为止，我一直在尝试一些组合，但我没有运气。如果您有任何建议，我将不胜感激!

Answer 1

您可以通过按一对(提及、tweeter)对查询结果进行分组来获取交互次数:

select mentioned, tweeter, count(*)
from (
    select id1 mentioned, id2 tweeter
    from (
        select tweet.id as tweetid, tweet_mentusers.mentuser_id as id1 
        from tweet 
        inner join tweet_mentusers on tweet.id = tweet_mentusers.tweet_id
        group by 2, 1
        ) a 
    inner join (
        select users.id as id2, tweet.id as tweetid 
        from users 
        inner join tweet on users.id = tweet.user_id 
        group by 1, 2
        ) b 
    on a.tweetid = b.tweetid
    ) sub
group by 1, 2
order by 1, 2;

 mentioned | tweeter | count 
-----------+---------+-------
         3 |       1 |     2
         4 |       2 |     3
         5 |       1 |     1
(3 rows)    

但是，您的查询过于复杂。试试这个:

select mentuser_id mentioned, user_id tweeter, tweet_id
from tweet_mentusers m
join tweet t
on m.tweet_id = t.id

 mentioned | tweeter | tweet_id 
-----------+---------+----------
         3 |       1 |       11
         4 |       2 |       12
         4 |       2 |       13
         3 |       1 |       14
         5 |       1 |       15
         4 |       2 |       16
(6 rows)

最后:

select mentioned, tweeter, count(*)
from (
    select mentuser_id mentioned, user_id tweeter, tweet_id
    from tweet_mentusers m
    join tweet t
    on m.tweet_id = t.id
    ) sub
group by 1, 2
order by 1, 2;

 mentioned | tweeter | count 
-----------+---------+-------
         3 |       1 |     2
         4 |       2 |     3
         5 |       1 |     1
(3 rows)