将每个用户的最高 group_id 计为不同值

Question

我最新的项目中有一些棘手的逻辑（至少对我来说），我一直在尝试使用 HAVING 来解决它，但我所有的玩法都有不正确的结果。

我有一个链接表，用于将用户分配到用户组，另一个表用于记录每个组中有多少用户，因此我不需要一直对它们进行计数（第二个表在用户注册完成时更新）。

我遇到的问题是更新第二个表中的数字的函数。

链接表如下所示，这里的示例数据解释了我的问题：

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
| 1  | 1       | 1        |
| 2  | 1       | 5        |
| 3  | 14      | 2        |
+----+---------+----------+

一个用户可以属于任意数量的组。事实上，为了成为一个组，用户必须属于其下面的所有组。这是因为每个组都分配有一定的权限。如果不对当前的应用程序进行重大重写，这一点就无法改变。

我想要做的是只统计每个用户最高的组，因此在上面的示例数据中，用户 1 将仅计入组 5，而组 1 看起来为空。

目前，所有条目都会被计算在内，这意味着如果第 4 组中有 3 个人，那么这 3 个用户也会被计算在第 3、2 和 1 组中，这使得我的数字相对毫无意义。

到目前为止，我已经尝试过以下几种变体：

SELECT user_group_id, COUNT(user_id) members FROM `group_users` GROUP BY user_group_id HAVING count(user_group_id) = 1;

这给了我同样多的数字。然后我尝试了：

SELECT user_group_id, COUNT(user_id) members FROM `group_users` GROUP BY user_group_id HAVING COUNT(user_id) = 1;

但据我现在了解，我只询问未出现在任何其他组中的用户。

我觉得我走在正确的轨道上，但我不知道在哪里或条件应该是什么。我看到了一些类似流程的示例查询，但所有别名只会增加混乱。

有人可以给我指出正确的方向吗？

Answer 1

您可以使用聚合来获取每个用户的最大 group_id 和 COUNT() 窗口函数来对每个返回的最大 group_id 进行计数>用户数量：

SELECT DISTINCT MAX(group_id) AS group_id,
       COUNT(*) OVER (PARTITION BY MAX(group_id)) AS members
FROM group_users
GROUP BY user_id;

此查询过滤掉所有没有成员的group_id。

如果您想要所有group_id的结果，请使用表groups的LEFT连接到上述查询：

WITH cte AS (
  SELECT DISTINCT MAX(group_id) AS group_id,
         COUNT(*) OVER (PARTITION BY MAX(group_id)) AS members
  FROM group_users
  GROUP BY user_id
)
SELECT g.group_id,
       COALESCE(c.members, 0) AS members
FROM `groups` AS g LEFT JOIN cte AS c
ON c.group_id = g.group_id;

查看简化的演示。