mysql - max 和 count 加入 n 到 n 表

Question

我被查询阻塞了，我需要一些帮助。如果有人可以帮助我，我将不胜感激 :)

我有两个表(我只用了一部电影来展示情况):

表消耗

client_id   movie_id    name        date_consumption
XXX         1           MovieA          01/Jan/2000
YYY         1           MovieA          01/Jan/2000
ZZZ         1           MovieA          02/Jan/2000
XXX         1           MovieA          02/Jan/2000
ZZZ         1           MovieA          10/Jan/2000

表 movies_owners

movie_id    rightowner      date_buyed*
A           LucasFilm       01/Jan/2000
A           Disney          02/Jan/2000
A           Sony            05/Jan/2000

**Date_buyed : 这是电影属于新权利所有者的日期。*

思路很简单:

I have to find the count of clients who watched a movie for day with the correct Right Owner in the day this movie was watched.

预期表

movie_id    date_consumption    rightowner      consumption(count)
MovieA      01/Jan/2000         LucasFilm       2
MovieA          02/Jan/2000         Disney          2
MovieA          10/Jan/2000         Sony            1

--

通过这个查询，我可以在某天找到电影的正确所有者(在相关日期之前的所有购买日期的最大值):

SELECT A.movie_id, A.date_buyed, A.rightowner
    FROM movies_owners A
    WHERE A.date_buyed EXISTS (
        SELECT max(date_buyed) 
        FROM movies_owners
        WHERE TO_DATE(date_buyed) <= TO_DATE('2000-01-02') AND movie_id = 'MovieA') 
    AND movie_id = 'MovieA';

但我的问题是与消耗表连接时。我不能在子查询中使用表消费中的 date_consumption。

我试图打入辅助表进行连接，但我仍然找不到结果。 =\

至少有人可以给我一个想法或建议吗？先谢谢大家。

仅供引用:我正在使用 Hive，但语法与 Sql 几乎相同。

Answer 1

Hive 不支持非等值连接。移动加入 c.consumption_date<=o.date_buyed WHERE 子句的条件:

select c.movie_id, c.date_consumption, o.rightowner, c.consumption_count 
from
(--consumption count per movie, date
 select substr(movie_id,6) movie_id, date_consumption, count(*) consumption_count 
   from consumption
  group by  substr(movie_id,6), date_consumption
)c
left join movies_owners o on c.movie_id=o.movie_id
where c.consumption_date<=o.date_buyed