sql - mysql count number of times a field value appear in another table field -

given have 2 tables, how can see how many distinct values of x in distinct values of y within 31 days (or month) before date_x?

tb1      date_x        x     2015-05-01    cat     2015-05-01    pig     2015-05-01    mouse     2015-04-01    dog     2015-04-01    horse tb2       date_y         y     2015-04-30    cat     2015-04-02    cat     2015-04-05    mouse     2015-04-15    rabbit     2015-04-10    pig     2015-03-20    dog     2015-03-10    horse     2015-03-09    frog 

for example, want:

date_period num_match count_y percent_match 2015-05-01   2            4        40 2014-04-01   2            3        67 

date_period unique(date_x)

num_match number of distinct(y) matches distinct(x) 31 days before given date_period

count_y distinct(y) 31 days before given date_period.

percent_match num_match/count_y

this question extension earlier question here: join mysql on date range

one way can non-equijoin on date. can count distinct values of y either in set or match:

select x.date_x,        count(distinct case when x.x = y.y y.seqnum end) nummatch,        count(distinct y.seqnum) count_y,        (count(distinct case when x.x = y.y y.seqnum end) /         count(distinct y.seqnum)         ) ratio x left join      (select y.*, rownum seqnum       y      ) y      on y.date_y between x.date_x - 31 , x.date_x group x.date_x; 

edit:

the above treats 2 "cat" lines in y being different. misread desired results, think appropriate query is:

select x.date_x,        count(distinct case when x.x = y.y y.y end) nummatch,        count(distinct y.y) count_y,        (count(distinct case when x.x = y.y y.y end) /         count(distinct y.y)         ) ratio x left join      y      on y.date_y between x.date_x - 31 , x.date_x group x.date_x;