mysql - 使用mysql查找两个列表之间的公共(public)集

Question

我有两个查询，我想在其中找到它们的共同值。我试图最终找出访问过这两个网页的用户百分比。

SELECT DISTINCT user_id 
FROM table 
WHERE url ='y'
ORDER BY user_id;

SELECT DISTINCT user_id 
FROM table 
WHERE url ='z'
ORDER BY user_id;

我已经尝试过

NOT IN 

和一个

UNION

但运气不太好——尽管我很容易做错。我是新人。

Answer 1

一种方法是使用条件聚合。要获取每个用户的信息:

select user_id,
       sum(url = 'y') as y_visits,
       sum(url = 'z') as z_visits
from t
group by user_id;

要获取用户列表，请添加 having 子句:

having y_visits >= 1 and z_visits >- 1

获取摘要信息:

select y_visitor, z_visitor, count(*)
from (select user_id,
             max(url = 'y') as y_visitor,
             max(url = 'z') as z_visitor
      from t
      group by user_id
     ) yz
group by y_visitor, z_visitor;

要获得简单的百分比:

select avg(y_visitor = 1 and z_visitor = 1) as p_VisitedBothYandZ
from (select user_id,
             max(url = 'y') as y_visitor,
             max(url = 'z') as z_visitor
      from t
      group by url
     ) yz;