1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| With SR1 as
(
select 1 as id_produit, 1 as id_client from dual union all
select 2 , 1 from dual union all
select 2 , 2 from dual union all
select 2 , 3 from dual union all
select 3 , 3 from dual union all
select 4 , 4 from dual union all
select 5 , 1 from dual union all
select 5 , 5 from dual union all
select 5 , 6 from dual
)
, SR2 as
(
select id_client, min(id_produit) as id_produit
from SR1
group by id_client
having count(distinct id_produit) = 1
)
select prd.id_produit, count(SR2.id_client) as nb_clients_unique
from SR2
right outer join (select distinct id_produit from SR1) prd
on prd.id_produit = SR2.id_produit
group by prd.id_produit
order by prd.id_produit asc;
ID_PRODUIT NB_CLIENTS_UNIQUE
---------- -----------------
1 0
2 1
3 0
4 1
5 2 |
Partager