Zählen Sie die höchste Gruppen-ID für jeden Benutzer als eindeutigen Wert

Question

In meinem neuesten Projekt gibt es eine knifflige Logik (zumindest für mich) und ich habe versucht, sie mit HAVING zu lösen, aber alle meine Spielzüge haben falsche Ergebnisse geliefert.

Ich habe eine verknüpfte Tabelle, die Benutzer Benutzergruppen zuordnet, und eine weitere Tabelle, die aufzeichnet, wie viele Benutzer sich in jeder Gruppe befinden, sodass ich sie nicht ständig zählen muss (die zweite Tabelle wird aktualisiert, wenn die Benutzerregistrierung abgeschlossen ist).

Das Problem, das ich habe, ist die Funktion, die die Zahlen in der zweiten Tabelle aktualisiert.

Die verknüpfte Tabelle sieht so aus und hier sind die Beispieldaten, die mein Problem erklären:

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

Ein Benutzer kann beliebig vielen Gruppen angehören. Um eine Gruppe zu sein, muss ein Benutzer tatsächlich allen darunter liegenden Gruppen angehören. Dies liegt daran, dass jeder Gruppe bestimmte Berechtigungen zugewiesen werden. Dies kann nicht ohne eine umfassende Neufassung der aktuellen Anwendung geändert werden.

Was ich tun möchte, ist, für jeden Benutzer nur die höchste Gruppe zu zählen, sodass in den Beispieldaten oben Benutzer 1 nur in Gruppe 5 gezählt wird, während Gruppe 1 leer aussieht.

Derzeit werden alle Einträge gezählt. Das heißt, wenn sich 3 Personen in Gruppe 4 befinden, werden diese 3 Benutzer auch in den Gruppen 3, 2 und 1 gezählt, was meine Zahlen relativ nutzlos macht.

Bisher habe ich einige Variationen ausprobiert von:

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

Das gibt mir die gleiche Nummer. Dann habe ich versucht:

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

Aber so wie ich es jetzt verstehe, frage ich nur Benutzer, die in keiner anderen Gruppe vorkommen.

Ich habe das Gefühl, auf dem richtigen Weg zu sein, aber ich weiß nicht, wo und wie die Bedingungen sein sollen. Ich habe einige Beispielabfragen für einen ähnlichen Prozess gesehen, aber all die Aliase tragen nur zur Verwirrung bei.

Kann mir jemand den richtigen Weg weisen?

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;

查看简化的演示。