將每個使用者的最高 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;

查看簡化的演示。