mysql 将子查询保留在连接之外

Question

我想计算 (pid = pid_raw) 占 pid_raw 总数的百分比，其中 date_raw 是该日期之前的 31 天。

我知道我可以部分地使用内部联接来完成此操作，但因为我想获取百分比，因此需要 pid_raw 的总数，无论匹配如何，该子查询不能成为内部联接的一部分。我如何编写子查询来获取不受内部联接影响但与 where 子句一致的 pid_raw 总数？

table1
date          pid
2015-06-01    223
2015-06-01    333 
2015-05-01    124 
2015-05-01    543 


table2
date_raw      pid_raw
2015-05-30    223
2015-05-15    111
2015-05-03    333 
2015-05-02    242
2015-05-05    300
2015-04-10    124
2015-04-15    543
2015-04-09    511


Example output
date         pid_percentage
2015-06-01     0.40           <-------(2/5)
2015-05-01     0.67            <------(2/3)

我的 sudo 代码:

select count(a.pid)/(select count(b.pid_raw) from b) AS pid_percentage, a.date       from 
table1 a join table2 b
ON a.pid = b.pid_raw
Where a.date - b.date_raw <=31 and a.date - b.date_raw > 0
group by a.date
order by YEAR(a.date),Month(a.date);

Answer 1

我的建议是加入日期，然后使用条件聚合进行计算:

select t1.date,
       count(distinct case when t1.pid = t2.pid_raw then t1.pid end) as NumMatches,
       (count(distinct case when t1.pid = t2.pid_raw then t1.pid end) / 
        count(distinct case when t1.pid = t2.pid_raw then t2.pid_raw end) 
       ) as percentage_pid
from table1 t1 left join
     table2 t2
     on t2.date_raw between t1.date - interval 31 day and t1.date
group by t1.date;