统计转发次数和转发总数

Question

考虑下表

users                                tweets
---------------------------------    ---------------------------
user_id num_retweets sum_retweets    tweet_id user_id retweeted
---------------------------------    ---------------------------
1                                    1        1       3
2                                    2        1       0
3                                    3        1       4
                                     4        2       0
                                     5        2       0
                                     6        3       1
                                     7        3       2
                                     8        3       0

我想要计算num_retweets：一个用户写了一条被转发的推文的次数，以及sum_retweets：一个用户的所有tweet被转发的次数。UPDATE查询后的预期users表为：

users
---------------------------------
user_id num_retweets sum_retweets
---------------------------------
1       2            7 <-- 3 + 4
2       0            0
3       2            3 <-- 1 + 2

任何关于构建这两个查询的帮助都将得到极大的赞赏:-)我一直在跨表执行UPDATE时遇到问题。

Answer 1

UPDATE
USERS u
JOIN (
SELECT 
    tweets.user_id,
    COUNT(IF(tweets.retweeted > 0, 1, null)) as num_retweets,
    SUM(tweets.retweeted) as sum_retweets
FROM tweets
GROUP BY tweets.user_id
) as t ON t.user_id = u.user_id
SET u.num_retweets = t.num_retweets, u.sum_retweets = t.sum_retweets