Compter les lignes qui ne correspondent pas entre deux tables au sein d'un groupe

J'ai deux tables, chacune avec un user_id et des colonnes group_name .

Par exemple

SELECT * FROM
    (SELECT 
        times_show_up,
        COUNT(user_id) AS num_users,
        group_name1,
        group_name2
    FROM
    (
        SELECT 
            user_id, 
            COUNT(*) AS times_show_up,
            group_name1, 
            group_name2
        FROM
            table1
        INNER JOIN
            (SELECT DISTINCT user_id, group_name2 FROM table2) t2
        USING(user_id)
        GROUP BY user_id, group_name1, group_name2
    ) t1
    GROUP BY times_show_up, group_name1, group_name2) t9
    UNION ALL
    (SELECT
       0 AS times_show_up,
       SUM(CASE WHEN t1.user_id IS NULL 
           THEN 1 ELSE 0 END) AS num_users,
       'groupA' AS group_name1,
       group_name2
     FROM
       table2
     LEFT JOIN
       (SELECT user_id FROM table1 WHERE group_name1 = 'groupA') t1
     USING(user_id)
     GROUP BY group_name2)
     UNION ALL
    (SELECT
       0 AS times_show_up,
       SUM(CASE WHEN t1.user_id IS NULL 
           THEN 1 ELSE 0 END) AS num_users,
       'groupB' AS group_name1,
       group_name2
     FROM
       table2
     LEFT JOIN
       (SELECT user_id FROM table1 WHERE group_name1 = 'groupB') t1
     USING(user_id)
     GROUP BY group_name2)
     --- ORDER BY group_name1, group_name2, times_show_up

J'essaie de créer une répartition des décomptes entre le nombre de fois qu'un utilisateur de la table2 apparaît dans la table1, mais faites cela au sein d'un groupe.

Pour l'exemple ci-dessus, j'obtiendrais

    SELECT 
        COUNT(user_id) AS num_users,
        times_show_up,
        group_name1,
        group_name2
    FROM
    (
        SELECT 
            user_id, 
            COUNT(*) AS times_show_up,
            group_name1, 
            group_name2
        FROM
            table1
        RIGHT JOIN
            (SELECT DISTINCT user_id, group_name2 FROM table2)
        USING(user_id)
        GROUP BY user_id, group_name1, group_name2
    )
    GROUP BY times_show_up, group_name1, group_name2

Pour expliquer certaines lignes comme exemple supplémentaire:

Pour la ligne 1, l'utilisateur avec user_id = 3 dans le groupeL apparaît 0 fois dans le groupeA. Pour la ligne 2, l'utilisateur avec user_id = 2 dans groupL apparaît une fois dans groupA. Et pour la ligne 3, l'utilisateur avec user_id = 1 dans groupL apparaît deux fois dans groupA.

Bien que dans cet exemple, une personne apparaisse jusqu'à un maximum de 2 fois, dans les données réelles, ce nombre est arbitrairement grand nombre que je ne connais pas à l'avance.

De même pour les autres groupes si j'ai rempli tout cela correctement.

J'ai trouvé une requête qui peut faites tout cela sauf en comptant les 0, qui ressemble à ceci:

| times_show_up | number_of_users | group_name1 | group_name2 |
---------------------------------------------------------------
|      0        |       1         |    groupA   |    groupL    |
|      1        |       1         |    groupA   |    groupL    |
|      2        |       1         |    groupA   |    groupL    |
|      0        |       2         |    groupB   |    groupL    |
|      1        |       1         |    groupB   |    groupL    |
|      2        |       0         |    groupB   |    groupL    |
|      0        |       2         |    groupA   |    groupN    |
|      1        |       0         |    groupA   |    groupN    |
|      2        |       1         |    groupA   |    groupN    |
|      0        |       2         |    groupB   |    groupN    |
|      1        |       1         |    groupB   |    groupN    |
|      2        |       0         |    groupB   |    groupN    |
----------------------------------------------------------------

Malheureusement, cela ne renverra pas les nombres de 0 dans la colonne times_show_up , et je n'ai pas trouvé de solution qui puisse accomplir cela sans beaucoup de sous-requêtes. Une méthode possible consiste simplement à exécuter des sous-requêtes pour récupérer tous les 0 pour toutes les combinaisons de tous les groupes, puis simplement UNION ces lignes au reste de la table. Mais je veux éviter les approches qui incluent des sous-requêtes pour chaque combinaison group1, group2 possible car le nombre de groupes est très grand.

Certaines limitations incluent que la partition par sur cet ensemble de données a tendance à manquer de mémoire, je veux donc l'éviter. Exigence mise à jour: De plus, l'utilisation de CROSS JOIN au niveau de l'utilisateur individuel (donc la jonction croisée de la table1 directement à la table2 sans regrouper les lignes au préalable) ne fonctionne pas car chaque table comporte des dizaines de millions de lignes.

Enfin, les lignes avec des 0 dans la colonne number_of_users ne doivent pas apparaître (c'est bien si elles le font car elles peuvent être supprimées avec un simple WHERE code >, mais ne sont pas nécessaires, si cela aide avec la requête)

MISE À JOUR:

J'ai pu proposer une requête qui peut générer les zéros tout en ne nécessitant qu'une seule requête pour chaque nom_groupe1, plutôt qu'une seule requête pour chaque combinaison nom_groupe1, nom_groupe2. Je l'ajoute à la question au cas où cela aiderait à trouver des réponses avec encore moins de requêtes, car il est toujours vrai que le nombre de groupes dans le tableau 1 pourrait être supérieur à 20+, ce qui signifie plus de 20 requêtes ajoutées via UNION ALL .

table1:

| user_id | group_name1|
------------------------
|    1    |   'groupA' |
|    1    |   'groupB' |
|    2    |   'groupA' |
|    1    |   'groupA' |
------------------------


table2:

| user_id | group_name2|
------------------------
|    1    |   'groupL' |
|    2    |   'groupL' |
|    3    |   'groupL' |
|    4    |   'groupN' |
|    1    |   'groupN' |
|    3    |   'groupN' |
------------------------