Comptez le group_id le plus élevé pour chaque utilisateur comme une valeur distincte

Question

Il y a une logique délicate dans mon dernier projet (du moins pour moi) et j'ai essayé d'utiliser HAVING pour la résoudre, mais toutes mes pièces ont des résultats incorrects.

J'ai un tableau lié qui attribue les utilisateurs aux groupes d'utilisateurs et un autre tableau qui enregistre le nombre d'utilisateurs dans chaque groupe afin que je n'aie pas besoin de les compter tout le temps (le deuxième tableau est mis à jour lorsque l'enregistrement des utilisateurs est terminé).

Le problème que j'ai vient de la fonction qui met à jour les chiffres du deuxième tableau.

Le tableau lié ressemble à ceci et voici les exemples de données qui expliquent mon problème :

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

Un utilisateur peut appartenir à n'importe quel nombre de groupes. En fait, pour être un groupe, un utilisateur doit appartenir à tous les groupes inférieurs. En effet, chaque groupe se voit attribuer certaines autorisations. Cela ne peut pas être modifié sans une réécriture majeure de l'application actuelle.

Ce que je veux faire, c'est compter uniquement le groupe le plus élevé pour chaque utilisateur, donc dans l'exemple de données ci-dessus, l'utilisateur 1 ne sera compté que dans le groupe 5, tandis que le groupe 1 semblera vide.

Actuellement, toutes les entrées sont comptées, ce qui signifie que s'il y a 3 personnes dans le groupe 4, ces 3 utilisateurs sont également comptés dans les groupes 3, 2 et 1, ce qui rend mes chiffres relativement inutiles.

Jusqu'à présent, j'ai essayé quelques variantes de :

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

Cela me donne le même numéro. Ensuite j'ai essayé :

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

Mais si je comprends bien maintenant, je demande uniquement aux utilisateurs qui n'apparaissent dans aucun autre groupe.

J'ai l'impression d'être sur la bonne voie, mais je ne sais pas où ni quelles devraient être les conditions. J'ai vu quelques exemples de requêtes pour un flux similaire, mais tous les alias ne font qu'ajouter à la confusion.

Quelqu’un peut-il m’indiquer la bonne direction ?

Answer 1

Vous pouvez utiliser l'agrégation pour obtenir le maximum group_id 和 COUNT() 窗口函数来对每个返回的最大 group_id pour chaque utilisateur à compter > Nombre d'utilisateurs :

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

Cette requête filtre tous les group_id qui n'ont aucun membre.

Si vous voulez des résultats pour tous group_id的结果，请使用表groups的LEFT, connectez-vous à la requête ci-dessus en utilisant LEFT de la table groups :

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;

Voir une démo simplifiée.