mysql - 通过 SQL 使用特定标签查找最活跃的用户

Question

这是一个用户可以上传图片并在描述中使用标签的网站。我想找出过去 10 天内使用特定标签发布图片最活跃的用户是谁。

以下是表格:

卡片(帖子)

+----+---------+---------+------------+
| id |  name   | user_id |    date    |
+----+---------+---------+------------+
|  1 | daw.jpg |       1 | 2020-01-25 |
|  2 | efs.jpg |       2 | 2020-01-25 |
|  3 | dwa.jpg |       1 | 2020-01-25 |
|  4 | wda.jpg |       3 | 2020-01-25 |
|  5 | efa.jpg |       2 | 2020-01-25 |
+----+---------+---------+------------+

卡片标签

+----+---------+--------+---------------------+
| id | card_id | tag_id |      date_time      |
+----+---------+--------+---------------------+
|  1 |       1 |      1 | 2020-01-25 12:46:00 |
|  2 |       1 |      2 | 2020-01-24 13:26:00 |
|  3 |       1 |      3 | 2020-01-23 14:46:00 |
|  4 |       2 |      1 | 2020-01-22 15:51:00 |
|  5 |       2 |      3 | 2020-01-21 12:52:00 |
+----+---------+--------+---------------------+

用户

+----+---------+-------+
| id |  name   | ect.. |
+----+---------+-------+
|  1 | bush    |       |
|  2 | clinton |       |
|  3 | obama   |       |
|  4 | trump   |       |
|  5 | reagan  |       |
+----+---------+-------+

我运行了这个 SQL 查询，它为我提供了过去 10 天内使用特定标签的用户列表。

$q2 = $db->prepare('SELECT *
                    FROM cards_tags
                    JOIN cards ON cards_tags.card_id = cards.id
                    JOIN users ON cards.user_id = users.id
                    WHERE cards_tags.date_time > DATE_SUB(CURDATE(), INTERVAL 10 DAY)
                    AND cards_tags.tag_id = :tag_id');
$q2->execute([':tag_id' => $tag->id()]);

while($data = $q2->fetch()) 
{
    echo $data['user'].' posts with the tag id : '.$tag->id().'<br>';
}

但是，我想知道用户在过去 10 天内使用特定标签的次数，并按用户 ID 进行分组。

这是我到目前为止所想到的，但它不起作用:

$q2 = $db->prepare('SELECT *, COUNT(users.id) AS occurrence
                    FROM cards_tags
                    JOIN cards ON cards_tags.card_id = cards.id
                    JOIN users ON cards.user_id = users.id
                    WHERE cards_tags.date_time > DATE_SUB(CURDATE(), INTERVAL 10 DAY)
                    AND cards_tags.tag_id = :tag_id
                    GROUP BY users.id');
$q2->execute([':tag_id' => $tag->id()]);

while($data = $q2->fetch()) 
{
    echo $data['user'].' is trending with '.$data['occurrence'].' posts with the tag id : '.$tag->id().'<br>';
}

Answer 1

使用以下查询，可能会对您有所帮助:

SELECT cards_tags.tag_id, COUNT(users.id) AS 出现次数 来自卡片_标签 加入卡片卡上的cards_tags.card_id = cards.id 在cards.user_id = users.id上加入用户 WHERE cards_tags.date_time > DATE_SUB(CURDATE(), INTERVAL 10 DAY) AND cards_tags.tag_id = :tag_id GROUP BY users.id');