Requête SQL pour calculer l'adresse e-mail partagée dans le champ de l'entreprise

Si vous voulez des adresses e-mail qui appartiennent exactement à trois entreprises, utilisez l'agrégation et having:

select email
from "email_DB"
group by email
having count(distinct company) = 3

Commencez par compter pour chaque e-mail le nombre d'entreprises qui l'utilisent, puis agrégez les résultats:

select 
  case when counter = 1 then 'just_1' else 'shared' end type,
  count(*) counter
from (
  select email, count(distinct company) counter
  from email_DB
  group by email
) t
group by case when counter = 1 then 'just_1' else 'shared' end

Voici ma solution a obtenu le résultat étape par étape, en utilisant une clause WITH pour créer des données factices, vous devez donc sélectionner l'exécution de texte complet, cela fonctionne dans Oracle tel quel et si vous voulez exécuter dans Snowflake, vous avez besoin pour peaufiner un peu. Il donnera ci-dessous le résultat

Image de l'ensemble de résultats du SQL donné, exécuté dans Oracle a>

with t as (select 'jeo@jeo.com' Mail, 'ABC' Company from dual
        union all select 'jeo@jeo.com' Mail, 'XYZ' Company from dual
        union all select'jeo@jeo.com' Mail, '123' Company from dual
        union all select'sam@sam.com' Mail, 'XYZ' Company from dual
        union all select'sam@sam.com' Mail, '123' Company from dual
        union all select'bill@bill.com' Mail, 'XYZ' Company from dual
        union all select'no@sharedmail.com' Mail, 'PQR' Company from dual),
    g_CNT(mail,counter) as (Select  mail,COUNT(distinct Company) as counter from t group by mail),
    g_Companies(mail,Company) as (Select    distinct mail, Company from t),
    g_Combinations (mail,Combination, Depth,counter)
        as (Select      c1.mail, c1.Company|| ',' as Combination,       1 Depth,   g_CNT.counter
            From    g_Companies c1,g_CNT where c1.mail=g_CNT.mail
            union all
            Select  c.mail,Combination||Company || ',' as Combination , Depth + 1,    g_CNT.counter
            from    g_Companies c , g_Combinations p,g_CNT
            Where   c.mail=g_cnt.mail and c.mail=p.mail and p.Depth < g_CNT.counter AND     Combination not like '%' || Company || ',%'),
    g_Combinations_2 as (select rownum as r ,mail,trim(Combination) Combination,Depth,counter from g_Combinations where depth>1),
    g_Combinations_3 as (SELECT r,mail,Combination,trim(COLUMN_VALUE) s  FROM g_Combinations_2, xmltable(('"' || REPLACE(Combination, ',', '","') || '"'))),
    g_possible_combs as (select distinct mail,listagg(s,',') within group (order by s) poss_comb from g_Combinations_3 group by R,mail,combination),
-----
    all_CNT as (Select  COUNT(distinct Company) as counter from t),
    all_Companies as (Select    distinct Company from t),
    all_Combinations (Combination, Depth,counter)
        as (Select      c1.Company|| ',' as Combination , 1 Depth, all_CNT.counter From all_Companies c1,all_CNT
        union all
        Select      Combination||Company || ',' as Combination ,    Depth + 1,    all_CNT.counter
        from        all_Companies c ,   all_Combinations p,all_CNT
        Where   p.Depth < all_CNT.counter  AND  Combination not like '%' || Company || ',%'),
    all_Combinations_2 as (select rownum as r ,trim(Combination) Combination,Depth,counter from all_Combinations where depth>1),
    all_Combinations_3 as (SELECT r,Combination,trim(COLUMN_VALUE) s  FROM all_Combinations_2, xmltable(('"' || REPLACE(Combination, ',', '","') || '"'))),
    all_possible_combs as (select distinct listagg(s,',') within group (order by s) poss_comb from all_Combinations_3 group by R,combination),
    Mail_comp_comb as (select Mail,Listagg(Company,',') WITHIN GROUP (ORDER BY Company) Spread_Across,count(1) Number_Of_Companies from t group by Mail)
select poss_comb Companies_List,max(cnt) Count_of_Shared_Emails from 
(select poss_comb,count(distinct mail) cnt from g_possible_combs group by poss_comb
union all (select a.poss_comb, 0 cnt from all_possible_combs a )
)
group by poss_comb order by 1